Data Types

Halo database supports the full range of PostgreSQL data types in PostgreSQL compatibility mode.

Numeric Types

Type	Storage Size	Range
SMALLINT	2 bytes	-32768 to +32767
INTEGER / INT	4 bytes	-2147483648 to +2147483647
BIGINT	8 bytes	-9223372036854775808 to +9223372036854775807
REAL	4 bytes	6 decimal digits of precision
DOUBLE PRECISION	8 bytes	15 decimal digits of precision
NUMERIC(p,s) / DECIMAL(p,s)	Variable	Up to 131072 digits before the decimal point, up to 16383 digits after
SERIAL	4 bytes	Auto-incrementing integer (1 to 2147483647)

-- Integer types
CREATE TABLE int_test (
    id SERIAL PRIMARY KEY,
    small_num SMALLINT,
    num INTEGER,
    big_num BIGINT
);
INSERT INTO int_test (small_num, num, big_num) VALUES (-32768, 2147483647, 9223372036854775807);
SELECT * FROM int_test;

-- High-precision decimals
CREATE TABLE numeric_test (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2),
    ratio DECIMAL(30,20)
);
INSERT INTO numeric_test (price, ratio) VALUES (12345.67, 123456.78901234567890123456);
SELECT * FROM numeric_test;

Character Types

Type	Description	Maximum Length
CHAR(n) / CHARACTER(n)	Fixed-length string	10485760
VARCHAR(n) / CHARACTER VARYING(n)	Variable-length string	10485760
TEXT	Variable-length string	1 GB

CREATE TABLE char_test (
    id SERIAL PRIMARY KEY,
    fixed_char CHAR(10),
    variable_char VARCHAR(100),
    long_text TEXT
);
INSERT INTO char_test (fixed_char, variable_char, long_text)
VALUES ('hello', 'world', 'This is a long text field');

Date/Time Types

Type	Storage Size	Description
DATE	4 bytes	Date only (no time)
TIME	8 bytes	Time only (no date)
TIMETZ	12 bytes	Time with time zone
TIMESTAMP	8 bytes	Date and time
TIMESTAMPTZ	8 bytes	Date and time with time zone
INTERVAL	16 bytes	Time interval

CREATE TABLE datetime_test (
    id SERIAL PRIMARY KEY,
    a_date DATE,
    a_time TIME,
    a_timestamp TIMESTAMP,
    a_timestamptz TIMESTAMPTZ,
    a_interval INTERVAL
);

INSERT INTO datetime_test (a_date, a_time, a_timestamp, a_timestamptz, a_interval)
VALUES ('2024-01-01', '12:34:56', '2024-01-01 12:34:56', '2024-01-01 12:34:56+00',
        '3 years 4 months 1 day 2 hours 30 minutes 15 seconds');

-- Date arithmetic and formatting
SELECT CURRENT_DATE + INTERVAL '10 days';
SELECT to_char(a_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM datetime_test;

Boolean Type

CREATE TABLE bool_test (
    a BOOL,
    b BOOLEAN
);
INSERT INTO bool_test VALUES ('y', 'n'), (TRUE, FALSE);
SELECT * FROM bool_test;

Binary Type

CREATE TABLE bytea_test (
    id SERIAL PRIMARY KEY,
    data BYTEA
);
INSERT INTO bytea_test (data) VALUES ('\x48656C6C6F20576F726C64');
SELECT * FROM bytea_test;

Advanced Data Types

-- JSON and XML
CREATE TABLE advanced_test (
    json_data JSON,
    xml_data XML
);
INSERT INTO advanced_test VALUES (
    '{"name":"Halo","version":"16"}'::JSON,
    '<title>Halo Database</title>'::XML
);
SELECT * FROM advanced_test;