'select/delete can not use default in postgresql?

begin;
create table test101(col1 int default 2, col2  text default 'hello world');
insert into test101 values (1,default);
insert into test101 values (default,default);
insert into test101 values (default,'dummy');
insert into test101 values (5,'dummy');
commit;

update: OK.
update test101 set col2 = default where col1 = 4;
select, delete not OK.

select * from test101 where col1 =  COALESCE (col1,default);
delete from test101 where  col1 =  COALESCE (col1,default);

error code:

ERROR:  42601: DEFAULT is not allowed in this context
LINE 1: delete from test101 where  col1 =  COALESCE (col1,default);
                                                          ^
LOCATION:  transformExprRecurse, parse_expr.c:285

also tried: delete from test101 where col1 = default;
default value is not easy to find. Get the default values of table columns in Postgres? Then select/delete operation with default operation is not that weird.



Solution 1:[1]

In the question you linked to they do:

SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'test101')
ORDER BY ordinal_position;

which produces something like:

 column_name |   column_default
-------------+---------------------
 col1        | 2
 col2        | 'hello world'::text

Maybe, you can combine this query with your query? (But I would not recommend it, because ... )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk