Copy value from one column into another during insert using postgreSQL-CodePudding

If I have a table like this:

CREATE TABLE mytable
(
    id       SERIAL,
    content  TEXT,
    copyofid INTEGER
);

Is there a way to copy id into copyofid in a single insert statement?

I tried: INSERT INTO mytable(content, copyofid) VALUES("test", id);

But that doesn't seem to work.

CodePudding user response：

You can find the sequence behind your serial column using pg_get_serial_sequence() and access it using currval() to get what serial column just got as a result of your INSERT.

CREATE TABLE mytable
(
    id       SERIAL,
    content  TEXT,
    copyofid INTEGER
);

--this works for a single-record insert
INSERT INTO mytable
  (content, copyofid) 
  VALUES
  ('test', currval(pg_get_serial_sequence('mytable','id')));

--inserting more, you'll have to handle both columns relying on the sequence
INSERT INTO mytable
( id, 
  content, 
  copyofid) 
  VALUES
( nextval(pg_get_serial_sequence('mytable','id')),
  'test3', 
  currval(pg_get_serial_sequence('mytable','id'))),
( nextval(pg_get_serial_sequence('mytable','id')),
  'test4', 
  currval(pg_get_serial_sequence('mytable','id')));

table mytable;
-- id | content | copyofid
------ --------- ----------
--  1 | test    |        1
--  2 | test3   |        2
--  3 | test4   |        3
--(3 rows)

Fiddle

CodePudding user response：

The solution is to create a trigger function which is fired before inserting a new row in table mytable and which copy NEW.id into NEW.copyofid if a condition is true :

CREATE OR REPLACE FUNCTION before_insert_mytable() RETURN trigger LANGUAGE plpgsql AS $$
BEGIN
  IF condition
  THEN NEW.copyofid = NEW.id ;
  END IF ;
  RETURN NEW ;
END ; $$

CREATE OR REPLACE TRIGGER before_insert_mytable BEFORE INSERT ON mytable
FOR EACH ROW EXECUTE FUNCTION before_insert_mytable () ;

The condition can also be stated directly in the WHEN clause of the trigger instead of in the function :

CREATE OR REPLACE FUNCTION before_insert_mytable() RETURN trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.copyofid = NEW.id ;
  RETURN NEW ;
END ; $$

CREATE OR REPLACE TRIGGER before_insert_mytable BEFORE INSERT ON mytable
WHEN condition
FOR EACH ROW EXECUTE FUNCTION before_insert_mytable () ;

see the manual