Date: Thu, 6 Mar 2025 15:05:06 +0100
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Subject: Re: Subqueries
Message-ID: <20250306150506.5b06d9df@msi>
In-Reply-To: 
 <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
References: 
 <CAGzvs8dyQh3XDbJiPGzowckmN9o7sA3ysnOC5wE=-mSAKkyL5g@mail.gmail.com>
Organization: Anyone, anywhere but in banana demokratik republik of france
 or uerSS.
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:0J3s22QK+ZM=;qw4+YU7vPCVVGYgkyjzCTnc2RbL
 nGnUOytsiclNu+7muMIj5tubsJSnlu2QXDBffWnsRR7OzXxb8x8duKKnr1AiaOHTwNhwK3iqY
 nNcp19r4CpgcvwPOiDoqwzjD7Gh8DrUvIeQCHUDLv8KY+Np8RQoUNcG5Wl0sYK/Bfx62e10VX
 QQ4M+7iIpsW22bwpeUbp2O8B6/B6WxAXosQSWwEJQaJvrr7zi0BBlxuHyxickFNyjHleN8wvI
 TYtmps88QBCA9UsDQRAuiPVsj30mzVlQqziVFH5FFj1Pmcw2PUFdH7s5PdkdTdMl3g85NTfyh
 8EWQnCpMrlHno4xmKfQgHbM0n2cEKBunTzPTRDP8kjkPdgfRaL4Cz661yU+FNsmjpx7JnAUJV
 bELmNX8j2xK+/fmfTojIFNyUp1XAYa4i1CVWRiPw3y7GIs0Cr14hYYnxT1zuO3Ga1R9Axz0T3
 4Clj6MLFzo3GO+HQwEoyehfZ6JgGrf+AxB9o8j+D15FF7hiY0DaTDROc+H1x7nz4UwbxAXRrT
 nVr+xGETKS/djjdwrQ5hYEneMnPGZdyRFDM/Jp1zNu27whwDA/qdhy0uN64Of4AxOeRMSen8h
 oRz+HNwCwZP/Lxdc/IGoxMAMJFEUHTtooha7N52CiGCidvHn7uDJcxSVZalwJkPYh9e/hgyGn
 2BlhKQxUWVNxwc+t7gBnD/GrMyHXEJJoeoveRYY1rLOVDmPG8gs1IwcHmzQ/rY53yGjawwdgw
 Ec9dsuJO4a0ROfi2t++sVjT+AeLbEYm5/tTgy2l9JWAC9Y4DU0teu05aiy5sY5R1rtlsQ/62B
 FMlGKPJd9xuZpH9Rdx16GBxspruatyyiFMjjAzmP025/bMGXSdLrd4SW7HZJUPzRL329e9TAQ
 bn9e7QZQE/tyPjqp71TEg0+6ZZkDVGjb2GPQS+52wWTaQj7DS9zB6G+0udwbHc2ADneEeCbZD
 RRmcaHr5oHevYE3euB1t4jZ0TomdXM+u07IltpKr2UlhegVyOYOfSHxJZuDyPtOiVq6w08RFt
 Xd1vf6vicMF4PfGG7REvIkTUqqDQTYVyVBCNwbNN4bB98BtrKGA+QLeXP2hN3DNVGp0/+mBTR
 0SbUBW5MTDScCVDXAzwEF3LDTloXxnVms710CGIg/POj+K9RdvC6j9AMgxwHddFDRuNqREoiW
 CH91v68LHItXNfPLo93iyCyRFUVMjUPhS1G2kTnLVYsOUQMGatzk5MUOhPrHr+f9B4KhYxM5u
 cVzRCfPKhc8JRQZGIC5hNQP/uBNpHb8coLQ00CKvlQCRHzjRZJ4L8+v1cIHWOGn4D/Inoi1UT
 hs0MhOqq30i+OUciHVZ0b/+ikp13ev1fJjJVVCzSx5joB8+DuZrzYznyhnnzyxqMZLES7TGbW
 wPNlrkyomjk/ZL7SWOfVbXKeAyCXJhr7bYe9xC2twkBaGb64LYfHK6UHCt2HdlHFe42X0fbpm
 TnNURQ1I2snst9WJlPZ1uajPepi2JUM+Ry+oKf2tkQTRtKvn/
