public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: Multiple tables row insertions from single psql input file
Date: Mon, 10 Jun 2024 23:27:15 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On 2024-06-10 11:50:27 -0700, Rich Shepard wrote:
> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
> 
> I've been adding new rows using INSERT INTO files separately for each table
> after manually finding the last PK for the company and contact tables. The
> location table has the company PK as a FK; the contact table has both
> company PK and location PK as foreign keys.
> 
> Now I will use next_val 'PK' to assign the value for each new table row.
> 
> My question is whether I can create new rows for all three tables in the
> same sql source file.

Yes, of course.

> Since the location and contact tables require sequence
> numbers from the company and location tables is there a way to specify,
> e.g., current_val 'tablename PK' for the related tables?

Yes. The function is called currval() and you have to specify the
sequence, not the key, though. For example:

-- set up test tables:
create table company(id serial primary key, name text); 
create table location(id serial primary key, company int references company,  name text);
create table contact(id serial primary key, company int references company, location int references location, name text);

-- add some data
insert into company (name) values('ACME, Inc.');
insert into location(company, name) values(currval('company_id_seq'), 'Phoenix'); 
insert into contact(company, location, name) values(currval('company_id_seq'), currval('location_id_seq'), 'R. Runner');
insert into location(company, name) values(currval('company_id_seq'), 'Los Angeles'); 
insert into contact(company, location, name) values(currval('company_id_seq'), currval('location_id_seq'), 'H. Warner');
insert into company (name) values('Wayne Enterprises');
insert into location(company, name) values(currval('company_id_seq'), 'Gotham City'); 
insert into contact(company, location, name) values(currval('company_id_seq'), currval('location_id_seq'), 'Alfred P.');

This will result in:

test=> select * from company;
╔════╤═══════════════════╗
║ id │       name        ║
╟────┼───────────────────╢
║  1 │ ACME, Inc.        ║
║  2 │ Wayne Enterprises ║
╚════╧═══════════════════╝
(2 rows)

test=> select * from location;
╔════╤═════════╤═════════════╗
║ id │ company │    name     ║
╟────┼─────────┼─────────────╢
║  1 │       1 │ Phoenix     ║
║  2 │       1 │ Los Angeles ║
║  3 │       2 │ Gotham City ║
╚════╧═════════╧═════════════╝
(3 rows)

test=> select * from contact;
╔════╤═════════╤══════════╤═══════════╗
║ id │ company │ location │   name    ║
╟────┼─────────┼──────────┼───────────╢
║  1 │       1 │        1 │ R. Runner ║
║  2 │       1 │        2 │ H. Warner ║
║  3 │       2 │        3 │ Alfred P. ║
╚════╧═════════╧══════════╧═══════════╝
(3 rows)


> Or, do I still need to enter all new companies before their locations
> and contact?

You will of course have to enter each company before its location and
each company and location before its contact. Otherwise you won't have a
value to insert into the foreign key field(s).

There is no need to enter all companies before all locations. Indeed,
currval() can only (as the name implies) return the *current* value of a
sequence, so you can only use it to refer to the last entry you created.
If you create two companies in a row, you've lost the id of the first
one (unless you have somehow saved it - or of course you could get it
back with a select).

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"


Attachments:

  [application/pgp-signature] signature.asc (833B, 2-signature.asc)
  download

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Multiple tables row insertions from single psql input file
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox