1

I know that you can use = DEFAULT in UPDATE statements, e.g.

UPDATE my_table
SET some_column = DEFAULT
WHERE id = 1;

Is it possible to look for defaults in a SELECT statement? e.g.

SELECT id FROM my_table
WHERE some_column = DEFAULT;

That specific syntax gives the error "DEFAULT is not allowed in this context."

Bbrk24
  • 739
  • 6
  • 22

2 Answers2

2

No, you cannot do that, and since a column's DEFAULT value can be an expression, it would be difficult to fake.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

This answer shows how to get the DEFAULT value stored in the information_schema.

The problem is that the default value is stored as text, so you see e.g. a string 'hello'::text or expression (1 + 1) which is difficult to compare with the column value.

Anyway you may use a trick using a helper table illustrated below.

Create table

create table mytable (
id int ,
col text default 'hello',
col2 int default 1,
col3 int
);

and put some data in it

insert into mytable(id) values(1);
insert into mytable(id,col,col2,col3) values(2,'x',3,10);

now create a clone table (like) and insert one row with DEFAULT values

drop table get_default;
create table get_default ( like mytable including all);
insert into get_default DEFAULT VALUES;

query is trivial only you must hanle the case of null default value in extra OR predicate

select * from   mytable
where col3 = (select col3 from get_default) or
col3 is null and (select col3 from get_default) is null;

id|col  |col2|col3|
--+-----+----+----+
 1|hello|   1|    |

But note that your approach will fail badly if you use non deterministic DEFAULT values such as now() or nextval.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • That certainly works. Would it make sense to `CREATE TEMPORARY TABLE get_default`? – Bbrk24 Jul 19 '23 at 11:08
  • Depends on your use case. If you need the table only in the current session or transaction - then why not to use a `temporary table`? @Bbrk24 – Marmite Bomber Jul 19 '23 at 11:29