- 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
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
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
So place your table columns carefully.
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.
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