3

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?

11
  • Multiple columns are inserted? Did you mean rows? Mar 7 '16 at 10:40
  • oh sorry multiple rows Mar 7 '16 at 10:41
  • 2
    But really, why do you need 2 columns to store the same value? Will one be changed later? Mar 7 '16 at 10:41
  • yes, one column is change later Mar 7 '16 at 10:46
  • 1
    "I don't want to use a trigger". Well, there's your problem. "How do I start my car? I don't want to use the key." Well, you could open it up and fiddle with some wires which could work on a really old car if you know what you're doing ... or you could use the key." Mar 7 '16 at 11:45
5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.