Reference Value of Serial Column in Another Column During Same Insert

Reference value of serial column in another column during same INSERT

You could use a CTE to retrieve the value from the sequence once and use it repeatedly:

WITH cte AS (
   SELECT nextval('foo_id_seq') AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   cte;

The CTE with a data-modifying command requires Postgres 9.1 or later.

If you are not sure about the name of the sequence, use
pg_get_serial_sequence() instead:

WITH i AS (
   SELECT nextval(pg_get_serial_sequence('foo', 'id')) AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   i;

If the table name "foo" might not be unique across all schemas in the DB, schema-qualify it. And if the spelling of any name is non-standard, you have to double-quote:

pg_get_serial_sequence('"My_odd_Schema".foo', 'id')

---

Quick tests indicated @Mark's idea with lastval() might work too:

INSERT INTO foo (ltree) VALUES ('1.' || lastval());

• You can just leave id out of the query, the serial column will be assigned automatically. Makes no difference.

• There shouldn't be a race condition between rows. I quote the manual:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has
never been called for this sequence in this session.) Because this is
returning a session-local value, it gives a predictable answer whether
or not other sessions have executed nextval since the current session
did.

This function requires USAGE or SELECT privilege on the sequence.

lastval

Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of
taking the sequence name as an argument it refers to whichever
sequence nextval was most recently applied to in the current session.
It is an error to call lastval if nextval has not yet been called in
the current session.

This function requires USAGE or SELECT privilege on the last used sequence.

Bold emphasis mine.

But, as @Bernard commented, it can fail after all: there is no guarantee that the default value is filled (and nextval() called in the process) before lastval() is called to fill the 2nd column ltree. So stick with the first solution and nextval() to be sure.

how can I reference another column inside postgres insert query?

It sounds like you want to default the coder to something based on the id. Unfortunately, this doesn't work in Postgres:

create table tab (
    id   integer primary key generated always as identity,
    code text not null default ('code '||id::text),
    data text
);

One option is a single statement that does both an insert and update:

with i as (
      insert into tab (code, data)
           values ('', 'my data'
           returning *
     )
update tab
    set code = 'code: ' || id::TEXT
    where tab.id in (select i.id from i);

Another is to use a trigger that assigns the value.

MySQL - if value in column then insert value in another column

Thats not insert, thats update,and you can do it as

update table_name
set 
columnB = 
case when columnA = 15 then 'text' end

What happens when you insert rows in a SERIAL column with existing values?

The underlying SEQUENCE will not care about the values you inserted (explicitly providing values for the serial column, overruling the default), you have to adapt manually to avoid duplicate key errors:

SELECT setval(pg_get_serial_sequence('tbl', 'id'), max(id)) FROM tbl;

'tbl' and 'id' being the names of table and column respectively.

Related:

• How to reset postgres' primary key sequence when it falls out of sync?
• How to copy both structure and contents of PostgreSQL table, but duplicate sequences?

Insert single column return value into multiple columns of another table

First of all you must add some constraint in children_A table and children_B table for upsert. Now to achieve the above insert you can try like below:

 with
  children_A_insert as (  
    insert into children_A(value)
    values ('John')
    on conflict (value)
    do
    update set value=EXCLUDED.value
    returning id  
),
  children_B_insert as (
    insert into children_B(value)
    values ('Terry')
    on conflict (value)
    do
    update set value=EXCLUDED.value
    returning id
  )

insert into parent_c (child_A_id,child_B_id) 
values((select id from children_A_insert), 
(select id from children_B_insert))

DEMO

NOTE: Above will work if you are inserting only one value in each table at a time

Insert inserted id to another table

Avoid rules, as they'll come back to bite you.

Use an after trigger on table a that runs for each row. It should look something like this (untested):

create function a_ins() returns trigger as $$
begin
  insert into b (a_id) values (new.id);
  return null;
end;
$$ language plpgsql;

create trigger a_ins after insert on a
for each row execute procedure a_ins();

---

Related Topics