Multiple inserts PostgreSQL

Question

I'm trying to perform multiple inserts from 1 sql query. To break down what I'm trying to do here is the DB structure:

links:
  - id // uuid_generate_v4()
  - hash

permissions:
  - id // uuid_generate_v4()
  - name

photos:
  - id // uuid_generate_v4()
  - url

link_permissions:
  - link_id
  - permission_id

link_photo:
  - link_id
  - photo_id

Now whenever I insert a link I need to also insert its permissions and photos. This is the sql queries I've attempted so far.

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) 
INSERT INTO link_photo(link_id, photo_id) 
VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7'), 
INSERT INTO link_permission(link_id, permission_id) 
VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01')

and

WITH link as (
  INSERT INTO links(hash) VALUES ('my-random-hash') 
  RETURNING *
) 
(INSERT INTO link_photo(link_id, photo_id) 
  VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7')), 
 INSERT INTO link_permission(link_id, permission_id) 
 VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01')

How would I write this query?

user330315 · Accepted Answer · 2018-03-19 23:15:28Z

3

Just put the the second insert into another CTE:

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) , lp as (
  INSERT INTO link_photo(link_id, photo_id) 
  VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7')
)
INSERT INTO link_permission(link_id, permission_id) 
VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01');

Alternatively, don't use values, that makes the query a bit easier to read (I think)

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) , lp as (
  INSERT INTO link_photo(link_id, photo_id) 
  select id, '095ccacf-ebc1-4991-8ab0-cac13dac02b7'
  from link
)
INSERT INTO link_permission(link_id, permission_id) 
select id, '506f3302-fe9f-4982-8439-d6781f646d01'
from link;

answered Mar 19, 2018 at 23:15

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Dionei Miodutzki Over a year ago

I was unaware of this kind of combination!

Rodrigo Over a year ago

same. Is there any negatives to making the second statement a CTE also?

user330315 Over a year ago

@Rodrigo: no, there are no disadvantages

Dionei Miodutzki · Accepted Answer · 2018-03-19 19:32:27Z

1

Postgresql's "with query" only works with one statetment.

You can do it in "one run" by using an anonymous code block, but it will probably be a better idea to create a proper plpgsql insert function.

answered Mar 19, 2018 at 19:32

Dionei Miodutzki

6577 silver badges16 bronze badges

1 Comment

user330315 Over a year ago

You can do that with a CTE. No need for PL/pgSQL

Schwern · Accepted Answer · 2018-03-19 19:53:55Z

Now whenever I insert a link I need to also insert its permissions and photos.

There's several ways you can accomplish this. You can use returning id into link_id to place the link id into the variable link_id and reference that in later statements.

Since this all has to happen in concert, you can write a function to handle it. This ensures it always happens together, you don't have to try and cram it all into one statement, and it makes it easier to use variables. You can also add a transaction and error handling should any part fail.

create function add_link( photo_id uuid, permission_id uuid)
    returns uuid
    as
$func$
declare
    link_id uuid;
begin   
    insert into links(id, hash)
    values (default, 'probably have the function generate this?')
    returning id into link_id;

    insert into link_photo(link_id, photo_id)
    values (link_id, photo_id);

    insert into link_permission(link_id, permission_id)
    values (link_id, permission_id);

    return link_id;
end
$func$ language plpgsql;

test=# select * from add_link(uuid_generate_v4(), uuid_generate_v4());
               add_link               
--------------------------------------
 1c18b24c-c7ac-4169-8449-1c18c9087f8b
(1 row)

Joakim Danielson · Accepted Answer · 2018-03-19 19:33:49Z

0

Wouldn't your first attempt work (I haven't tried myself) if you replaced "RETURNING *" with "RETURNING id" and selecting that.

See the answer to this question for the full query

answered Mar 19, 2018 at 19:33

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Collectives™ on Stack Overflow

Multiple inserts PostgreSQL

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related