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

This should be of help: http://stackoverflow.com/questions/23794405/generate-default-values-in-a-cte-upsert-using-postgresql-9-3/23797357#23797357 — Erwin Brandstetter, Jun 10 '14 at 22:31

Craig Ringer · Accepted Answer · 2014-06-10T07:23:53.933

3

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

edited Jun 10 '14 at 07:23

answered Jun 10 '14 at 02:35

Craig Ringer

307,061
76
688
778

Thanks. I think the bit I want is: `SELECT nexval('statistical_outputs_id_seq')` however this returns an erroor -> `HINT: No function matches the given name and argument types. You might need to add explicit type casts.` – Alex Jun 10 '14 at 02:56
I do not intend to use the retrieved value for `nextval` in an `insert` statement. My problem is that I am using `Insert... (DEFAULT)` however it is returning an error that the id already exists... So I am trying to work out what value for `id` it is trying to insert as diagnostics. – Alex Jun 10 '14 at 02:57
Argh, why didn't you say so?! Please post a *new question* with that detail to dba.stackexchange.com and post a link to it in a comment here. I'll take a look. – Craig Ringer Jun 10 '14 at 03:03
Sorry. I thought I would better be able to diagnose my postgres insert problem if I found out the answer to this problem. I am still quite curious how to retrieve the `nextval()` value. But agreed, perhaps this is a better question for dba; I will move it. – Alex Jun 10 '14 at 03:05
@Alex Please post a link when you do. – Craig Ringer Jun 10 '14 at 04:10
Hi @Craig, here is the question I wanted to ask: http://dba.stackexchange.com/questions/60802/fixing-table-structure-to-avoid-error-duplicate-key-value-violates-unique-cons I am still interested in your solution in returning the current default key value, except it doesn't work for my database. – Alex Jun 10 '14 at 06:21
@Alex "It doesn't work for my database". Specifics? Also, that seems to be a question from March by someone else, and not clearly related to what you're asking here. I'm confused,. – Craig Ringer Jun 10 '14 at 06:25
Ok, I'll port my question to dba. I ran the command you suggested and the error is in my first comment to your answer. The ultimate question I was solving is in my second comment (sorry about the confusion!), and this has been solved by the link I posted. Basically, I had manually inserted id values into the id column, but this does not increment `nextval()` automatically. The fix was to reset the numbering of `nextval()`, which I manually did by inserting values until the right value for `nextval()` was reached. – Alex Jun 10 '14 at 06:48
1

I typo'd it as `nexval` not `nextval`, and you copied the typo ;-) . If you want to renumber a sequence, use `setval`. See the manual. – Craig Ringer Jun 10 '14 at 07:23
Thanks @Craig, it worked after the typo was fixed :p – Alex Jun 10 '14 at 23:21

score 0 · Answer 2 · answered Jun 10 '14 at 02:36

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.

You can't actually do that, because `currval` isn't defined until you fetch the sequence within a session. Try it and see. You'll get `ERROR: currval of sequence "xxx" is not yet defined in this session` — Craig Ringer, Jun 10 '14 at 02:37

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

2 Answers2