Postgresql Insert from Select Returning Id

PostgreSql INSERT FROM SELECT RETURNING ID

You just need to add a RETURNING id to your INSERT ... SELECT:

WITH rows AS (...)
INSERT INTO dealer (user_id)
    SELECT id
    FROM rows
    RETURNING id;

Demo: http://sqlfiddle.com/#!12/75008/1

Can I use return value of INSERT...RETURNING in another INSERT?

You can do so starting with Postgres 9.1:

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows

In the meanwhile, if you're only interested in the id, you can do so with a trigger:

create function t1_ins_into_t2()
  returns trigger
as $$
begin
  insert into table2 (val) values (new.id);
  return new;
end;
$$ language plpgsql;

create trigger t1_ins_into_t2
  after insert on table1
for each row
execute procedure t1_ins_into_t2();

INSERT INTO ... FROM SELECT ... RETURNING id mappings

This would be simpler for UPDATE, where additional rows joined into the update are visible to the RETURNING clause:

• Return pre-UPDATE column values using SQL only

The same is currently not possible for INSERT. The manual:

The expression can use any column names of the table named by table_name

table_name being the target of the INSERT command.

You can use (data-modifying) CTEs to get this to work.

Assuming title to be unique per query, else you need to do more:

WITH sel AS (
   SELECT id, title
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel
   RETURNING id, title
 )
SELECT ins.id, sel.id AS from_id
FROM   ins
JOIN   sel USING (title);

If title is not unique per query (but at least id is unique per table):

WITH sel AS (
   SELECT id, title, row_number() OVER (ORDER BY id) AS rn
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   ORDER  BY id
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel ORDER  BY id  -- ORDER redundant to be sure
   RETURNING id
 )
SELECT i.id, s.id AS from_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN   sel s USING (rn);

This second query relies on the undocumented implementation detail that rows are inserted in the order provided. It works in all current versions of Postgres and is probably not going to break.

db<>fiddle here

_{Old sqlfiddle}

INSERTING and RETURNING the ID of a new or existing record, need a better strategy in Postgres 12.5

That just how on conflict works. When no row is inserted, nothing is returned.

A workaround is to rephrase the logic with a CTE: first insert (or do nothing), then select again from the table, using the input data for filtering.

with 
    data as (
        select * 
        from (values 
            ('hello','world','passthrough.hello.world')
        ) v(schema_name, target_name, target_path) 
    ),
    ins as (
        insert into pgconfig_target (schema_name, target_name, target_path) 
        select * 
        from data
        on conflict (schema_name, target_name, target_path) do nothing
    )
select c.id 
from pgconfig_target c
inner join data d using (schema_name, target_name, target_path)

How to insert and then update returned Id from insert query as returning id in a single command in postgres?

You can do that right within the INSERT statement:

INSERT INTO items 
  (name, user_id) 
values 
  ('pen', currval(pg_get_serial_sequence('items','id')));

Online example

Get id from INSERT or SELECT

Your function can be simplified some more. More importantly, you can fix the built-in race condition:

CREATE OR REPLACE FUNCTION public.insert_city(name_city1   varchar
                                            , country1     varchar
                                            , province1    varchar
                                            , region1      varchar
                                            , cap1         varchar
                                            , nationality1 varchar)
  RETURNS integer AS
$func$
   WITH ins AS (
      INSERT INTO city
            (name_city , country , province , region , cap , nationality ) 
      VALUES(name_city1, country1, province1, region1, cap1, nationality1)
      ON     CONFLICT (name_city) DO UPDATE
      SET    name_city = NULL WHERE FALSE  -- never executed, but locks the row!
      RETURNING id_city
      )
   SELECT id_city FROM ins
   UNION  ALL
   SELECT id_city FROM city WHERE name_city = name_city1  -- only executed if no INSERT
   LIMIT  1;
$func$  LANGUAGE sql;

Major points

• Assuming you run Postgres 9.5 or later, since you did not declare it.

• Use the new faster UPSERT solution INSERT .. ON CONFLICT ...
  Detailed explanation:

  • Is SELECT or INSERT in a function prone to race conditions?

• You need a UNIQUE constraint on name_city for this.

• About UNION ALL ... LIMIT 1:

  • Way to try multiple SELECTs till a result is available?

• Can be achieved with a single SQL command using a data-modifying CTE. This is least vulnerable to lock contention or other concurrency issues. It's shortest and fastest even without concurrent access.

• The function can be a simpler SQL function. (But plpgsql isn't wrong or bad either.)

• Don't abuse ALIAS FOR to attach names to parameters. That's explicitly discouraged in the manual. Use proper parameter names. The manual:

  It's best to use it only for the purpose of overriding predetermined names.

Returning ID from the upsert procedure in language sql postgresql

A procedure cannot return a value. Change the procedure to a function and declare it as int or bigint as return type:

CREATE OR REPLACE FUNCTION locations_upsert(
  _name        TEXT,
  _address     TEXT,
  _postal_code TEXT,
  _country     TEXT
) RETURNS bigint
AS $$ 
  INSERT INTO locations  
    (name, address, postal_code, country) VALUES
    (_name,_address,_postal_code,_country)
   ON CONFLICT (name)
   DO UPDATE SET 
     address = _address,
     postal_code = _postal_code,
     country = _country
   RETURNING id;    
$$ LANGUAGE sql;

Alternatively you can use the table name as return data type - RETURNS locations -, which will enable you to return the whole record - RETURNING *:

CREATE OR REPLACE FUNCTION locations_upsert(
  _name        TEXT,
  _address     TEXT,
  _postal_code TEXT,
  _country     TEXT
) RETURNS locations
AS $$ 
  INSERT INTO locations  
    (name, address, postal_code, country) VALUES
    (_name,_address,_postal_code,_country)
    ON CONFLICT (name)
    DO UPDATE SET 
      address = _address,
      postal_code = _postal_code,
      country = _country
  RETURNING *;  
$$ LANGUAGE sql;

Demo: db<>fiddle

Further reading: PostgreSQL CREATE PROCEDURE

---

Related Topics