Check a whole table for a single value

Question

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?

Answer 1

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.

Answer 2

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)