string search in table with partial text and without all field names

Question

I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. And string could be like just half . For examaple search string can ve IT and I should return record of ITALY .

my table is something like

currently using this query but it is not working for half text. and don't want to user fields name in IN like (1st field ,2nd filed, 3rd filed ) is there something * like generic there ?

currently using this

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

Answer 1

Build a string consisting of what you want to match against, and test against that. It could be as simple as casting the whole row to text:

testdb=# create table t(userid integer, uname text, native text, place text);
CREATE TABLE
testdb=# insert into t select 1, 'Richard', 'Italy', 'San Francisco';
INSERT 0 1
testdb=# insert into t select 2, 'Elon', 'Europe', 'New York';
INSERT 0 1
testdb=# insert into t select 3, 'John', 'Vietnam', 'Italy';
INSERT 0 1
testdb=# select t::text from t;
                 t                 
-----------------------------------
 (1,Richard,Italy,"San Francisco")
 (2,Elon,Europe,"New York")
 (3,John,Vietnam,Italy)
(3 rows)

testdb=# select * from t where t::text ~ 'It';
 userid |  uname  | native  |     place     
--------+---------+---------+---------------
      1 | Richard | Italy   | San Francisco
      3 | John    | Vietnam | Italy
(2 rows)

Or, if you want to ensure that commas in the search string don't cause it to match more than one column, or if you need to omit any of the columns, use concat() to separate the values with unprintable chars instead.

testdb=# select concat(uname, e'\x01', native, e'\x01', place) from t;
              concat               
-----------------------------------
 Richard\x01Italy\x01San Francisco
 Elon\x01Europe\x01New York
 John\x01Vietnam\x01Italy
(3 rows)

testdb=# select * from t where t::text ~ 'rd,It';
 userid |  uname  | native |     place     
--------+---------+--------+---------------
      1 | Richard | Italy  | San Francisco
(1 row)

testdb=# select * from t where concat(uname, e'\x01', native, e'\x01', place) ~ 'rd,It';
 userid | uname | native | place 
--------+-------+--------+-------
(0 rows)