PostgreSQL - return string when testing integer with case statement

Question

When selecting some data from a table I'd like to use a CASE statement to return "n/a" in the event of a null value.

This is a simplified but representative version of the table I'm looking at:

CREATE TABLE test (
    id INTEGER NOT NULL,
    paid_cents INTEGER NULL DEFAULT NULL,
    PRIMARY KEY (id)
);

INSERT INTO test VALUES (1, 100);
INSERT INTO test VALUES (2, NULL);
INSERT INTO test VALUES (3, 0);

I'd expect to be able to use the following query:

SELECT CASE
    WHEN paid_cents IS NULL THEN 'n/a'
    ELSE paid_cents / 100
    END AS "dollar amount"
FROM test

A fiddle of this behaves exactly as I'd expect. However trying this on my real database (PostgreSQL 9.4) results in an error:

ERROR: invalid input syntax for integer: "n/a"
LINE 2: WHEN paid_cents IS NULL THEN 'n/a'

It seems that Postgres is expecting an integer to be returned when testing an integer (and changing 'n/a' to a number does indeed work). I'm guessing that I'll need to CAST somewhere?

postgres treats NULL as unknown - maybe WHEN paid_cents IS NULL THEN NULL will serve?.. because you can't mix datatyopes in column - it is ether int or char — Vao Tsun
– Vao Tsun, Commented Aug 14, 2017 at 14:47

Vao Tsun · Accepted Answer · 2017-08-14 14:48:11Z

8

either use NULL:

SELECT CASE
    WHEN paid_cents IS NULL THEN NULL
    ELSE paid_cents / 100
    END AS "dollar amount"
FROM test

or cast both to string:

SELECT CASE
    WHEN paid_cents IS NULL THEN 'n/a'
    ELSE (paid_cents / 100)::text
    END AS "dollar amount"
FROM test

answered Aug 14, 2017 at 14:48

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

Hex Over a year ago

Casting both to string is what I needed. Thanks very much.

Vao Tsun Over a year ago

sure, glad to help.

user1694674 · Accepted Answer · 2017-08-14 14:57:50Z

1

You cannot mix datatypes in the columns. As previous answer suggest you should go to a textual datatype (char/varchar/text etc...)

I would use ANSI-SQL and cast( expression as datatype) instead of :: datatype. Same functionality but would be portable between most database engines.

In this case it would be cast( (paid_cents/100) as text) instead of (paid_cents/100) :: text

Cheers.

answered Aug 14, 2017 at 14:57

user1694674

1265 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - return string when testing integer with case statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related