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

( Forgot to hit reply all, so probably someone will get this twice, sorry ).

Hi:

On Thu, Apr 3, 2014 at 4:41 PM, Ben Hoyt <benhoyt(at)gmail(dot)com> wrote:
.....
> Is there some way to do something like this:
> INSERT INTO images (filename) VALUES
> ('foo' || image_id_about_to_used_for_this_row::text || '.jpg')
> ('bar' || image_id_about_to_used_for_this_row::text || '.jpg')
> I tried using currval() to see if that'd work, but it gave an error, I guess
> because I was using it multiple times per session.

You normally need to call nextval before currval. Anyway, subqueries
are your friend:

psql (9.3.2)
Type "help" for help.

postgres=# create table files ( id serial primary key, file varchar);
CREATE TABLE
postgres=# \d+ files
Table "public.files"
Column | Type | Modifiers
| Storage | Stats target | Description
--------+-------------------+----------------------------------------------------+----------+--------------+-------------
id | integer | not null default
nextval('files_id_seq'::regclass) | plain | |
file | character varying |
| extended | |
Indexes:
"files_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

postgres=# select nextval('files_id_seq'::regclass) as id from
generate_series(1,3);
id
----
1
2
3
(3 rows)
postgres=# select newid as id, 'image_'||cast(newid as text)||'.jpg'
as file from ( select nextval('files_id_seq'::regclass) as newid from
generate_series(1,3)) as newids;
id | file
----+-------------
4 | image_4.jpg
5 | image_5.jpg
6 | image_6.jpg
(3 rows)

postgres=# insert into files (id, file) select newid as id,
'image_'||cast(newid as text)||'.jpg' as file from ( select
nextval('files_id_seq'::regclass) as newid from generate_series(1,3))
as newids returning *;
id | file
----+-------------
7 | image_7.jpg
8 | image_8.jpg
9 | image_9.jpg
(3 rows)

INSERT 0 3
postgres=# select * from files;
id | file
----+-------------
7 | image_7.jpg
8 | image_8.jpg
9 | image_9.jpg
(3 rows)

Francisco Olarte.