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?

As long as you don't know all possible solutions, any or all of them can depend on the version in use. For instance, `format()` was introduced with version 9.1 ... — Erwin Brandstetter, Jun 02 '14 at 18:46

score 2 · Accepted Answer · edited May 23 '17 at 10:32

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.

Thanks for the explanation - I don't need a general solution but now I understand what is happening, so I realize I can just select `left(right(column_default, -1), -7)` from the info schema and get it to work. — user9645, Jun 02 '14 at 18:39
@user9645: You may also be interested in the alternative route without dynamic SQL I applied in [the other answer I linked to](http://stackoverflow.com/questions/23794405/generate-default-values-in-a-cte-upsert-using-postgresql-9-3/23797357#23797357). Basically: create a temp copy of the table and insert a dummy row (which materializes a default value). — Erwin Brandstetter, Jun 02 '14 at 18:43

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

1 Answers1