Обсуждение: Any way to insert rows with ID used in another column
Hi folks,
We have a table images in our db with id (serial primary key) and filename columns, where the filename is a unique text column that looks something like "pool-1234.jpg".
The catch is that the "1234" in the filename is the image ID. We want the filename to include the image ID because it's a nice way of making it unique and gives the benefit of being able to easily map from the filename back to the ID for debugging and the like.
Currently I insert new image rows in multiple steps:
1) begin transaction
2) insert a whole bunch of image rows in one multiple-row INSERT, using a temporary random filename
3) use the RETURNING clause on the above insert to get a mapping between the database IDs and filenames just inserted
4) loop through all images just inserted, and for each image, execute UPDATE to set the filename to the real filename which includes the new image ID
5) commit
This works, but it's pretty cumbersome, and requires N UPDATE statements which is also slow.
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.
Thanks,
Ben
On 04/03/2014 07:41 AM, Ben Hoyt wrote:
> Hi folks,
>
> We have a table images in our db with id (serial primary key) and
> filename columns, where the filename is a unique text column that looks
> something like "pool-1234.jpg".
>
> The catch is that the "1234" in the filename is the image ID. We want
> the filename to include the image ID because it's a nice way of making
> it unique and gives the benefit of being able to easily map from the
> filename back to the ID for debugging and the like.
>
> Currently I insert new image rows in multiple steps:
>
> 1) begin transaction
> 2) insert a whole bunch of image rows in one multiple-row INSERT, using
> a temporary random filename
> 3) use the RETURNING clause on the above insert to get a mapping between
> the database IDs and filenames just inserted
> 4) loop through all images just inserted, and for each image, execute
> UPDATE to set the filename to the real filename which includes the new
> image ID
> 5) commit
>
> This works, but it's pretty cumbersome, and requires N UPDATE statements
> which is also slow.
>
> 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.
Write a BEFORE INSERT trigger function?
>
> Thanks,
> Ben
>
--
Adrian Klaver
adrian.klaver@aklaver.com
( 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.
On 3 April 2014 16:41, Ben Hoyt <benhoyt@gmail.com> wrote: > Hi folks, > 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. currval() requires that nextval() was called before it (either automatically or explicitly) in the same transaction. Usually what you want is achieved using nextval(). You request n new ID's using nextval(), which you can then use to both name your n image files and for the ID with which you will be inserting them into your table. Unfortunately there doesn't appear to be a variant of nextval() that you pass a number which then subsequently returns a set of values, that would be ideal for such usage, but that can be worked around by calling nextval() in conjunction with generate_series(). -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
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
On 04/05/2014 05:14 PM, Ben Hoyt wrote:
> 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.
Still think this is something for a BEFORE INSERT TRIGGER:
test=> \d seq_test
Table "public.seq_test"
Column | Type | Modifiers
--------+-------------------+-------------------------------------------------------
id | integer | not null default
nextval('seq_test_id_seq'::regclass)
fld | character varying |
Triggers:
test_id BEFORE INSERT ON seq_test FOR EACH ROW EXECUTE PROCEDURE
id_test()
CREATE OR REPLACE FUNCTION public.id_test()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
NEW.fld := NEW.id::text || '_' || NEW.fld;
RETURN NEW;
END;
$function$
insert into seq_test(fld) values ('my_file.jpg');
insert into seq_test(fld) values ('another_file.jpg');
test=> select * from seq_test;
id | fld
----+--------------------
1 | 1_my_file.jpg
2 | 2_another_file.jpg
(2 rows)
>
> -Ben
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 06 Apr 2014, at 2:14, Ben Hoyt <benhoyt@gmail.com> wrote:
> 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
aredistinct 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
If you want to be able to rename your original files to the new names using the information returned from your INSERT,
youwill probably have to add the original name to the table you’re inserting to - I don’t see any way to get to the
originalname otherwise. You’d probably have to go procedural for that, either in your application or (what Adrian
suggests)in a trigger function.
You might be able to fake it by inserting that data into a view (with an insert rule, or you can’t insert into one)
thathas the original file name as some sort of placeholder field, without actually inserting the data into the
underlyingtable. That would be a rather misleading view to people attempting to query from it later though. It’s not
“proper”.
You could even go fancy and have the file renaming be done by an (untrusted) PL language, such as plpythonu or plperlu
orsimilar. Put that in a trigger and use the original file name in the INSERT statement and the trigger will take care
ofthe rest. There are some caveats there though, such as not making the database wait with processing the remainder of
yourtransaction until the file rename operation is completed each time.
As a final note, please don’t top-post on this list.
> 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 ).
>
> 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
>
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Hi:
On Sun, Apr 6, 2014 at 2:14 AM, Ben Hoyt <benhoyt@gmail.com> wrote:
> Thanks for the info, Francisco and Alban -- that looks useful.
May be.
> 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.
Relatively simple, replace the inner select ( the one using generate
series in the demo ) with a values statement and munge the outer
select wich combines it appropiately:
postgres=# insert into files (select id, dir || '/image_' || cast(id
as text) || '.' || ext as file from (values
(nextval('files_id_seq'::regclass), 'somedir','jpeg') ,
(nextval('files_id_seq'::regclass), 'someotherdir','gif')) as
source(id,dir,ext)) returning *;
id | file
----+---------------------------
12 | somedir/image_12.jpeg
13 | someotherdir/image_13.gif
(2 rows)
Munge as needed. Doing it with some WITHs makes for some more readable query:
postgres=# WITH
postgres-# source(id,dir,ext) as (
postgres(# VALUES (nextval('files_id_seq'::regclass), 'somedir',
'jpeg')
postgres(# , (nextval('files_id_seq'::regclass), 'someotherdir','gif')
postgres(# ),
postgres-# rows(id,file) as (
postgres(# SELECT id
postgres(# , dir || '/image_' || cast(id as text) || '.' || ext
postgres(# FROM source
postgres(# )
postgres-# INSERT INTO files (TABLE rows) RETURNING *;
id | file
----+---------------------------
20 | somedir/image_20.jpeg
21 | someotherdir/image_21.gif
(2 rows)
INSERT 0 2
Regards.
Francisco Olarte.
Hi Adrian: On Sun, Apr 6, 2014 at 2:30 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > Still think this is something for a BEFORE INSERT TRIGGER: I think a trigger is overkill for just a simple data-combining procedure. JMO, but I prefere to reserve triggers for htings which need them. Regards. Francisco Olarte.
On 04/06/2014 05:30 AM, Francisco Olarte wrote: > Hi Adrian: > > > On Sun, Apr 6, 2014 at 2:30 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> Still think this is something for a BEFORE INSERT TRIGGER: > > I think a trigger is overkill for just a simple data-combining > procedure. JMO, but I prefere to reserve triggers for htings which > need them. Well the flip side to that argument is that a trigger is a single point of reference for the data changing. You can hit the table from wherever and whatever and have the same thing happen. No wrestling with ORMs to get database specific code to run. No tracking down where the query is that is munging the data. Not saying one approach is inherently better than the other, just that there are options. > > Regards. > Francisco Olarte. > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian: On Sun, Apr 6, 2014 at 5:05 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 04/06/2014 05:30 AM, Francisco Olarte wrote: >> I think a trigger is overkill for just a simple data-combining >> procedure. JMO, but I prefere to reserve triggers for htings which >> need them. > Well the flip side to that argument is that a trigger is a single point of > reference for the data changing. You can hit the table from wherever and > whatever and have the same thing happen. No wrestling with ORMs to get > database specific code to run. No tracking down where the query is that is > munging the data. Not saying one approach is inherently better than the > other, just that there are options. You raise some notable points. Specially in the ORM wrestling part, which I never thought of as I hate them. I'm still partial to some normal logic, perhaps hidden in a procedure which may be invoked on an instead trigger on inserts of a dedicated view ( data changing insert triggers scare me, I like to be able to select what I've just inserted, so I would prefer to insert into a view with only the ids and extra part and recover the full files from another table / view ). Although I still think that given his original statement the best would be to select some values from the sequence, rename the files and insert them after, or what I did for something similar once, insert the rows with the original names and then rename the files after insert, and, after a crash, find leftover unrenamed files, rescan table for them and rename. Francisco Olarte.