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”
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.
did you read https://www.depesz.com/tag/tnt/ ?
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. 😉
You posted a similar tip some 12 years ago.
heh. forgot about it. To explain myself – wordpress says that I wrote 757 blogposts with postgresql tag.
Comments are closed.