How to use column default in the where clause to identify default value

Question

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?

Answer 1

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.