Hackers,

It appears that something broke the ability to refer to columns by full
SQL path names in 9.0. That is, references to columns as
schema.table.col will produce a completely bogus error which did not
exist on previous versions.

The following works perfectly well in 8.4:

postgres=# create table test1( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"
CREATE TABLE
postgres=# create table test2( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for
serial column "test2.id"
CREATE TABLE
postgres=# insert into test1(val) select gs.i from generate_series(1,10)
as gs(i);
INSERT 0 10
postgres=# insert into test2(val) select gs.i from generate_series(1,10)
as gs(i);
INSERT 0 10
postgres=# select test1.* from public.test1, public.test2 where
public.test1.id = public.test2.id;
id | val
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)

postgres=# update public.test1 set val=public.test2.val from
public.test2 where public.test1.id = public.test2.id;
UPDATE 10

However, it breaks in 9.0a5:

postgres=# create table test1( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"
CREATE TABLE
postgres=# create table test2( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for
serial column "test2.id"
CREATE TABLE
postgres=# insert into test1(val) select gs.i from generate_series(1,10)
as gs(i);
INSERT 0 10
postgres=# insert into test2(val) select gs.i from generate_series(1,10)
as gs(i);
INSERT 0 10
postgres=# select test1.* from public.test1, public.test2 where
public.test1.id = public.test2.id;
ERROR: invalid reference to FROM-clause entry for table "test1"
LINE 1: ...ect test1.* from public.test1, public.test2 where public.tes...
^
HINT: There is an entry for table "test1", but it cannot be referenced
from this part of the query.
postgres=# select public.test1.* from public.test1, public.test2 where
public.test1.id = public.test2.id;
ERROR: invalid reference to FROM-clause entry for table "test1"
LINE 1: select public.test1.* from public.test1, public.test2 where ...
^
HINT: There is an entry for table "test1", but it cannot be referenced
from this part of the query.
postgres=#



--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Search Discussions

  • Tom Lane at Apr 28, 2010 at 12:47 am

    Josh Berkus writes:
    It appears that something broke the ability to refer to columns by full
    SQL path names in 9.0. That is, references to columns as
    schema.table.col will produce a completely bogus error which did not
    exist on previous versions.
    My fault :-(. Seems we have no regression tests covering this specific
    point :-( :-(

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 27, '10 at 10:32p
activeApr 28, '10 at 12:47a
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Josh Berkus: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase