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?

Answer 1

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

Answer 2

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.