Skip to content

petere/pguint

Folders and files

NameName
Last commit message
Last commit date

Latest commit

7e6d6a6 · Dec 5, 2023

History

86 Commits
Dec 4, 2023
Dec 5, 2023
Feb 9, 2015
Dec 5, 2023
Dec 28, 2019
May 26, 2022
May 26, 2022
Feb 6, 2015
Jul 2, 2020
Feb 22, 2015
Jan 30, 2015
Jan 30, 2015
Jan 30, 2015
Dec 5, 2023
Feb 22, 2015
Feb 22, 2015
Feb 9, 2015
Feb 8, 2015
Nov 28, 2013
Jul 2, 2020
May 26, 2022

Repository files navigation

Unsigned and other extra integer types for PostgreSQL

This extension provides additional integer types for PostgreSQL:

  • 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)

Installation

PostgreSQL version 9.1 or later is required. Currently, only 64-bit builds are supported.

To build and install this module:

make
make install

or selecting a specific PostgreSQL installation:

make PG_CONFIG=/some/where/bin/pg_config
make PG_CONFIG=/some/where/bin/pg_config install

And finally inside the database:

CREATE EXTENSION uint;

Using

You can use the new types like the standard integer types. Examples:

CREATE TABLE foo (
    a uint4,
    b text
);

SELECT * FROM foo WHERE a > 4;

SELECT avg(a) FROM foo;

The types come with a sizable set of operators and functions, index support, etc. Some pieces are still missing, but they are being worked on. If there is anything you can't find, let me know.

Discussion

Support for unsigned integer types and smaller integer types has been one of the more common outstanding feature request for PostgreSQL. Inclusion of additional integer types into the core is typically rejected with the argument that it would make the type system too complicated and fragile. The experience from writing this module suggests: That is not wrong. Another argument, either explicit or implicit, is that it is a lot of work. Again: true.

The combination of the requirements of the SQL standard and the type system of PostgreSQL effectively create a situation where you need to provide a comprehensive set of operators and functions for each combination of numeric types. So for the three standard integer types, that's 9 "+" operators, 9 "<" operators, and so on. And with 3 + 5 = 8 types, well, you do the math. This module solves that problem by generating most of the code automatically.

The purpose of this module is therefore twofold: First, it should be useful in practice. There is no reason why it couldn't be. Second, it is a challenge to the PostgreSQL extension mechanism. In that area, there are various "interesting" problems that still need to be worked out.

Testing

In addition to the test suite of this module (make installcheck), it is useful to test this module by running the main PostgreSQL regression tests while this module is loaded, which should not fail. This will verify that the presence of the additional types and operators will not cause changes in the interpretation of expressions involving the existing types and operators.

About

unsigned integer types extension for PostgreSQL

Resources

License

Stars

Watchers

Forks

Packages

No packages published