15
  • 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?
Improve this question
1

4 Answers 4

Reset to default
27

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.

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

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.

Improve this answer
1
  • 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.
    – Robert Stiffler
    Dec 17, 2019 at 16:04
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.

Improve this answer
2
  • 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, 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
    Dec 2, 2019 at 23:00
-4

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

Improve this answer

Your Answer

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

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