Re: Subqueries - Mailing list pgsql-novice

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

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

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

--