Postgres – Adding and Populating Column with Data from Existing Column

alter-tablepostgresqlupdate

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.

Best Answer

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.

Related Question