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

Thanks for the info, Francisco and Alban -- that looks useful.

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.

-Ben

On Thu, Apr 3, 2014 at 11:17 AM, Francisco Olarte <folarte(at)peoplecall(dot)com>wrote:

> ( 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>