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 ! :(
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'