Re: Multiple tables row insertions from single psql input file

On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
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)