public inbox for [email protected]
help / color / mirror / Atom feedRe: Multiple tables row insertions from single psql input file
2+ messages / 2 participants
[nested] [flat]
* Re: Multiple tables row insertions from single psql input file
@ 2024-06-10 20:02 Torsten Förtsch <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Torsten Förtsch @ 2024-06-10 20:02 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard <[email protected]>
wrote:
> 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?
>
>
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Multiple tables row insertions from single psql input file
@ 2024-06-10 20:11 Rich Shepard <[email protected]>
parent: Torsten Förtsch <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Rich Shepard @ 2024-06-10 20:11 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-10 20:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-10 20:02 Re: Multiple tables row insertions from single psql input file Torsten Förtsch <[email protected]>
2024-06-10 20:11 ` Rich Shepard <[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