citusdata/pg_intpair

Name: pg_intpair

Owner: Citus Data

Description: null

Created: 2017-11-20 18:57:45.0

Updated: 2018-04-28 00:05:36.0

Pushed: 2018-03-06 08:24:49.0

Homepage: null

Size: 12

Language: C

GitHub Committers

User	Most Recent Commit	# Commits

Other Committers

User	Email	Most Recent Commit	# Commits

README

pg_intpair

This PostgreSQL extensions provides a single datatype named int64pair. It is simply a pair of 64-bit integers packed. In the absence of this extension, one could use PostgreSQL's composite types or arrays for the same purpose. The advantages of using int64pair are more performance and better storage density.

Usage

• To create an instance, you can use the int64pair(a, b) function. For example, int64pair(-1, 1) will create a pair with first component equal to -1 and second component equal to 1.
• To access the first component you can use p[0], and to access the second component you can use p[1].
• You can create b-tree or hash indexes on int64pair columns.

Migrating from a matching composite type

Suppose you already have a composite type with the following definition:

TE TYPE my_composite_type AS (first BIGINT, second BIGINT);

You can use the following `ALTER TABLEcommand to convert a column with the composite type to ``int64pair```:

R TABLE t ALTER COLUMN col TYPE int64pair USING a::text::int64pair;

You can enable implicit casting by defining following casts:

TE CAST (my_composite_type AS int64pair) WITH INOUT AS IMPLICIT;
TE CAST (int64pair AS my_composite_type) WITH INOUT AS IMPLICIT;

Then, you can do the previous `ALTER TABLEcolumn without the ``USING``` clause:

R TABLE t ALTER COLUMN col TYPE int64pair;

To be able to use the same `(col).firstand ``(col).second` syntax forint64pair`` columns, you can create couple of simple functions:

TE FUNCTION first(p int64pair) RETURNS BIGINT
AS 'select p[0];'
LANGUAGE SQL IMMUTABLE
RETURNS NULL ON NULL INPUT;

TE FUNCTION second(p int64pair) RETURNS BIGINT
AS 'select p[1];'
LANGUAGE SQL IMMUTABLE
RETURNS NULL ON NULL INPUT;