Columns with keywords as names in PostgreSQL

问题:

I have a couple of tables which have keywords as column names. I am not able to write a subquery using them. Even if I use non-existing column names, the query works. For example

CREATE TABLE tgt("view" int);

CREATE TABLE src(id  int);

select * from tgt where view in (select view from src);
 view 
------
(0 rows)

select * from tgt where view in (select "view" from src);
 view 
------
(0 rows)

select * from tgt where "view" in (select "view" from src);
 view 
------
(0 rows)

select "view" from src;
ERROR:  column "view" does not exist
LINE 1: select "view" from src;

insert into tgt values(1);

insert into src  values(2);

select * from tgt where "view" in (select "view" from src);
 view 
------
    1
(1 row)

select * from tgt where view in (select id  from src);
 view 
------
(0 rows)

I have a few questions - when I use a non-existing column name in a subquery, it works. But if I use the same column name in a query, I get an error. How can I use key-word column names in a subquery without running into surprises like this? I know that using keywords as column names is not a good idea, but the system has been around for 2 decades and changing column names is not an option now.

回答1:

when I use a non-existing column name in a subquery, it works.

Well, but not the way you think it works.

It will resolve to the column in some other part of the query (here the other table).

select * from tgt where view in (select view from src);

is the same as

select * from tgt where view in (select tgt.view from src);

This is not related to "view" being a bad choice for a column name, the same thing happens if your column is called "x".

回答2:

Another alternative is to use an inner join instead of the subquery. The ambiguity in column names is more obvious here, and you can use aliasing to remove the ambiguity, e.g.:

select DISTINCT t.* 
from tgt t
inner join src s
   on t.view = s.view;