Postgres – Replace a string in a table

Postgres – Replace a string in a table

Came across an interesting problem where I had to replace all the occurrences of string with another one inside a column value for a table. This was on a Postgres database. Thankfully Postgres provides some inbuilt functions which work perfectly to achieve this.

The function that helped me out was regexp_replace.

The target was to add www to all the URLS for GaganOnTheNet.com. Basically replace the occurrence of the string “http://gaganonthenet.com/“ with “http://www.gaganonthenet.com/“ inside the table “url” under the column “address”.

The following is how I achieved this:

gagan_db_stage=# UPDATE url SET address = regexp_replace(address, ‘http://gaganonthenet.com/', 'http://www.gaganonthenet.com/’, 'g');
UPDATE 25976

And that just saved me from manually modifying around 26 thousand rows inside this table.

This function has been tested on larger set of data as well and it works seamlessly and completes in seconds.

Postgres – Replace a string in a table

No Comments

Post a Comment

Time limit is exhausted. Please reload CAPTCHA.