0

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with the IP address from rows who's TEXT column is an IP so I can take advantage of the IP Address and IP Network features in Postgres.

Adding the column is easy enough, but when I go to run UPDATE foo SET ip_addr = data::inet I (expectedly) get an ERROR: invalid input syntax for type inet for the first row that's a domain name an the update stops.

Is there any way to skip the rows that aren't IP addresses, or am I going to have to write a function for this? I'm needing to do it as part of a migration using a Golang migration tool (github.com/mattes/migrate) so I was hoping the UPDATE command would be sufficient.

1

To achieve this, you need to do the following:

(It's implicitly understood that your ALTER TABLE succeeded).

ty=# CREATE TABLE a(b text, c inet);
CREATE TABLE

Insert a text record with an embedded IP address.

ty=# INSERT INTO a VALUES('adfasfsaa 232.123.221.10', NULL);
INSERT 0 1

And then update the second column with the first by extracting the IP address (as a SUBSTRING) using a regular expression and then (CASTing the resultant substring as an inet type).

ty=# UPDATE a SET c = SUBSTRING(b, '(?:[0-9]{1,3}\.){3}[0-9]{1,3}')::inet;
UPDATE 1

Et voilà!

ty=# SELECT * FROM a;
            b             |       c        
--------------------------+----------------
 adfasfsaa 232.123.221.10 | 232.123.221.10
(1 row)

A more accurate regex to extract IP addresses from text might be - depending on your use case:

'\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b'

If there's more than one IP address in the string, you will get the first occurrence of an IP address in that string.

I also tested (the simple regex) on a string like 'https://123.23.21.12/sadf.net' and it works - looks like the simple one will be sufficient for most use cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.