0

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

table

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);

1 Answer 1

2

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)
2
  • why I can't use query like this ? select * from the_table where 'Italy' IN (name, native, place);
    – pie
    May 21, 2021 at 22:42
  • Because, in your question, you state that want to be able to search for partial strings ('It' -> Italy). 'string_literal' IN (list, of, columns) is only true if the string appears as one of the column values, not in one of the column values (as that would be a matching, rather than an equality, operation).
    – AdamKG
    May 24, 2021 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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