MIME-Version: 1.0
References: <7da8ec9-089-74ce-eb3-c88889b62c64@appl-ecosys.com>
In-Reply-To: <7da8ec9-089-74ce-eb3-c88889b62c64@appl-ecosys.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Mon, 10 Jun 2024 22:02:26 +0200
Message-ID: <CAKkG4_mjjPcWJYhqs4VboSrMJno4t1mtOZLvppy-W3edA=Jzew@mail.gmail.com>
Subject: Re: Multiple tables row insertions from single psql input file
To: Rich Shepard <rshepard@appl-ecosys.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009026e1061a8ea107"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_mjjPcWJYhqs4VboSrMJno4t1mtOZLvppy-W3edA%3DJzew%40mail.gmail.com>
Precedence: bulk

--0000000000009026e1061a8ea107
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jun 10, 2024 at 8:50=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.=
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:

=3D# create table a( id bigserial primary key, x text );
CREATE TABLE
=3D# create table b( fk bigint references a(id), y text );
CREATE TABLE
=3D# 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
=3D# 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)

--0000000000009026e1061a8ea107
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>On Mon, Jun 10, 2024 at 8:50=E2=80=
=AFPM Rich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard=
@appl-ecosys.com</a>&gt; wrote:<br></div></div><div class=3D"gmail_quote"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">My question is whether I c=
an create new rows for all three tables in the<br>
same sql source file. Since the location and contact tables require sequenc=
e<br>
numbers from the company and location tables is there a way to specify,<br>
e.g., current_val &#39;tablename PK&#39; for the related tables? Or, do I s=
till need<br>
to enter all new companies before their locations and contact?<br><br></blo=
ckquote><div>=C2=A0</div><div>Something along these lines perhaps:=C2=A0</d=
iv><div><br></div><span style=3D"font-family:monospace"><span style=3D"colo=
r:rgb(0,0,0)">=3D# create table a( id bigserial primary key, x text );
</span><br>CREATE TABLE
<br><span style=3D"color:rgb(0,0,0)">=3D# create table b( fk bigint referen=
ces a(id), y text ); =C2=A0</span><br>CREATE TABLE
<br><span style=3D"color:rgb(0,0,0)">=3D# with ins_a as (insert into a (x) =
values (&#39;a row&#39;) returning *)</span></span></div><div class=3D"gmai=
l_quote"><span style=3D"font-family:monospace"><span style=3D"color:rgb(0,0=
,0)">=C2=A0 =C2=A0insert into b(fk, y) select <a href=3D"http://ins_a.id">i=
ns_a.id</a>, &#39;yy&#39;||i.i from ins_a cross join generate_series(1,10) =
as i(i);
</span><br>INSERT 0 10
<br><span style=3D"color:rgb(0,0,0)">=3D# table a; table b;
</span><br> id | =C2=A0=C2=A0x =C2=A0=C2=A0=C2=A0<br>----+-------
<br> =C2=A01 | a row
<br>(1 row)
<br>
<br>Time: 0.215 ms
<br> fk | =C2=A0y =C2=A0=C2=A0=C2=A0<br>----+------
<br> =C2=A01 | yy1
<br> =C2=A01 | yy2
<br> =C2=A01 | yy3
<br> =C2=A01 | yy4
<br> =C2=A01 | yy5
<br> =C2=A01 | yy6
<br> =C2=A01 | yy7
<br> =C2=A01 | yy8
<br> =C2=A01 | yy9
<br> =C2=A01 | yy10
<br>(10 rows)<br>
<br></span><div>=C2=A0</div></div></div>

--0000000000009026e1061a8ea107--