Postgresql – join value in a RETURNING which is not inserted

insertjoin;postgresql

I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "column doesn't exist" error.

INSERT INTO new_table (x,y) 
select A.x,B.y 
from A 
  join B on A.w = B.z 
RETURNING id,B.z;

The new_table has a unique constraint on (x,y).

The new_id and B.z are needed to insert into a second table.

Best Answer

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y) 
  select A.x, B.y 
  from A join B on A.w = B.z 
  returning id, x, y
)
-- insert into another_table (id, z)
select 
    ins.id, B.z        -- whatever columns from the 3 tables
from ins 
  join A on A.x = ins.x 
  join B on B.y = ins.y and A.w = B.z ;

Not valid integer types

If the values in the field are not actually valid integer types (digits and leading - only), a JOIN like @mustaccio suggested would fail with an exception: you couldn't cast to integer at all.

It would have to be the other (even more expensive) way round:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id::varchar = t2.id;

Also, since @mustaccio misleadingly suggested to use int8: Don't. integer equals int4. int8 would be bigint.

You can support that with a functional index:

CREATE INDEX tbl1_taxonomy_id_idx ON tbl1 (cast(taxonomy_id AS varchar));

Valid integer types

If we are dealing with valid integer types, you could just convert your column id to integer - if your setup allows that.

ALTER TABLE tbl2 ALTER COLUMN id TYPE integer USING id::int;

Then your problem is gone for good:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id;

Barring that (if you cannot convert the column for some reason), a functional index would help:

CREATE INDEX tbl2_id_idx ON tbl2 (cast(id AS int));

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;

Best Answer

Related Solutions

Join the two tables which possess one to many relationship

Postgresql – Join on different types

Not valid integer types

Valid integer types

Related Question