Postgresql Nested Inserts/Withs for Foreign Key Insertions

PostgreSQL nested INSERTs / WITHs for foreign key insertions

Don't nest the common table expressions, just write one after the other:

WITH Y AS (
  INSERT INTO A (foo)
  VALUES ('abc')
  RETURNING id
), x as (
  INSERT INTO B (a_id, bar)
  SELECT id, 'def'
  FROM Y
  RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;

How postgresql multiple insert works when there is foreign key constraint in the table itself?

Foreign key constraints are implemented with triggers in PostgreSQL. This is not directly documented, but you can see it indirectly here:

One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints).

You can also see it with

SELECT * FROM pg_trigger
WHERE tgrelid = 'schools'::regclass;

The firing rules for triggers are documented and apply to foreign keys as well:

Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

(Emphasis mine)

So foreign keys are validated after the complete statement is done.

Postgres insert value from insert in other table

You need a common table expression for this kind of insert chaining:

with ta as (
  INSERT INTO tbl_b (status) VALUES ('OK') 
  RETURNING id
)
INSERT INTO tbl_a (name, tbl_b_reference) 
VALUES ('myName', (select id from ta));

Another option is to simply use the lastval() function to reference the last generated sequence value:

INSERT INTO tbl_b (status) VALUES ('OK');
INSERT INTO tbl_a (name, tbl_b_reference) 
  VALUES ('myName', lastval());

Note that you must not have any other statements that generate sequence values between those two.

Or use the currval() function:

INSERT INTO tbl_b (status) VALUES ('OK');
INSERT INTO tbl_a (name, tbl_b_reference) 
  VALUES ('myName', currval('tbl_b_id_seq'));

'tbl_b_id_seq' is the standard name Postgres uses for a sequence that is created for a serial column:

Insert a value from a table in another table as foreign key

You have two options.

The first one is using the lastval() function which returns the value of the last generated sequence value:

insert into cinema(name, is_active) values ('Cinema One', true); 
insert into theater(cinema_id) values (lastval());

Alternatively you can pass the sequence name to the currval() function:

insert into theater(cinema_id) 
values (currval(pg_get_serial_sequence('cinema', 'id')));

Alternatively you can chain the two statements using a CTE and the returning clause:

with new_cinema as (
   insert into cinema (name, is_active)   
   values ('Cinema One', true)
   returning id
)
insert into theater (cinema_id)
select id
from new_cinema;

In both statements I assume theater.id is also a generated value.

PostgreSQL insert into multiple tables, using foreign key from first insertion in the second insertion

If you want to avoid problems with duplicates you should use a DISTINCT clause in the first INSERT. This will make two sections point to the same media_file but it should be more than okay. (Upgraded from @a_horse_with_no_name answer)

WITH ids AS (
    INSERT INTO media_files (url, mimetype)
    SELECT DISTINCT file_url, 'image/jpeg'
    FROM sections
    WHERE file_url IS NOT NULL
    RETURNING id AS media_file_id, url AS file_url
)
INSERT INTO attachments (media_file_id, section_id)
SELECT ids.media_file_id, s.id
FROM ids
  JOIN sections s ON s.file_url = ids.file_url;

Fiddle

PostgreSQL - Insert data into multiple tables simultaneously

The idea is to write WITH clauses that contain INSERT ... RETRUNING to return the generated keys. Then these “views for a single query” can be used to insert those keys into the referencing tables.

WITH par_key AS
        (INSERT INTO participante (nome) VALUES ('Laurenz') RETURNING id),
     ven_key AS
        (INSERT INTO venda (inicio) VALUES (current_date) RETURNING id),
     item_key AS
        (INSERT INTO item (nome) VALUES ('thing') RETURNING id)
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valor)
   SELECT ven_key.id, item_key.id, par_key.id, numeric '3.1415'
   FROM par_key, ven_key, item_key;

Can INSERT [...] ON CONFLICT be used for foreign key violations?

Yes, join your input rows to the referenced table, thereby removing rows without a match on the FK column:

INSERT INTO entries(entry_id, referenced_id, name)
SELECT val.entry_id, val.referenced_id, val.name
FROM  (
  VALUES (1, 2, 'references two')
         -- more?
  ) val (entry_id, referenced_id, name)
JOIN   referenced USING (referenced_id)  -- drop rows without matching FK
ON     CONFLICT (entry_id) DO NOTHING;   -- drop rows with duplicate id

The UPSERT itself (INSERT ... ON CONFLICT DO NOTHING) only reacts to unique violations. The manual:

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

Since the VALUES expression is now not attached to an INSERT directly, column types are not derived from the target table. You may need to cast input values explicitly when operating with non-basic types. See:

• Casting NULL type when updating multiple rows

---

Related Topics