Any way to insert rows with ID used in another column

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

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

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.

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

If you want to be able to rename your original files to the new names using the information returned from your INSERT, you will probably have to add the original name to the table you’re inserting to - I don’t see any way to get to the original name 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) that has the original file name as some sort of placeholder field, without actually inserting the data into the underlying table. 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 or similar. Put that in a trigger and use the original file name in the INSERT statement and the trigger will take care of the rest. There are some caveats there though, such as not making the database wait with processing the remainder of your transaction 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
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/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general