Re: Compare with default value?

On 3/13/21 1:05 PM, Ulrich Goebel wrote:
> I would like to get the rows, where a column has the default value,
> similar to:
>
> select id fromt tbl where col = default
>
> Is there a chance?

It isn't pretty, and not all that practical, but for example:

8<--------------------
create table def(id int, f1 text not null default 'foo');

insert into def values(1,'hello'),(2,default),(3,default),(4,'bar');

SELECT *
FROM def
WHERE $$'$$ || f1 || $$'::text$$ =
(SELECT pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_attrdef d
JOIN pg_attribute a on d.adrelid = a.attrelid
AND d.adnum = a.attnum
WHERE a.attrelid = 'def'::regclass
AND a.attname = 'f1');
id | f1
----+-----
2 | foo
3 | foo
(2 rows)
8<--------------------

Hope this helps,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development