postgresql - 我可以在选择列表中使用 Postgres 关键字作为别名吗？

Question

今天早上我遇到了一个奇怪的问题。我正在创建一个 View 来简化 Postgres 表中的应用程序列表。

这次失败了。

CREATE OR REPLACE VIEW application_view AS 
SELECT COALESCE( nullif(full_name,''), nullif(additional_info,''), app_name) name
, id
FROM application
ORDER BY COALESCE( nullif(full_name,''), nullif(additional_info,''), app_name) 

而

CREATE OR REPLACE VIEW application_view AS 
SELECT COALESCE( nullif(full_name,''), nullif(additional_info,''), app_name) application
, id
FROM application
ORDER BY COALESCE( nullif(full_name,''), nullif(additional_info,''), app_name) 

成功了。

我经常使用 name 作为表中的列名，所以关于为什么第一个 sql 语句失败有什么想法吗？

Answer 1

这是一个 keyword 。当您想在选择列表中使用关键字作为别名时，您必须使用单词 as:

select 1 name;

ERROR:  syntax error at or near "name"
LINE 1: select 1 name;

select 1 as name;

 name 
------
    1
(1 row)

来自the documentation关于选择列表中的别名:

To specify the name to use for an output column, write AS output_name after the column's expression. (You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.)