June 24th, 2010 by depesz | Tags: , , , | 4 comments »
Did it help? If yes - maybe you can help me?

Every so often you might need to find a value regardless of which column it's in.

Of course not in application code, as this would be very slow. But you might be in situation where you just don't know where did application get some value from, and you want to find it in table. Table that has 1-2 fields is trivial to search, but if you have 15 columns, naming them all in WHERE clause is tedious.

In such case you can use simple trick which represents whole row as text.

From PostgreSQL 8.3 you can simply:

select t.*
from your_table t
where t::text ~* 'what_you_are_looking_for';

If you're on 8.2 (sorry, I don't have any Pg installation with 8.1 or earlier versions), you will need to cast it “manually":

select t.*
from your_table t
where textin( record_out(t) ) ~* 'what_you_are_looking_for';

And that's all. Of course – this is not indexable, and will require full table scan, but sometimes you just have to find out where this value came from.

  1. 4 comments

  2. # Frank
    Jun 24, 2010

    Great tip! Thanks

  3. # Koszi
    Jun 24, 2010

    Where do You get that knowledge from? And don’t tell me it is all in manual.

  4. Jun 24, 2010

    irc, and generally playing with stupid ideas.

  5. # motzel
    Jun 25, 2010

    In 8.4 you can also use “magic” column t.name to achieve the same result, ie:

    SELECT t.name FROM table t;

    (using alias is mandatory, table can not contain column “name”).

    In 8.3 there is another “magic” column – t.text

    If you want to know where it comes from just read Depesz’s answer to my post:

Leave a comment