From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Any way to insert rows with ID used in another column |
Date: | 2014-04-03 15:17:42 |
Message-ID: | CA+bJJbx8uPo6G-exZ3wbth2=WQtVfAscsC-uMmVxmnqd3JQ37Q@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
( 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2014-04-03 15:19:56 | Re: Any way to insert rows with ID used in another column |
Previous Message | Thomas Kellerer | 2014-04-03 15:01:36 | Re: COPY v. java performance comparison |