How to retrieve the actual default value for a column before insertion

Question

I have a table in my postgres database that looks like this when I describe it.

                                  Table "public.statistical_outputs"
      Column       |           Type           |                            Modifiers                             
-------------------+--------------------------+------------------------------------------------------------------
 id                | bigint                   | not null default nextval('statistical_outputs_id_seq'::regclass)

I want to know what value will be inserted into the id column if I use a statement like

insert into statistical_outputs VALUES (DEFAULT);

I have tried things like

select nextval('id') from statistical_outputs;

but it does not work.

Possibly related questions:

postgresql sequence nextval in schema

PostgreSQL nextval and currval in same query

This questions is a possible duplicate of:

Get the default values of table columns in Postgres?

However, the answer given by Chris is the one I want without having to look at the information schema (which I think I tried but didn't work).

Answer 1

There's no way to do what you want directly - you can't preview the value.

Imagine:

regress=> CREATE TABLE crazy (blah integer, rand float4 default random());
CREATE TABLE
regress=> insert into crazy(blah, rand) values (1, DEFAULT);
INSERT 0 1
regress=> select * from crazy;
 blah |   rand   
------+----------
    1 | 0.932575
(1 row)

random() is a volatile function that returns a different value each time. So any attempt to preview the value would only get you a different value to the one that'll be inserted.

The same is true of nextval as concurrent transactions can affect the value - even if you directly read the current sequence position, which PostgreSQL tries to prevent you from doing (because it'll produce wrong results). It's just more obvious to think about this problem with random than nextval.

So, with a volatile default, all you can do is:

• Evaluate the default expression yourself, then supply the value in the insert, i.e. call SELECT nextval('statistical_outputs_id_seq') then INSERT INTO ... VALUES (..., 'the value from nextval()');

• Use RETURNING to obtain the generated value

I suggest the latter. The former is annoying and difficult in the general case, since a default can be any arbitrary expression.

Example for RETURNING:

regress=> insert into crazy(blah, rand) values (1, DEFAULT) RETURNING rand;
   rand   
----------
 0.975092
(1 row)

INSERT 0 1

Answer 2

The default value for a column that is a sequence will vary depending on the transaction and its relation to the current state of the MVCC.

That is to say, it will all depend on when you first get the value of the sequence, and what other transactions currently involve that sequence. i.e. the default will vary over time, dependent heavily on how other transactions are using that sequence.

The closest way to determine the default value (and again, this will vary over time) is to select the currval of the sequence (with the understanding that it's theoretically possible for another transaction to call nextval afterward and change it, although the usefullness of calling currval will depend on exactly what you want to do with the value).

Edit in response to comment:

@Craig Ringer points out that to call currval first requires a call to nextval, which is a fair point.