Question 211034 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to determine whether a row contains a default value in Postgres

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

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 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 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|    |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search