Store same sequence value in two columns

Question

I have a table in which one column is auto increment sequence number. I want the same value to be stored in another column as well (so it can be changed later).

My table structure is like this:

CREATE TABLE Test
(
    test_id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    ...
    ...
    uid integer not null DEFAULT currval('test_id_seq'::regclass),
)

I want uid = test_id but the problem is when multiple row are inserted in one sessions. Then currval doesn't take the correct id.

I don't want to use a trigger. How can I manage it?

Answer 1

In order to not rely at all on currval, you might transform the INSERT queries in systematic way following this pattern:

• To insert from a select, instead of :

  INSERT INTO Test(columns) SELECT columns FROM...;

  consider this form:

  INSERT INTO Test(test_id, uid, columns) 
  SELECT pk,pk,columns FROM
  (SELECT nextval('test_id_seq') as pk, columns FROM ...) AS alias;
  

• For a single row in a VALUES clause, instead of

  INSERT INTO Test(columns) VALUES(...values here...)
  

  consider:

  INSERT INTO Test(test_id, uid, columns...) 
  SELECT pk,pk,columns FROM
   (SELECT nextval('test_id_seq') as pk, ...values here...) AS alias;
  

• for VALUES clauses with multiple rows, instead of:

  INSERT INTO Test(columns) VALUES(...values1...),(...values2...)
  

  consider:

   WITH v(columns...) AS ( VALUES(...values1...),(...values2...) )
   INSERT INTO Test(test_id, uid, columns) 
   SELECT pk,pk,columns... FROM
    (SELECT nextval('test_id_seq') as pk, v.columns... FROM v) AS alias;
  

---

If PostgreSQL had a standard-compliant NEXTVAL, you could do without the subquery and just use something like:

INSERT INTO ... SELECT NEXT VALUE FOR seqname,
                       NEXT VALUE FOR seqname,
                       other columns...

and the NEXT VALUE FOR seqname would evaluate to the same value for both columns of the same output row, as mandated by the standard. But PostgreSQL does not have this construct.