Set the default value for one column to another column in an INSERT statement

Question

I have a table, name it t1, with three integer columns c1, c2, c3. c1 has a default value:

not null default nextval 

For the INSERT statements that I'm executing now, I want c2 to have the same meaning as c1. This does not apply to most of my inserts, so it makes no sense to define c2 to have a default value or to have a trigger for updating. I am currently making two statements:

 INSERT INTO t1 (c3) VALUES (val3); UPDATE t1 SET c2 = c1 WHERE //Get correct row 

Answer 1

There is no guarantee that the items in the kit order will be processed. There is also no need to make two function calls. Use a subselect or CTE :

 INSERT INTO t (c1, c2, c3) SELECT xs, xs, val3 FROM (SELECT nextval('c1_seq') AS s) x; 

Or using CTE:

 WITH x(s) AS (SELECT nextval('c1_seq')) INSERT INTO t (c1, c2, c3) SELECT xs, xs, val3 FROM x; 

Answer 2

In your case, you need to set the default value for c2 to the current value of the sequence associated with c1, ie:

 ALTER TABLE t1 ALTER COLUMN c2 SET DEFAULT CURRVAL(PG_GET_SERIAL_SEQUENCE('t1', 'c1')); 

Of course, this will not work if you specify some explicit value for c1. If there are such cases, then you should make a BEFORE trigger to be sure that c2 will always be the same as c1 in the inset.

 CREATE FUNCTION sync_c2() RETURNS trigger AS $$ BEGIN IF NEW.c2 IS NULL THEN NEW.c2 := NEW.c1; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER sync_c2 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE sync_c2(); 

Answer 3

The default value for c1 will only be used if you do not provide an explicit value for it, or, of course, if you explicitly request the default value with default in your VALUES. You can also freely access the sequence using nextval and currval in your VALUES; The nextval function does the following:

Preliminary sequence and return of new value.

and currval :

Return value, last time received with nextval for the specified sequence

So you can do this to get the following sequence value for c1 and get the same value for c2 :

 insert into t1 (c1, c2, c3) values (nextval('c1_seq'), currval('c1_seq'), val3) 

where c1_seq is the name of the sequence that you use to provide default values ​​for c1 .