PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert sql but all rows have the same id (no unique constraint)

I’m trying to make an insert where the first row is the default item and the other rows are the variants with different language (making a primary key with id and locale) and I was thinking on something like this but it’s not working:

with data as (
    select uuid_generate_v4() as uuid
)
insert into content (id, type, locale)
select
(uuid, 'page', 'en-us'),
(uuid, 'page', 'es-mx')
from data

I tried using a CTE to get an UUID and then use that for all the inserts for that I need to use SELECT instead of VALUES, I also need to return results.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found this solution but I'm not sure if it can be improved:

    with data as (
    select uuid_generate_v4() as uuid
)
insert into content (id, type, locale)
select data.uuid, 'page', 'en-us'
from data
union
select data.uuid, 'page', 'es-mx'
from data

    So with this I can add N times of variants for each language using the same uuid.


  2. 0

    You can use unnest() to convert an array to multiple rows, (and string_to_array for splitting a string into an array):

    insert into content (id, type, locale) 
select uuid_generate_v4(), 'page', unnest(string_to_array('en-us,es-mx',','))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search