select/delete can not use default in postgresql?

Question

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.

Per the docs [Create Table](https://www.postgresql.org/docs/current/sql-createtable.html); "The DEFAULT clause assigns a default data value for the column whose column definition it appears within. ... The default expression will be used in any insert operation that does not specify a value for the column." It works in `UPDATE` as in Postgres that is essentially an `INSERT` of a new row and a `DELETE` of an old row. — Adrian Klaver, Mar 09 '22 at 16:14

score 1 · Answer 1 · answered Mar 09 '22 at 16:11

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

select/delete can not use default in postgresql?

1 Answers1