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?


4 Answers 4


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

   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.

  • 5
    I love "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. Commented Dec 2, 2019 at 17:32
  • 7
    Absolutely true. Usually scrimping for storage space and portability don't go together so well. Commented Dec 3, 2019 at 6:52

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.

  • 2
    The big problem with using null as a third value is that the join semantics for null are different than for non-null values. Commented Dec 17, 2019 at 16:04

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.

  • 4
    I'd rather consider pguint created by Peter Eisentraut (Postgres core developer). The "Discussion" in the Readme section provides some rationale, why you may not want to install additional integer types. Commented Dec 2, 2019 at 17:28
  • @ErwinBrandstetter The description in that link is fascinating. It never occured to me that there'd be no concept of extension/inheritance for similar types. Thanks very much for sharing.
    – Scoots
    Commented Dec 2, 2019 at 23:00

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.