在PostgreSQL中以关键字作为名称的列 [英] 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)

我有几个问题-当我在子查询中使用不存在的列名时，它会起作用.但是，如果我在查询中使用相同的列名，则会收到错误消息.如何在子查询中使用关键字列名称，而不会遇到类似的意外情况?我知道使用关键字作为列名不是一个好主意，但是该系统已经存在了20年了，现在不能更改列名了.

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.

推荐答案

当我在子查询中使用不存在的列名时，它会起作用.

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

与

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

这与视图"不是列名的错误选择有关，如果您的列名为"x"，也会发生同样的事情.

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

这篇关于在PostgreSQL中以关键字作为名称的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！