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.

Answer

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