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

From: Torsten Förtsch <[email-masked]>
Date: Mon, 10 Jun 2024 22:02:26 +0200

On Mon, Jun 10, 2024 at 8:50 PM Rich Shepard <[email-masked]>
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)

---

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

> generate_series(1,10) as i(i);

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