4

I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key.

What I'm doing is more complicated than the following but this simple example demonstrates what I want to do.

So let's say I have a temporary table temp_import defined by :

create temp_import(
    row_id int,
    name varchar(32),
    email varchar(255)
)

I have two target tables defined by :

create users (
    userid int not null default nextval('tmp_uid_seq'::regclass),
    username varchar(32)
)

create emails (
    userid int,
    email varchar(255)
)

Currently I have some plpython which inserts a row at a time into users returning userid, followed by a second insert which uses the returned userid to populate emails.

loop:
insert into users (username) values 'Blah' returning userid
insert into emails (userid, email) values (userid, 'Blah2') 
goto loop

This works OK but I want to extract better performance by doing set based operations.

I want to create a temporary mapping table :

create table mapping (
    row_id int,
    userid int
)

and try to do something like :

with rows as (insert into users (username) 
select username from temp_import returning userid, row_id)
insert into mapping (userid, row_id)

The issue here is that I cannot return row_id as I am not selecting it for insert into the target table

If I could do this I could populate the emails table in one go:

insert into emails (userid,email)
select userid,email from temp_import t, mapping m
where t.row_id = m.row_id

Anyone know how I can return the row_id in the with rows as statement above?

Thanks in advance.

0

1 Answer 1

2

One possible solution is adding userid to the staging table you already have:

CREATE TABLE temp_import (
    row_id int,
    name varchar(32),
    email varchar(255),
    userid int not null default nextval('tmp_uid_seq'::regclass)
);

Use the same column definition above as you do in users - this way you use the same sequence, and even the nextval() will be correct after the import.

When doing the COPY, the userid will be filled. This way you have the mapping and the staging all in the same place. Now you have to copy the userid into the final table, too:

INSERT INTO users (userid, name)
SELECT userid, name
  FROM temp_import;

INSERT INTO mapping (userid, row_id)
SELECT userid, row_id
  FROM temp_import;

Notes:

  • in PostgreSQL, unlimited text and varchar have some performance advantage over the limited types. Use the latter if you really have a limit. With varchar(255) the case is usually 'We don't know how long it can be, but definitely not that long'. Email addresses are usually considered as limited to 254 characters, not 255: https://www.rfc-editor.org/errata_search.php?eid=1690 Also, are you sure that nobody can have a name longer than 32 characters, including spaces, punctuation and so on?
  • it usually makes sense to use some consistent naming scheme in any software system, including databases. I'm already confused a bit about the different styles used in userid and row_id.
  • if the import will happen repeatedly or in parallel, just use the same sequence all the time. Sequences are transaction-safe and won't reuse any values until they wrap over (which can be mostly prevented by using bigint/bigserial).
7
  • This won't work for me as i want to be able to run more than one import job at a time, each creating their own 'temp_import table'. After each import has finished, i'll drop the temporary import table. Yeah I should have used rowid instead of row_id ... This is just an example I made up from the top of my head to demonstrate the problem I have
    – David
    Sep 22, 2015 at 14:16
  • @David and why won't it work? If you use the same sequence all the time, everything should go smoothly. Sep 22, 2015 at 14:24
  • I think your solution probably would work but I'm not sure how I feel with attaching a live sequence to tables which are created temporarily and then dropped (probably more of a psychological issue rather than an actual concern). Likewise I'm not that keen on assigning userid's on inserting into the temporary table (again, perhaps that's an ok approach that I need to warm to). Thanks for the reply ... I'll ponder over it
    – David
    Sep 22, 2015 at 16:19
  • @David the output from sequences is not supposed to be contiguous, if you mean that. Sep 22, 2015 at 17:10
  • 1
    @DB140141 thanks for your comment. Did you notice this was a temporary table? Also, production systems routinely adapt to changes, except in the most rigid environments (I haven't yet seen one, but who knows). In PostgreSQL, if done the right way, adding a new column is only a catalog change, blocking concurrent access for a few milliseconds. Oct 7, 2016 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.