Postgres data types

This page explains how the various data types available in Postgres are handled in Gallium Data

Numbers

• smallint

• integer

• bigint

• decimal/numeric

• real

• double precision

• smallserial

• serial

• bigserial

• money

All these types are exposed to filter logic as JavaScript numbers, except for extremely large or extremely precise numbers (with precision greater than 15 digits), in which case the value will be exposed to the JavaScript logic as a Java BigDecimal object (this can only happen for values of type decimal/numeric).

For values of type decimal/numeric, keep in mind that JavaScript numbers are floating-point numbers, and therefore that some precision may be lost. See the Bind packet for ways to avoid that.

Example

Given two columns named weight and tare of type decimal:

let weight = context.packet.weight;

context.packet.tare = weight / 10;

Characters

• varchar

• char

• bpchar

• text

All these types are exposed as strings.

Example

let country = context.packet.country_name;

if (country.toLowerCase() === 'norway') {

    context.packet.region = 'Scandinavia';

}

Binary

• bytea

Parameters of type bytea are exposed as byte arrays, e.g.:

context.packet.setParameter(2, [1,2,3,4,5,6,7,8,9,0]);

Date/time

• timestamp: 2023-01-29 13:22:45.660045

• timestamp with time zone: 2023-01-29 13:22:45.660045-08

• date: 2023-01-29

• time: 13:22:45.660045

• time with time zone: 13:22:45.660045-08

• interval: 2 years 3 months 4 days

All these types are exposed as strings in the indicated format. It is usually possible to pass a JavaScript Date object for date and timestamp types.

Ranges

• int4range: [100,199)

• int8range: [1000000000,1999999999)

• numrange: [1.5,1.99999)

• tsrange: (2024-01-29 13:44:01.808655, 2024-12-31 23:59:59.999999)

• tstzrange: (2024-01-29 13:44:01.808655-08, 2024-12-31 23:59:59.999999-08)

• daterange: (2024-01-29, 2024-12-31)

All these types are exposed as strings in the indicated format.

Geometric types

• point: (11.0,2.0)

• line: [(11.0,2.0),(33.5,44.2)]

• lseg: [(11.0,2.0),(33.5,44.2),(-9.8,-6.4)]

• box: (4.5,1.0),(2.0,7.33)

• path: [(11.0,2.0),(33.5,44.2),(-9.8,-6.4)]

• polygon: [(11.0,2.0),(33.5,44.2),(-9.8,-6.4)]

• circle: <(3.0,4.0),5.0>

All geometric types are exposed as strings.

Network addresses

• inet: 192.168.1.34

• cidr: 192.168.1.0/24

• macaddr: 0F:02:B3:04:05:06

• macaddr8: 0F:02:B3:04:05:06:07:08

All these types are exposed as strings in the indicated format.

Miscellaneous types

• boolean values are exposed as JavaScript booleans. They can be set to numbers, anything other than zero will be considered true.

• enum types are exposed as strings

• user-defined types are exposed as strings

• bit strings are exposed as arrays of bytes

• tsvector is exposed as a string

• tsquery is exposed as a string

• json, jsonb, jsonpath and xml  are exposed as strings

Arrays

Array types are exposed as JavaScript arrays, and the type of the array members is as described above.