0

I'm french. So, sorry for my mistakes !

I'm doing a database migration on PostgreSql 9.6 via pgAdmin4 and in the first one there's a CHARACTER VARYING type field and in the other it's a CIDR type field. I use this command :

INSERT INTO ip.cidr (cid_id, cid_res_id, cid_cidr)
SELECT a, b, network(c) FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT ipp_id, res_id, ipp_ipcidr FROM public.ipplage')
AS x(a integer, b integer, c cidr)

but it doesn't work ! This is the error :

ERREUR: invalid cidr value : « 10.26.3.0/23 » DETAIL: The value has bits positioned to the right of the mask. ********** Error ********** ERREUR: invalid cidr value : « 10.26.3.0/23 » SQL state: 22P02 Detail: The value has bits positioned to the right of the mask.

errors are in french. I translated it myself

I tried to cast varchar to cidr but it doesn't work ! same eror ! I searched everywhere to know how to cast this f*****g CHARACTER VARYING to CIDR type but no result ! Please !!! Help me ! :(

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Unrillaz Ti'bob
  • 71
  • 1
  • 1
  • 7

1 Answers1

1

This is because 10.26.3.0/23 is not the address of the network. 10.26.2.0/23 is.

So you have two options, it depends on what infos do you want to keep :

INSERT INTO ip.cidr (cid_id, cid_res_id, cid_cidr)
SELECT a, b, network(c) FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT ipp_id, res_id, ipp_ipcidr::inet::cidr FROM public.ipplage')
AS x(a integer, b integer, c cidr)

This cast the network address to inet and then, find the network address of the inet.

Or, if you want to keep your address :

INSERT INTO ip.cidr (cid_id, cid_res_id, cid_cidr)
SELECT a, b, network(c) FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT ipp_id, res_id, ipp_ipcidr FROM public.ipplage')
AS x(a integer, b integer, c inet)

If you want more infos : https://www.postgresql.org/docs/9.6/static/datatype-net-types.html

EDIT: by the way, if you want your error in plain english and not in french (I'm french and experiencing the same issue ;-) You can comment out this lines in the postgresql.conf:

default_text_search_config = 'pg_catalog.french'
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Rémi Desgrange
  • 868
  • 1
  • 6
  • 20
  • Interesting. I tough the problem was the ip address but when test it was autocorrected and didnt throw any error. http://rextester.com/QXFG14627 – Juan Carlos Oropeza Oct 20 '17 at 13:32