Re: Any way to insert rows with ID used in another column

Hi:

On Sun, Apr 6, 2014 at 2:14 AM, Ben Hoyt <benhoyt(at)gmail(dot)com> wrote:
> Thanks for the info, Francisco and Alban -- that looks useful.

May be.

> Can you see a good way in the INSERT to combine VALUES with that nextval()
> subquery? As there are some columns that are distinct for each row, and some
> that are the same or programmatically generated for each row. For instance,
> there's a "folder" column that's different for each inserted row, so
> typically I'd specify that directly in the multiple VALUES rows.

Relatively simple, replace the inner select ( the one using generate
series in the demo ) with a values statement and munge the outer
select wich combines it appropiately:

postgres=# insert into files (select id, dir || '/image_' || cast(id
as text) || '.' || ext as file from (values
(nextval('files_id_seq'::regclass), 'somedir','jpeg') ,
(nextval('files_id_seq'::regclass), 'someotherdir','gif')) as
source(id,dir,ext)) returning *;
id | file
----+---------------------------
12 | somedir/image_12.jpeg
13 | someotherdir/image_13.gif
(2 rows)

Munge as needed. Doing it with some WITHs makes for some more readable query:

postgres=# WITH
postgres-# source(id,dir,ext) as (
postgres(# VALUES (nextval('files_id_seq'::regclass), 'somedir',
'jpeg')
postgres(# , (nextval('files_id_seq'::regclass), 'someotherdir','gif')
postgres(# ),
postgres-# rows(id,file) as (
postgres(# SELECT id
postgres(# , dir || '/image_' || cast(id as text) || '.' || ext
postgres(# FROM source
postgres(# )
postgres-# INSERT INTO files (TABLE rows) RETURNING *;
id | file
----+---------------------------
20 | somedir/image_20.jpeg
21 | someotherdir/image_21.gif
(2 rows)

INSERT 0 2

Regards.
Francisco Olarte.