Subject: Subqueries

From: Narsimham Chelluri <[email-masked]>
Date: Wed, 5 Mar 2025 15:01:49 -0400

Hello,

Can someone please explain to me why my first query does not return an
error?

--

subquerytest=# create table something (id bigint generated always as
identity primary key);
CREATE TABLE
subquerytest=# create table somethingelse (asdf text);
CREATE TABLE
subquerytest=# select * from something where id in (select id from
somethingelse);
 id
----
(0 rows)

subquerytest=# select id from somethingelse;
ERROR:  column "id" does not exist
LINE 1: select id from somethingelse;
               ^
subquerytest=# select * from something where id in (select id);
 id
----
(0 rows)

subquerytest=# 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=#

--

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.

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?

Thanks.

- Narsa

---

From: Bzzzz <[email-masked]>
Date: Thu, 6 Mar 2025 15:05:06 +0100

On Wed, 5 Mar 2025 15:01:49 -0400
Narsimham Chelluri <[email-masked]> wrote:

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

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

--

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 06 Mar 2025 15:23:27 +0100

On Wed, 2025-03-05 at 15:01 -0400, Narsimham Chelluri wrote:
> Can someone please explain to me why my first query does not return an error? 
> 
> 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.

Right.

> And that I would have to disambiguate if it did by using
> aliases or table names preceding a dot.

Right again.

> 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

I follow the following rule:
Whenever an SQL statement refers to more than one table, qualify all column
references with the table alias:

  select * from something where something.id in (select somethingelse.id);

Yours,
Laurenz Albe