DBA tips ‘n tricks – search in every field in a table

Sometimes you just want to find some value, regardless of which column it's in. If the table has few columns you can easily:

=$ select * from table where a ~ '...' or b ~ '...' or c ~ '...'

But if there are many columns writing such query quickly becomes tedious. Luckily there is simple solution.

Pg has ROW datatype(s) – basically single value which contains many fields. For example:

=$ select u.* from pg_user u limit 1;
 usename │ usesysid │ usecreatedb │ usesuper │ userepl │ usebypassrls │  passwd  │ valuntil │ useconfig 
─────────┼──────────┼─────────────┼──────────┼─────────┼──────────────┼──────────┼──────────┼───────────
 depesz  │    16384 │ f           │ t        │ f       │ f            │ ********[null][null]
(1 row)

I can select this as single row value using:

=$ select u from pg_user u limit 1;
                 u                 
───────────────────────────────────
 (depesz,16384,f,t,f,f,********,,)
(1 row)

Now, if I'd cast this value to text datatype, I can search in it:

=$ select u.* from pg_user u where u::text ~ '22';
 usename │ usesysid │ usecreatedb │ usesuper │ userepl │ usebypassrls │  passwd  │           valuntil            │ useconfig 
─────────┼──────────┼─────────────┼──────────┼─────────┼──────────────┼──────────┼───────────────────────────────┼───────────
 user10  │    16391 │ f           │ f        │ f       │ f            │ ********2022-02-23 16:45:09.085676+01 │ [null]
(1 row)

Of course it will not use any index, because how could it? But for quick(?) checking what's what – it is surprisingly helpful.

5 thoughts on “DBA tips ‘n tricks – search in every field in a table”

  1. Hey @Depesz,

    Thanks for the articleS. Just wanted to say I appreciate you and your efforts to post stuff on this page. You are one of the only feeds I regularly receive AND read! I have a suggestion of something you could have on your site; a regular section labeled “Tips N Tricks” (not that you need another thing to do, I’m sure). Your straightforward style of presentation is tremendously helpful and easy to read and I often read your posts not necessarily because I am interested in the topic, but because I learn from what you post. An example, so you know I’m not just fan-boy: The other day you posted about autovacuum workers and used “psql -AtX” in the processing you were doing. Nothing special, nothing that is not in the manuals, and something I’m sure I could have plinked around and figured out, BUT something I had not tried before that I find very useful for generating data to use with graphing engines. A collection of Tips N Tricks easily found when you just can’t remember how you did something would be wonderful.

  2. Lol. Thanks. That’s awesome – clearly I didn’t see it before writing – my mistake. I did do a search before writing last time but didn’t have many hits. Was probably too specific in what I searched for. Thanks for the tip. 😉

  3. @Ben:
    heh. forgot about it. To explain myself – wordpress says that I wrote 757 blogposts with postgresql tag.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.