Thread

1. Subqueries

   Narsimham Chelluri <nchelluri@gmail.com> — 2025-03-05T19:01:49Z

   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
   

2. Re: Subqueries

   Bzzzz <lazyvirus@gmx.com> — 2025-03-06T14:05:06Z

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

3. Re: Subqueries

   Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-06T14:23:27Z

   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? 
   > 
   > subquerytest=# select * from something where id in (select id);
   >  id 
   > ----
   > (0 rows)
   > 
   > 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
   > only to values within its own specific from clause and error out otherwise?
   > Maybe I could do that with a CTE?
   
   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