Can I use a Postgres keyword as an alias in select list?

Question

I encountered a strange issue this morning. I was creating a view simplifying a list of applications in a Postgres table.

This failed.

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)

whereas

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)

worked.

I often use name as a column name in tables so any ideas as to why the first sql statement failed?

klin · Accepted Answer · 2018-05-10 23:56:55Z

5

It's a keyword. When you want to use a keyword as an alias in the select list you have to use the word as:

select 1 name;

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

select 1 as name;

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

From the documentation about aliases in the select list:

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

edited May 10, 2018 at 23:56

answered May 10, 2018 at 22:09

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Keith John Hutchison Over a year ago

Thanks. I used to think the use of 'as' was optional in all cases.

Collectives™ on Stack Overflow

Can I use a Postgres keyword as an alias in select list?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related