'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 |
