Re: Any way to insert rows with ID used in another column - Mailing list pgsql-general
| From | Ben Hoyt |
|---|---|
| Subject | Re: Any way to insert rows with ID used in another column |
| Date | |
| Msg-id | CAL9jXCFh7TzNREr-yLAi7yFPneVxGk+7iiB6qnWa0PnnvOtpUw@mail.gmail.com Whole thread Raw |
| In response to | Re: Any way to insert rows with ID used in another column (Francisco Olarte <folarte@peoplecall.com>) |
| Responses |
Re: Any way to insert rows with ID used in another column
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Any way to insert rows with ID used in another column (Alban Hertroys <haramrae@gmail.com>) Re: Any way to insert rows with ID used in another column (Francisco Olarte <folarte@peoplecall.com>) |
| List | pgsql-general |
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@peoplecall.com> wrote:
( Forgot to hit reply all, so probably someone will get this twice, sorry ).> 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
pgsql-general by date: