12

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)?
13

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 ...

Workaround

A possible, simple workaround would be to encode 1-byte integer values as "char", an "internal" simplistic 1-character type, which actually uses a single byte of storage, byte values of a signed 1-byte integer, the upper half represented as ASCII characters.

You can encode values in the range of -128 to 127. Demo:

SELECT i
     , i::"char"
     , i::"char"::int
FROM   generate_series(-128,127) i;

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 for this.

Note:
My initial suggestions were carelessly based on the assumption that we would cover the range of an unsigned 1-byte integer (0-255) and we could use text as stepping stone. @Evan pointed out the errors of my way: that only works for numbers 1 - 128 and fails for the rest. Instead, use the integer range -128 to 127 and cast between "char" and integer directly to fix both problems.

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.