35

In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value and I want it to be stored in a one-byte data type.

  1. Is there an extension or a way to use one-byte integer data type in PostgreSQL?
  2. How many bytes is NUMERIC(1,0)?
0

2 Answers 2

41

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.

Extension pguint

But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:

In addition to various unsigned integer types, it also provides the 1-byte integer you are looking for:

int1 (signed 8-bit integer)
uint1 (unsigned 8-bit integer)
uint2 (unsigned 16-bit integer)
uint4 (unsigned 32-bit integer)
uint8 (unsigned 64-bit integer)

Be sure to read the chapter "Discussion" at the linked site, explaining possible complications. You need to exercise care with type casts and numeric literals when introducing more integer types ...

Partial Workaround

A possible, simple workaround would be to encode 1-byte integer values as "char" (with double-quotes!), an "internal" simplistic 1-character type, which actually uses a single byte of storage. Up to Postgres 9.5, it covered byte values of a signed 1-byte integer (decimal range of -128 to 127). But it has since been tightened to only cover positive numbers, which are represented as ASCII characters. So only half the range of a single-byte integer.

You can encode values in the range of 0 to 127:

SELECT i
     , i::"char"       -- "encode"
     , i::"char"::int  -- "decode"
FROM   generate_series(0,127) i;

db<>fiddle here

There are several characters not meant for display. So encode before you store and decode before you display.

Remember: "char" is an "internal" type intended for simple and cheap enumeration. Not officially designed for what we are doing here, and not portable to other RDBMS. There are no guarantees by the Postgres project. But since "char" is used all over the system catalogs, the type is not going to change.

You can also cast from text, now that negative numbers are excluded.

4
  • I think char occupies 2 bytes of storage! SELECT pg_column_size(row()); => 24, SELECT pg_column_size(row(1::char)); => 26, The only option is probably the pguint extension; SELECT pg_column_size(row(1::uint1)); => 25 Sep 2, 2020 at 6:43
  • 3
    @Mohammad "char" <> char! Try SELECT pg_column_size('1'::"char"); See dba.stackexchange.com/a/125526/3684 or stackoverflow.com/a/20334221/939860 Sep 2, 2020 at 9:11
  • 1
    Under PG 14, negative values in the demo you wrote throw an encoding exception.
    – IamIC
    Nov 24, 2021 at 14:44
  • 1
    @IamIC: Thanks for pointing out. I adapted accordingly. Nov 24, 2021 at 18:32
0

You can try to store it as bit(8). https://www.postgresql.org/docs/12/datatype-bit.html

As mentioned in the comment below, using bits require 6 bytes acually.

2
  • 6
    "A bit string value requires 1 byte for each group of 8 bits, plus 5 or 8 bytes" - so a bit(8) needs at least 6 bytes. Actually. pg_column_size(1::bit(8)) shows that it needs 8 bytes
    – user1822
    Aug 20, 2020 at 20:51
  • @a_horse_with_no_name oh, my bad, thank you for correction! Aug 21, 2020 at 9:48

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.