Thread: Any way to insert rows with ID used in another column

Any way to insert rows with ID used in another column

From
Ben Hoyt
Date:
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

Re: Any way to insert rows with ID used in another column

From
Adrian Klaver
Date:
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


Re: Any way to insert rows with ID used in another column

From
Francisco Olarte
Date:
( 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.


Re: Any way to insert rows with ID used in another column

From
Alban Hertroys
Date:
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.


Re: Any way to insert rows with ID used in another column

From
Ben Hoyt
Date:
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 ).

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

Re: Any way to insert rows with ID used in another column

From
Adrian Klaver
Date:
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


Re: Any way to insert rows with ID used in another column

From
Alban Hertroys
Date:
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.



Re: Any way to insert rows with ID used in another column

From
Francisco Olarte
Date:
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.


Re: Any way to insert rows with ID used in another column

From
Francisco Olarte
Date:
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.


Re: Any way to insert rows with ID used in another column

From
Adrian Klaver
Date:
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


Re: Any way to insert rows with ID used in another column

From
Francisco Olarte
Date:
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.