Subject: Re: Multiple tables row insertions from single psql input file

From: "Peter J. Holzer" <[email-masked]>
Date: Mon, 10 Jun 2024 23:27:15 +0200

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

---

From: Rich Shepard <[email-masked]>
Date: Mon, 10 Jun 2024 14:58:42 -0700 (PDT)

> 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).

Peter,

That's what I thought was the case; no way to insert new rows in children
tables when the parent is having new rows at the same time.

So, I started with the parent (companies) table but psql is telling me
there's a syntax error and I don't see it. Testing script:
INSERT into companies (company_nbr,company_name,industry,status) VALUES 
nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opportunity'),
nextval('companies_org_nbr_seq'),'Baker','Livestock','Opportunity');

Running:
$ psql -U rshepard -d bustrac -f test-script.sql 
psql:test-script.sql:3: ERROR:  syntax error at or near "nextval"
LINE 2: nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opp...
         ^
What have I missed?

TIA,

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 10 Jun 2024 15:00:55 -0700

> LINE 2: nextval('companies_org_nbr_seq'),'Acme','Manufacturing','Opp...
Values introduces literal records/rows.  Rows are written within
parentheses.
Values (..., ...), (..., ...)

You seem to have the closing parenthesis but not the opening one.

David J.