Archived-At: 
 <https://www.postgresql.org/message-id/20250306150506.5b06d9df%40msi>
Precedence: bulk

On Wed, 5 Mar 2025 15:01:49 -0400
Narsimham Chelluri <nchelluri@gmail.com> wrote:

> Hello,
>=20
> Can someone please explain to me why my first query does not return an
> error?
>=20
> --
>=20
> subquerytest=3D# create table something (id bigint generated always as
> identity primary key);
> CREATE TABLE
> subquerytest=3D# create table somethingelse (asdf text);
> CREATE TABLE
> subquerytest=3D# select * from something where id in (select id from
> somethingelse);
>  id
> ----
> (0 rows)
>=20
> subquerytest=3D# select id from somethingelse;
> ERROR:  column "id" does not exist
> LINE 1: select id from somethingelse;
>                ^
> subquerytest=3D# select * from something where id in (select id);
>  id
> ----
> (0 rows)
>=20
> subquerytest=3D# select * from something where id in (select asdf);
> ERROR:  column "asdf" does not exist
> LINE 1: select * from something where id in (select asdf);
>                                                     ^
> subquerytest=3D#
>=20
> --
>=20
> I would imagine it has something to do with: "from something" means
> that "id" is available in the subquery and refers to the column in
> "something" and of course does not refer to "somethingelse" because
> that doesn't have such a column on it. And that I would have to
> disambiguate if it did by using aliases or table names preceding a
> dot.

Hi,

First, you did not said which query you were talking about in the text
just above=E2=80=A6

Second, the absence of error comes from the 'in' statement, as the 2nd
query involved by 'in' doesn't return anything, it doesn't fail the
whole query, but indicates that there is no solution to it, hence the
zero rows answer (you'll find this behavior addressed in the SQL
standard IIRC).

Third, you defined the PK of table 'something' as an automated value,
which will trigger an error when trying to insert into table
'something' directly - you can of course override that, but consequences
could be terrible if you don't know exactly what you are doing and why,
so avoid that at all cost.

> If that is correct: I almost made a mistake in a subquery where I
> used the wrong column in the subquery. Is it possible to make the
> subquery refer only to values within its own specific from clause and
> error out otherwise? Maybe I could do that with a CTE?

What are you talking about ??

You can't assume that YOU want a relationship between both tables
without TELLING the DB engine that it is the case=C2=A0; PostgreSQL is
excellent, but not to the level it reads your mind to build its
table relationships ;-p)

If you want to _tie_ one table column to another located in another
table, you _must_ use a foreign key constraint, which will _enforce_
the link presence, something like :

CREATE TABLE somethingelse (id bigint generated always as
identity primary key);

CREATE TABLE something (id bigint not null REFERENCES
somethingelse(id));

This way, you won't be able to insert into table 'something' if the
value doesn't already exist into table 'somethingelse'.

This is one of the magic of databases, they take care of your mandatory
predicates without a worry.

Another thing is you use 'bigint' in one table and 'text' in the other,
at the very least you should cast the text to a bigint, and as good
practice never use such a hack as it is an open door to difficulties
in the process of exploiting your database (when the text will not
be able to be casted to a bigint, it'll raise an error you will have
to localize and cure, which can easily cascade into inextricable
modifications).

IF (and ONLY IF) you are adamantine that the text value into table
'somethingelse' will _always_ be a 'bigint', then cast it to a
'bigint' _before_ insertion (and of course change the 'adsf' type from
text to bigint).

Jean-Yves

--=20