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: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 10 Jun 2024 15:42:56 -0400
Message-ID: <CANzqJaAXnwh-n0K_JhGiGja2vdqFvRvffdwe2RkVAVKLbmH69g@mail.gmail.com>
Subject: Re: Multiple tables row insertions from single psql input file
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cc449c061a8e5b5c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAXnwh-n0K_JhGiGja2vdqFvRvffdwe2RkVAVKLbmH69g%40mail.gmail.com>
Precedence: bulk

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

On Mon, Jun 10, 2024 at 2:50=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.=
com>
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 tab=
le
> after manually finding the last PK for the company and contact tables. Th=
e
> 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. 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?
>

With enough clever scripting you can create a .sql file that does almost
anything.

Most useful to you will be some number of "ALTER TABLE <foo> DISABLE
TRIGGER ALL;" statements near the beginning of the file, and their "ALTER
TABLE ... ENABLE TRIGGER ALL;" counterparts near the end of the file.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Jun 10, 2024 at 2:50=E2=80=AFPM R=
ich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-e=
cosys.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">My business tracking database has three=
 main tables: company, location,<br>
contact. The company and contact primary keys are sequences.<br>
<br>
I&#39;ve been adding new rows using INSERT INTO files separately for each t=
able<br>
after manually finding the last PK for the company and contact tables. The<=
br>
location table has the company PK as a FK; the contact table has both<br>
company PK and location PK as foreign keys.<br>
<br>
Now I will use next_val &#39;PK&#39; to assign the value for each new table=
 row.<br>
<br>
My question is whether I can 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></blockqu=
ote><div>=C2=A0</div><div>With enough clever scripting you can create a .sq=
l file that does almost anything.</div><div><br></div><div>Most useful to y=
ou will be some number of &quot;<font face=3D"monospace">ALTER TABLE &lt;fo=
o&gt; DISABLE TRIGGER ALL;</font>&quot; statements near the beginning of th=
e file, and their &quot;<font face=3D"monospace">ALTER TABLE ... ENABLE TRI=
GGER ALL;</font>&quot; counterparts near the end of the file.</div><div><br=
></div></div></div>

--000000000000cc449c061a8e5b5c--