codercms/postgres-uint128 repository overview

⁠pg-uint128

A PostgreSQL extension that adds native support for unsigned integers and 128-bit signed/unsigned integer types.

⁠Supported PostgreSQL Versions

PostgreSQL 12 - 17

⁠Features

• Provides multiple unsigned types:
  • uint1 (uint8)
  • uint2 (uint16)
  • uint4 (uint32)
  • uint8 (uint64)
  • uint16 (uint128)
• Provides multiple signed integer types:
  • int1 (int8)
  • int16 (int128)
• Provides corresponding range (and multi range) types with GiST indexing support:
  • uint1range, uint1multirange
  • uint2range, uint2multirange
  • uint4range, uint4multirange
  • uint8range, uint8multirange
  • uint16range, uint16multirange
  • int1range, int1multirange
  • int16range, int16multirange
• Binary send/recv support
• Casts to uint16, uint8, uint4, uint2, uint1, int16, int8, int4, int2, int1, numeric, real, double, uuid (for uint16 only)
• Mixed-type arithmetic support (signed ↔ unsigned)
• Indexing support for BTREE and HASH indexes
• Rich operators set:
  • + - ADD (Addition)
  • - - SUB (Subtract)
  • * - MUL (Multiply)
  • / - DIV (Divide)
  • % - MOD (Modulo)
  • # - Bitwise XOR
  • & - Bitwise AND
  • | - Bitwise OR
  • ~ - Bitwise NOT
  • << - Bitwise SHL (shift left)
  • >> - Bitwise SHR (shift right)
• Comprehensive set of comparison operators:
  • = - EQ (Equal)
  • <> (!=) - NE (Not Equal)
  • > - GT (Greater Than)
  • < - LT (Less Than)
  • >= - GE (Greater Than or Equal)
  • <= - LE (Less Than or Equal)
• Aggregation support:
  • SUM
  • AVG
  • MIN
  • MAX
  • Note on SUM/AVG Aggregation: These aggregations are performed using PostgreSQL's numeric type to minimize the risk of overflow.
    While this approach ensures precision, it may not be the most efficient in terms of performance, as it relies on PostgreSQL's numeric type calculations rather than CPU-native operations.
    The maximum precision of the aggregates is limited by PostgreSQL's numeric type precision.
• generate_series support for each type (SELECT * FROM generate_series(1::uint4, 10::uint4);)

⁠Mixed type arithmetic

Mixed-type arithmetic between signed and unsigned integer types is complex due to the different representations used for each.

Signed integer ranges:

Int8 (int1)    — [-128 : 127]
Int16 (int2)   — [-32768 : 32767]
Int32 (int4)   — [-2147483648 : 2147483647]
Int64 (int8)   — [-9223372036854775808 : 9223372036854775807]
Int128 (int16) — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

Unsigned integer ranges:

UInt8 (uint1)    — [0 : 255]
UInt16 (uint2)   — [0 : 65535]
UInt32 (uint4)   — [0 : 4294967295]
UInt64 (uint8)   — [0 : 18446744073709551615]
UInt128 (uint16) — [0 : 340282366920938463463374607431768211455]

From a binary perspective, UINT8_MAX (255) becomes -1 for a signed 8-bit integer, UINT 254 becomes -2, and so on.

This extension addresses potential overflow and underflow during arithmetic operations between signed and unsigned types.

⁠Arithmetic

• Addition
  • Signed int dominant - SELECT 9223372036854775807::int8 + 9223372036854775807::uint8; → int8 out of range
  • Unsigned int dominant - SELECT 9223372036854775807::uint8 + 9223372036854775807::int8; → 18446744073709551614
  • Signed int dominant - SELECT (-120)::int4 + 10::uint8; → -110
  • Unsigned int dominant - SELECT 10::uint8 + (-120)::int4; → uint8 out of range, because unsigned integer cannot represent negative values
  • Unsigned int dominant - SELECT 10::uint8 + (-10)::int4; → 0
• Subtraction
  • Signed int dominant - SELECT (-120)::int4 - 10::uint8; → -130
  • Unsigned int dominant - SELECT 10::uint8 - (-120)::int4; → 130
  • Unsigned int dominant - SELECT 10::uint8 - (120)::int4; → uint8 out of range, because unsigned integer cannot represent negative values
• Multiplication
  • Signed int dominant - SELECT (-120)::int4 * 10::uint8; → -1200
  • Unsigned int dominant - SELECT 10::uint8 * (-120)::int4; → unsigned int multiply by negative signed int is probhibited, because unsigned integer cannot represent negative values
  • Unsigned int dominant - SELECT 120::uint8 * 10::int4; → 1200
• Division
  • Signed int dominant - SELECT (-120)::int4 / 10::uint8; → 0 (because negative int is always less than uint)
  • Unsigned int dominant - SELECT 10::uint8 / (-120)::int4; → unsigned int division/modulo by negative signed int is probhibited, because unsigned integer cannot represent negative values
  • Unsigned int dominant - SELECT 120::uint8 / 10::int4; → 12
• Modulo
  • Signed int dominant - SELECT (-3)::int4 % 2::uint8; → -3 (because negative int is always less than int)
  • Unsigned int dominant - SELECT 3::uint8 % (-2)::int4; → unsigned int division/modulo by negative signed int is probhibited, because unsigned integer cannot represent negative values
  • Unsigned int dominant - SELECT 3::uint8 % 2::int4; → 1

⁠Docker

You can easily try out the pg-uint128 extension using a pre-built Docker image with PostgreSQL and the extension preinstalled.

The Docker image is based on the official PostgreSQL image, so you can use all the options and configurations supported by the official image.

Follow these steps to get started:

1. Run the PostgreSQL container with pg-uint128:

   docker run --name pg-uint128 -d -p 15432:5432 -e POSTGRES_PASSWORD=secret codercms/postgres-uint128:15-1.0.0
   

   Copy

2. Connect to PostgreSQL:

   • Option 1: Use psql from your local machine:

     psql -h 127.0.0.1 -p 15432 -U postgres
     

     Copy

   • Option 2: Use psql from inside the Docker container

     docker exec -it pg-uint128 psql -U postgres
     

     Copy

Now you can explore and experiment with the pg-uint128 extension within a PostgreSQL environment.