I am trying to write a query that tells if a column named s
in table a.t
has its default value (which is a very big base64 string). So I tried:
SELECT 1 FROM a.t WHERE s = (
SELECT column_default FROM information_schema.columns
WHERE (table_schema, table_name, column_name) = ('a', 't', 's'))
AND uname = 'joe';
Which didn't work, so I noticed that the result from information_schema.columns
had some stuff on it that the regular query did not:
SELECT column_default FROM information_schema.columns
WHERE (table_schema, table_name, column_name) = ('a', 't', 's');
column_default | 'data:image/png;base64,iVBO...QmCC'::text
Vs.
SELECT s FROM a.t WHERE uname = 'joe';
s | data:image/png;base64,iVBO...QmCC
Note the lack of quotes and explicit cast.
Is this why it does not match? Column s
is defined as type text
.
How can I change my query so I can test for equality between the column value and its default?
What you retrieve from the information schema (or the system catalogs in my solution) is just a string literal representing the expression. You need to actually execute it to get value. It can be a trivial cast like in your case or any other expression. That's where you need dynamic SQL. (Or concatenate a second query in your client from the results of the first query.)
Detailed explanation in this related answer:
Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
(You'll also find instructions for an alternative route without dynamic SQL over there.)
This DO
statement does the trick.
DO
$do$
DECLARE
_data text := 'data:image/png;base64,iVBO...QmCC';
_answer bool;
BEGIN
EXECUTE (
SELECT format('SELECT %s = $1', d.adsrc)
FROM pg_attribute a
JOIN pg_attrdef d ON (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
WHERE a.attrelid = 'a.t'::regclass -- schema.table
AND a.attname = 's'
)
USING _data
INTO _answer;
RAISE NOTICE '%', answer;
END
$do$;
For repeated use I would wrap this into a plpgsql function. There are many related answers.
Also be aware that column defaults can have side effects like increasing a sequence. Not in this particular case, but generally I'd advise to check the default before executing.