public inbox for [email protected]help / color / mirror / Atom feed
Re: Multiple tables row insertions from single psql input file 3+ messages / 3 participants [nested] [flat]
* Re: Multiple tables row insertions from single psql input file @ 2024-06-10 21:27 Peter J. Holzer <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Peter J. Holzer @ 2024-06-10 21:27 UTC (permalink / raw) To: [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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple tables row insertions from single psql input file @ 2024-06-10 21:58 Rich Shepard <[email protected]> parent: Peter J. Holzer <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Rich Shepard @ 2024-06-10 21:58 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Multiple tables row insertions from single psql input file @ 2024-06-10 22:00 David G. Johnston <[email protected]> parent: Rich Shepard <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: David G. Johnston @ 2024-06-10 22:00 UTC (permalink / raw) To: Rich Shepard <[email protected]>; +Cc: [email protected] On Mon, Jun 10, 2024 at 2:58 PM Rich Shepard <[email protected]> wrote: > > 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? > > 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-10 22:00 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-10 21:27 Re: Multiple tables row insertions from single psql input file Peter J. Holzer <[email protected]> 2024-06-10 21:58 ` Rich Shepard <[email protected]> 2024-06-10 22:00 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox