2

Background: I'm converting a database table to a format that doesn't support null values. I want to replace the null values with an arbitrary number so my application can support null values.

Question: I'd like to search my whole table for a value ("999999", for example) to make sure that it doesn't appear in the table. I could write a script to test each column individually, but I wanted to know if there is a way I could do this in pure sql without enumerating each field. Is that possible?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
canisrufus
  • 665
  • 2
  • 6
  • 19

2 Answers2

4

You can use a special feature of the PostgreSQL type system:

SELECT *
FROM   tbl t
WHERE  t::text LIKE '%999999%';

There is a composite type of the same name for every table that you create in PostgreSQL. And there is a text representation for every type in PostgreSQL (to input / output values).

Therefore you can just cast the whole row to text and if the string '999999' is contained in any column (its text representation, to be precise) it is guaranteed to show in the query above.

You cannot rule out false positives completely, though, if separators and / or decorators used by Postgres for the row representation can be part of the search term. It's just very unlikely. And positively not the case for your search term '999999'.

There was a very similar question on codereview.SE recently. I added some more explanation in my answer there.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Any particular reason you use `~~` instead of the standard LIKE operator? –  Apr 03 '12 at 16:57
  • @a_horse_with_no_name: No particular reason. You probably know that, but an odd fact for the general public: while being identical operators, `~~` ranks higher in [operator precedence](http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE) than `LIKE`. So the expression `'ab' LIKE 'a' || '%'` needs parentheses with `~~`: `'ab' ~~ ('a' || '%')`. – Erwin Brandstetter Apr 03 '12 at 18:16
  • Thanks. No, I didn't know that about the operator precendence. I try to use standard SQL wherever possible (so I never use `~~`). That makes switching between SQL dialects easier. –  Apr 03 '12 at 18:20
  • @a_horse_with_no_name: I'd say that's good practice. I amended my example to support that (even though this is Postgres specific anyway). I find `~~` slightly easier to read, and easier to switch between `~` (regexp match) and `~~`, and I mostly work with PostgreSQL anyway, so I like the shorter operator. – Erwin Brandstetter Apr 03 '12 at 18:25
  • @ErwinBrandstetter I'm not really conversant with SQL, and except due to context, I didn't know what ~~ meant. Thank you for your answer. – canisrufus Apr 04 '12 at 13:22
0
create or replace function test_values( real ) returns setof record as
$$
declare
query text;
output record;
begin
for query in select 'select distinct ''' || table_name || '''::text table_name, ''' || column_name || '''::text column_name from '|| quote_ident(table_name)||' where ' || quote_ident(column_name) || ' = ''' || $1::text  ||'''::' || data_type  from information_schema.columns where table_schema='public'   and numeric_precision is not null
loop
    raise notice '%1 qqqq', query;
    execute query::text into output;
    return next output;
end loop;
return;
end;$$ language plpgsql;

select distinct * from test_values( 999999 ) as t(table_name text ,column_name text)
quzary
  • 285
  • 1
  • 4