Postgresql – How to search for a string in all columns of a table

postgresqlstring-searching

I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. For example, I have a table like this.

I need a query to return all rows that has Italy in any of their columns i.e the query shoud return 1st and 3rd rows.

Best Answer

You can use an IN condition for that:

select *
from the_table
where 'Italy' IN (name, native, place);

Related Solutions

Postgresql – String replace using concatenated strings from various columns

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.

Postgresql – Bulk update of all columns

If you only want to update data, I'm not sure what the INSERT statement is for in your question.

If you just want to update several rows with a single statement, you might be looking for this:

with update_values (ID,PARENT_ID,BOUGHT_IN_FORM_TYPE_ID,PRIORITY,NAME,HEADING,DESCRIPTION,ICON,BOUGHT_IN_CONTROL_PANEL_FILE_ID) as 
(
  VALUES
     (109,1,28,100,'Tooling','Tooling','Enter your Machine Tools here','tooling.png',null), 
     (1,0,1,200,'Bought In','Bought In','','boughtin.png',null)
)
update bought_in_control_panel
   set parent_id = ud.parent_id, 
       bought_in_form_type_id = ud.bought_in_form_type_id,
       ....
from update_values ud
where ud.id = bought_in_control_panel.id;

Best Answer

Related Solutions

Postgresql – String replace using concatenated strings from various columns

Postgresql – Bulk update of all columns

Related Question