Need an extension that offers a full-text-search with a kind of contains(*,text) instead to concatenate each field-value.

There are times when a formal structured query language is overkill, and the easiest way to find the right content is to perform a search, like you would with a search engine such as Google or Yahoo (...) JCR-SQL2:
SELECT * FROM [nt:base] WHERE CONTAINS([nt:base],'full-text-query')

Full+text+search example in other SQL engine

| improve this question | | | | |
  • I'm confused at what you mean, you mean to search all columns in a table by the column's name, or the columns value? Could you provide a sample table and desired result? – Evan Carroll Jun 5 '17 at 17:55
  • Some SQL clients will allow you do search through the data of all tables – a_horse_with_no_name Jun 5 '17 at 20:48
  • Hi @EvanCarroll, columns values (as traditional concatenation for search string in all)... Sorry, see and editing only now. Community also edited, please review your "on hold". – Peter Krauss Jun 6 '17 at 7:24

You can cast the complete row to a string (text) and then use the built-in full text search on that result:

select *
from foo
where to_tsvector(foo::text) @@ to_tsquery('full-text-query');

foo::text concatenates all columns of that table as a single large string, using the default text representation for non-text data.

| improve this answer | | | | |
  • 1
    Perfect, is working! "concatenates all columns of that table" of each row. Is perfect (!), and do exactly the same that the reference-example (JCR-SQL2) do. – Peter Krauss Jun 6 '17 at 7:35
  • 1
    This is a neat trick, but the problem is that it is hard to index, as foo::text is not immutable. What I did in a similar situation is create a pl/perl function which returns the concatenation of all column values (minus a couple which it made not sense to search) with a suitable delimiter and falsely mark it as immutable, then index the results of that function. – jjanes Jun 8 '17 at 20:23

There is no extension. That's not how SQL works. There is no "grep on a table". You can however, use the information_schema to generate a dynamic query for this. First we create a sample table,

CREATE TABLE foo (a text, b text, c text, d text);

Now we have a query,

WITH dest(catname, schemaname, tablename) AS (
  SELECT CURRENT_CATALOG::text, 'public'::text, 'foo'::text
    $$ SELECT * FROM %I.%I.%I WHERE %s$$,
        $$to_tsvector(%I.%I.%I.%I) @@ to_tsquery('rat & fat')$$,
      ' OR '
FROM information_schema.columns
WHERE table_catalog = dest.catname
  AND table_schema = dest.schemaname
  AND table_name = dest.tablename
GROUP BY dest.catname, dest.schemaname, dest.tablename;

Now you can run that in psql with \gexec, or EXECUTE is as dynamic sql.

(paste query above)

You can also write dynamic sql to concatenate each field, if that's what you want and you're just being lazy by not wanting to write it out.

| improve this answer | | | | |
  • 1
    PS: today a RDBMS as PostgreSQL can't say "that's not how SQL works"... We have XPath, JSONb algebra, etc. to work with other representations, to be friendly and competitive (!). – Peter Krauss Jun 6 '17 at 7:46
  • For to_tsvector/to_tsquery your solution lost this aspect, is complex, and slower than the "cool trick". But your solution is a good reference (!) to express any other alternative demand, when default text representation (for non-text data) is not adequate. – Peter Krauss Jun 6 '17 at 7:48

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