Search Postgresql Archives

Understanding Aliases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello everybody,

 

I did encounter a behaviour of aliases on my postgresql 8.1, which I don’t quite understand.

Consider this schema layout:

 

CREATE TABLE t1

(

  id serial NOT NULL,

  t2_id integer NOT NULL,

  t3_id integer NOT NULL,

  t1_name character varying(255),

  CONSTRAINT t1_pkey PRIMARY KEY (id),

  CONSTRAINT t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE,

  CONSTRAINT t3_id_fkey FOREIGN KEY (t3_id)

      REFERENCES t3 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

CREATE TABLE t2

(

  id serial NOT NULL,

  t2_name character varying(255),

  active boolean NOT NULL,

  CONSTRAINT t2_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t3

(

  id serial NOT NULL,

  t3_name character varying(255),

  CONSTRAINT t3_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t4

(

  id bigserial NOT NULL,

  t2_id integer NOT NULL,

  value integer NOT NULL,

  CONSTRAINT t4_pkey PRIMARY KEY (id),

  CONSTRAINT t4_t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

 

 

 

Now, I try two different queries. One must select all ids of rows in t2, who have t3 or t1 entries with names containing an ‘a’, and exactly three entries in t4 with a value of 10. This works quite fine:

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t1 JOIN t2 ON (t1.t2_id = t2.id) JOIN t3 ON (t3.id = t1.t3_id)

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

            AND (

                                    t3.t3_name ILIKE '%a%'

                        OR       t1.t1_name ILIKE '%a%'

            )

ORDER BY t1.t1_name ASC

 

The second query does not need the “names containing ‘a’” - condition. It only needs to fetch those rows of t2, which have exactly three entries in t4 with a value of 10. Furthermore, no sorting is needed. But, when I try this query

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t2

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

 

I always get an

 

ERROR: column "account_id" does not exist

 

What exactly goes wrong here? I simply do not understand, why the first, more complex query works with the alias, but the second one does not. Did I misunderstand the meaning and usage of such aliases as got_t2_id?

 

Thank you very much in advance.

 

Stanislav Raskin

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux