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