Postgresql – Search a string across all columns of a query result

postgresqlpostgresql-10

I have a query, which has around 50 columns and it is subject to change.

I need to modify my SQL-DML to search a specific string across all 50 columns.

How is this possible?

select
  *
from
  messages a
  FULL OUTER JOIN  customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id

where
  product_id != '0'
  and a.hide_question = '0'
  and a.seller_id in ('2', '3', '1', '6')
  AND a.market_place_id IN (
    SELECT
      MAX(market_place_id)
    FROM
      messages
    GROUP BY
      product_id
  )
order by
  a.market_place_id desc

So far the best alternative seem to be

SELECT * 
FROM MyTable 
WHERE Col1 LIKE '%foo%' OR 
      Col2 LIKE '%foo%' OR  
      Col3 LIKE '%foo%' OR 
      Col4 LIKE '%foo%' OR 
      Col5 LIKE '%foo%' OR 
      Col6 LIKE '%foo%'

Best Answer

A database is not a fancy mechanism to grep. This is a horrible idea don't do this ever. That said you can convert the row object to a tsvector using Full Text Search

SELECT to_tsvector(to_jsonb(t)) @@ 'bar'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);

or you can search it as if it was text.

SELECT t::text LIKE '%bar%'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);

Related Solutions

Postgresql – Filter and append data to row

You could use an inner join to filter for rows that share a (p_id, k_id) value with rows that have a (t_id, pos) of (1,1) (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
join    two as filter
on      filter.t_id = 1
        and filter.pos = 1
        and filter.p_id = base.p_id
        and filter.k_id = base.k_id
group by
        base.p_id
,       base.k_id;

Or an equivalent way using an exists subquery (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
where   exists
        (
        select  *
        from    two as filter
        where   filter.t_id = 1
                and filter.pos = 1
                and filter.p_id = base.p_id
                and filter.k_id = base.k_id
        )
group by
        base.p_id
,       base.k_id;

Updated to use json_agg from @ErwinBrandstetter's answer.

Postgresql – pg_restore: [custom archiver] could not read from input file: end of file

I would suggest you try to do something along the line of:

SELECT count(*) FROM mytable WHERE my_primary_key < value ;

... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.]

You will most probably need to alter the different settings that affect index usage, because you actually want to force the database to use the index, even if it actually must scan 99% of the table. You want it not to scan the page that booms.

If you can get SELECT to give you most of the data, you can then do something such as:

CREATE TABLE my_table_2 AS 
SELECT * FROM my_table WHERE my_primary_key < value;

and later on:

ALTER TABLE my_table RENAME TO my_table_old ;
ALTER TABLE my_table_2 RENAME TO my_table ;

... and you'll have all the data that could be retrieved. I wouldn't drop the old table, in case someone finds later on a better method of retrieving the missing info.

Best of luck.

Best Answer

Related Solutions

Postgresql – Filter and append data to row

Postgresql – pg_restore: [custom archiver] could not read from input file: end of file

Related Question