PostgreSQL – Best Data Type for Storing -1, 0, and 1

database-designdatatypesperformancepostgresql

  • 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?

Best Answer

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.

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.

Related Question