Date: Mon, 10 Jun 2024 23:27:15 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Multiple tables row insertions from single psql input file
Message-ID: <20240610212715.aqpipehoa5xxmrmf@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <7da8ec9-089-74ce-eb3-c88889b62c64@appl-ecosys.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="iidkrxmh4gmjigeq"
Content-Disposition: inline
In-Reply-To: <7da8ec9-089-74ce-eb3-c88889b62c64@appl-ecosys.com>
Archived-At: <https://www.postgresql.org/message-id/20240610212715.aqpipehoa5xxmrmf%40hjp.at>
Precedence: bulk


--iidkrxmh4gmjigeq
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-06-10 11:50:27 -0700, Rich Shepard wrote:
> My business tracking database has three main tables: company, location,
> contact. The company and contact primary keys are sequences.
>=20
> 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. The
> location table has the company PK as a FK; the contact table has both
> company PK and location PK as foreign keys.
>=20
> Now I will use next_val 'PK' to assign the value for each new table row.
>=20
> My question is whether I can create new rows for all three tables in the
> same sql source file.

Yes, of course.

> 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?

Yes. The function is called currval() and you have to specify the
sequence, not the key, though. For example:

-- set up test tables:
create table company(id serial primary key, name text);=20
create table location(id serial primary key, company int references company=
,  name text);
create table contact(id serial primary key, company int references company,=
 location int references location, name text);

-- add some data
insert into company (name) values('ACME, Inc.');
insert into location(company, name) values(currval('company_id_seq'), 'Phoe=
nix');=20
insert into contact(company, location, name) values(currval('company_id_seq=
'), currval('location_id_seq'), 'R. Runner');
insert into location(company, name) values(currval('company_id_seq'), 'Los =
Angeles');=20
insert into contact(company, location, name) values(currval('company_id_seq=
'), currval('location_id_seq'), 'H. Warner');
insert into company (name) values('Wayne Enterprises');
insert into location(company, name) values(currval('company_id_seq'), 'Goth=
am City');=20
insert into contact(company, location, name) values(currval('company_id_seq=
'), currval('location_id_seq'), 'Alfred P.');

This will result in:

test=3D> select * from company;
=E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=97
=E2=95=91 id =E2=94=82       name        =E2=95=91
=E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=95=A2
=E2=95=91  1 =E2=94=82 ACME, Inc.        =E2=95=91
=E2=95=91  2 =E2=94=82 Wayne Enterprises =E2=95=91
=E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=9D
(2 rows)

test=3D> select * from location;
=E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97
=E2=95=91 id =E2=94=82 company =E2=94=82    name     =E2=95=91
=E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2
=E2=95=91  1 =E2=94=82       1 =E2=94=82 Phoenix     =E2=95=91
=E2=95=91  2 =E2=94=82       1 =E2=94=82 Los Angeles =E2=95=91
=E2=95=91  3 =E2=94=82       2 =E2=94=82 Gotham City =E2=95=91
=E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D
(3 rows)

test=3D> select * from contact;
=E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=A4=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97
=E2=95=91 id =E2=94=82 company =E2=94=82 location =E2=94=82   name    =E2=
=95=91
=E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2
=E2=95=91  1 =E2=94=82       1 =E2=94=82        1 =E2=94=82 R. Runner =E2=
=95=91
=E2=95=91  2 =E2=94=82       1 =E2=94=82        2 =E2=94=82 H. Warner =E2=
=95=91
=E2=95=91  3 =E2=94=82       2 =E2=94=82        3 =E2=94=82 Alfred P. =E2=
=95=91
=E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=A7=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D
(3 rows)


> Or, do I still need to enter all new companies before their locations
> and contact?

You will of course have to enter each company before its location and
each company and location before its contact. Otherwise you won't have a
value to insert into the foreign key field(s).

There is no need to enter all companies before all locations. Indeed,
currval() can only (as the name implies) return the *current* value of a
sequence, so you can only use it to refer to the last entry you created.
If you create two companies in a row, you've lost the id of the first
one (unless you have somehow saved it - or of course you could get it
back with a select).

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--iidkrxmh4gmjigeq
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmZnb64ACgkQ8g5IURL+
KF3y/hAAnbSpY25U9XNnhvG7YBOgEa1KQr9QZRBUE5CbS+SJmACal3KiVCn8XH+a
84flGD0doXR6m2qut/EPxFjZAIkAHiPCGSF0OHh1GdeiYXLOOXqqFznjlZTIeOTC
PzPAPpIMP4JeGPmFX6l/Cv8Rlt/GWlte8WNxlUNY6+VzZnIulJssTP25GOZO1PyC
tNrPU2vtDfy8Iyem25g6iRWmayzT/vdsLafzZhRHa3xGlEL5HfRO8U1yd+y3Cc2l
1Cx3z0Me4ZzluvCrq8TGL9+3wEsUBnhABoEsR/MESzYwA1QbjMl45trVhjftOsu/
ZtU6ceLTZZh0Wme8cSLpiLO2dM4NAMKyi7451Hexo/fNSNOjaQdiPZ5L8P+SYbma
3oPUURqt1bCMc0EipWWvqUgNqoe4M8v6xWWVA8GPxpfrSc4iDPBCZ2W9ydLMncPm
wEGwc9vfRqbJfV3rGdEN+rZkJoB94+uhfneLWD/u8TfIw2IUv5ykDAYRCE7rPNTz
vuzltqJ23fEg7SHLccEtPxZAFcmd+loqhKzZi1p4ODxS/jtLIuRGosArARXyoUR6
rnYg4iY4NnDYKYHjW9ySc+r/HL0a0A1Xq7yTlEJ9TCsMQ+qKLS42dNiyG48fgaR7
zXoZW7xD0LasCOfStf/9NwO5ahcSuotA+GoTMS+jJjkdR/EtfHs=
=LLbn
-----END PGP SIGNATURE-----

--iidkrxmh4gmjigeq--