Обсуждение: Multiple tables row insertions from single psql input file

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. 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? Or, do I still need
to enter all new companies before their locations and contact?

TIA,

Rich

On Mon, 10 Jun 2024, Ron Johnson wrote:

> With enough clever scripting you can create a .sql file that does almost
> anything.

Ron,

My projects don't all use SQL so I'm far from a clever scripter. :-)

> Most useful to you will be some number of "ALTER TABLE <foo> DISABLE
> TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
> TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.

Doesn't alter table primarily apply to existing row values for specific
columns rather than inserting new rows and their column values?

Thanks,

Rich

On Mon, 10 Jun 2024, Torsten Förtsch wrote:

> Something along these lines perhaps:
>
> =# create table a( id bigserial primary key, x text );
> CREATE TABLE
> =# create table b( fk bigint references a(id), y text );
> CREATE TABLE
> =# with ins_a as (insert into a (x) values ('a row') returning *)
>   insert into b(fk, y) select ins_a.id, 'yy'||i.i from ins_a cross join
> generate_series(1,10) as i(i);
> INSERT 0 10
> =# table a; table b;
> id |   x
> ----+-------
> 1 | a row
> (1 row)
>
> Time: 0.215 ms
> fk |  y
> ----+------
> 1 | yy1
> 1 | yy2
> 1 | yy3
> 1 | yy4
> 1 | yy5
> 1 | yy6
> 1 | yy7
> 1 | yy8
> 1 | yy9
> 1 | yy10
> (10 rows)

Torsten,

You answered my question. The tables are already created and I'll need to
insert new rows table-by-table as I've done before now.

Thanks,

Rich

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!"

On Mon, 10 Jun 2024, Peter J. Holzer wrote:

>> 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?

> 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

On Mon, 10 Jun 2024, David G. Johnston wrote:

> Values introduces literal records/rows. Rows are written within
> parentheses. Values (..., ...), (..., ...)
>
> You seem to have the closing parenthesis but not the opening one.

David,

Duh! Of course. For whatever the reason I kept not seeing that.

Many thanks,

Rich