Check the whole table for one value
Background: I am converting a database table to a format that does not support null values. I want to replace null values with an arbitrary number so that my application can support null values.
Question: I would like to search my table for the entire value (for example, "999999") to make sure it does not appear in the table. I could write a script to check each column separately, but I wanted to know if there is a way to do this in pure sql without listing each field. Is it possible?
source to share
You can use a special system function like PostgreSQL:
SELECT *
FROM tbl t
WHERE t::text LIKE '%999999%';
There is a composite type with the same name for every table created in PostgreSQL. And there is a view text
for every type in PostgreSQL (for I / O values).
So you can just pass the whole string in text
, and if the string "999999" is contained in any column (its exact representation text
, to be precise) it will be guaranteed to be shown in the query above.
You cannot completely eliminate false positives , although the delimiters and / or decorators used by Postgres to represent strings can be part of the search query. This is highly unlikely. And positively not for your search term "999999".
There was a very similar question on codereview.SE recently. I added some more explanation in my answer there .
source to share
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)
source to share