Sql – PostgreSQL – return string when testing integer with case statement

caseconditionalpostgresqlsql

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?

Best Answer

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
Related Topic