I want to store only 3 states in a column (if it was 2, I would use a boolean)
I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space.
Is there a better way to store only -1 0 and 1 in a PostgresQL column?
If you want to save space, you can use the "char" data type. It stores a single byte.
you can cast integer or text to "char":
SELECT 'u'::"char", 'd'::"char", 'n'::"char";
char | char | char
------+------+------
u | d | n
(1 row)
An enum uses 4 bytes since it is internally stored as a real.
You should not use "char" with non-ASCII characters!
What you are out to save space, you'll have to take alignment into account. The values are always aligned according to the type alignment. For example, a bigint always has to start at an address that is divisible by 8.
Now if your table is defined as
CREATE TABLE (
smallflag "char",
largenum bigint
);
there will be 7 padding bytes between the columns, which would render all the space gains from "char" moot.
So place your table columns carefully.
"char", but it should be noted that it's still tagged as "internal" type in the manual, not in the SQL standard and not portable to other RDBMS.
A bit of a hack, but is you make the column nullable you can still use a bool, with a null value as the third value.
Boolean still uses 1 byte in Postgres, though.
There is a tinyint extension that can store integers from -128 to 127 in a single byte of space, however it currently requires a rewrite to be compatible with postgres version 11 and greater.
https://github.com/umitanuki/tinyint-postgresql
Why this datatype is still unavailable in the main Postgres types has always been a mystery to me.
I'm not really an expert on Postgresql, but checking the docs you could use [var]char(n) as someone suggested. Or if you're comfortable working with bit values you could use [var]bit(n) which looks like the smallest possible. The storage size for [var]bit(n) is basically the number of bits rounded up to the next byte.
For portability: [var]char(n) is (afaik) universally supported, while [var]bit(n) is sometimes called [var]binary(n) instead
