Re: Multiple tables row insertions from single psql input file

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.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"