Question 114064 in PostgreSQL Question posted in
The official documentation can be found here.

Copy value from one column into another during insert using postgreSQL

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.

Tags:

2

Answers


  1. 0

    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

    Edouard makes makes a fair point that if you can specify the conditions when you want this behaviour, you can add them to the definition:

    CREATE TABLE mytable
(   id       SERIAL,
    content  TEXT,
    copyofid integer 
        generated always as (
        case when content ilike '%requires copying ID%' then id end)
        stored
);
insert into mytable (content) values ('abc') returning *;
-- id | content | copyofid
------+---------+----------
--  1 | abc     |
--(1 row)
insert into mytable (content) values ('abc, but requires copying ID') returning *;
-- id |           content            | copyofid
------+------------------------------+----------
--  2 | abc, but requires copying ID |        2
--(1 row)

    If they vary between inserts

    CREATE TABLE mytable                                                                                                                
(   id       SERIAL,
    content  TEXT,
    copyofid integer
        generated always as (
        case when should_copy_id then id end)
        stored,
    should_copy_id boolean default false
);
insert into mytable (content) values ('efg') returning *;
-- id | content | copyofid | should_copy_id
------+---------+----------+----------------
--  1 | efg     |          | f
--(1 row)
insert into mytable (content,should_copy_id) values ('klm','today'::date<>'2022-10-28'::date) returning *;
-- id | content | copyofid | should_copy_id
------+---------+----------+----------------
--  2 | klm     |        2 | t
--(1 row)

    The trigger will be better if

    1. the check is fairly complex – generated columns are pretty limited in terms of the definition complexity. For example, you can’t use mutable functions in them – not even STABLE are accepted
    2. you want to save the logic and change it later without having to drop the column each time, then re-add it with a new definition (only way to alter a generated column definition)
    3. as a part of the insert you’ll want to do more than just copy the id column
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search