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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general