Name an output column created with case in PostgreSQL

Question

Is it possible to name the output column created with case ("switch") in a PostgreSQL SELECT statement? It appears from the documentation that this is not possible. An example usage of what I would like to do is:

SELECT CASE (column) WHEN 1 THEN 'One' END AS 'TheColumn' FROM TABLE ;

wildplasser · Accepted Answer · 2012-06-10 14:07:57Z

10

It works for me (pg-9.1)

CREATE TABLE one
    ( one INTEGER
    );
INSERT INTO one(one) values ( 0), (1), (NULL);

SELECT case one 
        when 1 then 'one'
        when 0 then 'zero'
        else 'other' 
       end AS the_one
FROM one;

So, the single quotes (that you used to quote the aliased column name) should have been double quotes (or absent).

The result:

CREATE TABLE
INSERT 0 3
UPDATE 3
 the_one 
---------
 one
 zero
 other
(3 rows)

edited Jun 10, 2012 at 14:07

answered Jun 10, 2012 at 13:57

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

2 Comments

SabreWolfy Over a year ago

Thanks. Your example using either no quotes or double quotes works in PostgreSQL 8.4.12 as well.

wildplasser Over a year ago

It was only the quoting. I tend to avoid MixedCaseIdentifiers, thus avoiding quoting. The rules: single quotes := string literals; double quotes := identifiers.

Collectives™ on Stack Overflow

Name an output column created with case in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related