sqlpostgresqldynamic-sqlpostgresql-9.3column-defaults

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


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?


Solution

  • 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.