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

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.

  • 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. – Erwin Brandstetter Dec 2 at 17:32
  • 6
    Absolutely true. Usually scrimping for storage space and portability don't go together so well. – Laurenz Albe Dec 3 at 6:52
14

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.

1

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.

  • 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. – Erwin Brandstetter Dec 2 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 Dec 2 at 23:00
-3

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

New contributor
Will Menton is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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