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

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

Best Answer

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)

Related Solutions

Postgresql – Structure for storing table data, lists, text, pictures within PostgreSQL JSON field

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays:

CREATE TYPE mydoc AS (content_type text, names text[], values text[]);
CREATE TABLE documentData(id int primary key, documentName text, docs mydoc[]);
INSERT INTO documentData(id,documentName,docs) VALUES (1, 'DocumentA', '{"(Table,\"{ColA,ColB,ColC}\",\"{val1,val2,val3}\")","(Table,\"{ColA,ColB,ColC}\",\"{val4,val5,val6}\")"}'::mydoc[])

SELECT d.id, d.documentName, dd.*
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true);

This won't get a significant advantage over JSON, other than portability to older PostgreSQL versions, but unnest and =ANY type of operations are often much simpler than the JSON functions, so it may be easier to query and manipulate, e.g.:

SELECT d.id, d.documentName, dd.content_type, pos, name, dd.values[pos] AS value
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true)
LEFT JOIN LATERAL unnest(dd.names) WITH ORDINALITY AS y (name, pos) ON (true)
WHERE dd.content_type = 'Table' AND name ~ '^Col[AB]';

Postgresql – Full text search over multiple related tables: indices and performance

Optimization

You're going down the right track.

You either need to

Denormalize
Cache

Caching the results

What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well.

CREATE MATERIALIZED VIEW foo
AS
SELECT t.id, to_tsvector(concat_ws(' ',a.name, o.address, t.description, a.name)) AS tsv
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id 
;

Then just

SELECT * FROM foo WHERE tsv @@ plainto_tsquery('foo bar');

Denormalizing the table

This can take a lot of forms, you've got this right though..

Redesign

Searching everything in a fuzzy fashion like this is a losing game. Even this knock off of Dungeon and Dragons meets Yahoo Answers has rules.

It becomes a lot easier to generate a query when you introduce syntax likes [text] for tagging, and is:answer to search just answers, rather than rebuilding Google and normalized indexes.