32. PostgreSQL: Data types

A MIRAJ instance running the PostgreSQL language accepts PostgreSQL type names, both in column definitions and in conversions (x::type, CAST(x AS type)). Each type is mapped to a MIRAJ engine type, then announced to the client under its PostgreSQL name and number (OID): psql, psycopg2, pgjdbc or Npgsql receive int4, numeric, timestamptz and jsonb values.

This chapter describes each type family, the values it accepts, the errors returned, then conversions, NULL, default values, identity columns and generated columns. The differences from a PostgreSQL server are noted throughout the text and gathered in 32.17.

The examples run in the ventes schema:

CREATE SCHEMA ventes;
SET search_path TO ventes;

32.1 Summary of type families#

Keywords are recognized regardless of case. Column and table names are case-insensitive, even between double quotes: "Client" and client designate the same table.

The type of a column can be read back with format_type, information_schema.columns or pg_typeof:

CREATE TABLE prix (
    id       integer PRIMARY KEY,
    montant  numeric(10,2) NOT NULL,
    taux     real,
    poids    double precision,
    libre    numeric
);

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = 'prix'::regclass AND a.attnum > 0
ORDER BY a.attnum;

 attname | type
---------+------------------
 id      | integer
 montant | numeric(10,2)
 taux    | real
 poids   | double precision
 libre   | numeric

A table column returned as is by a query is announced to the client the way PostgreSQL does it: its declared type, its modifier (varchar(20), numeric(10,2), timestamp(3)), the OID of its table and its column number. This holds for direct reads, after DISTINCT, after GROUP BY (grouping key given by name, alias or position, column determined by the grouped primary key, *), and through a derived table or a table expression; through a view, the announced relation is the view. UNION, INTERSECT and EXCEPT keep the type and modifier shared by all their members, without a relation; members of the same varchar or char type with different lengths (varchar(20) and varchar(10)), or a varchar(n) combined with an untyped text or with NULL, keep the type without a modifier. An expression or an aggregate follows the type of its computation, with no modifier and no relation. pg_typeof likewise names the declared type after GROUP BY (character varying, not text).

SELECT montant, count(*) FROM prix GROUP BY montant;
-- column montant: numeric(10,2), table prix, column 2; count: bigint
SELECT montant FROM prix UNION ALL SELECT montant FROM prix;
-- column montant: numeric(10,2), no table

A view column is announced the way the query that defines the view announces it when run directly, the announced relation being the view: same type and same modifier on every read (whether MIRAJ serves the result it keeps for the view or replays its query), in any time zone, and in pg_attribute as well as in information_schema.columns. A computed column follows the type of its expression, an explicit conversion keeps its type, and a view read by another view keeps the declared type of the base column:

CREATE VIEW v_prix AS
  SELECT id, montant, 'n° ' || id AS libelle, id::smallint AS court, taux::numeric(6,2) AS arrondi
  FROM prix;
SELECT * FROM v_prix;
-- id: integer; montant: numeric(10,2); libelle: text; court: smallint; arrondi: numeric
SELECT pg_typeof(libelle), pg_typeof(court) FROM v_prix;   -- text | smallint

The modifier of an explicit conversion (::numeric(6,2), ::varchar(5), ::timestamptz(0)) is not announced to the client, either in a query or through a view (PostgreSQL announces it); the value itself is correctly rounded or truncated. The modifier of a ::char(n) is announced: it gives the length to which the value is padded.

Mapping between the written form and the type kept:

32.2 Integers#

CREATE TABLE mesure (
    id        integer PRIMARY KEY,
    quantite  smallint NOT NULL,
    total     bigint
);
INSERT INTO mesure VALUES (1, 32767, 9223372036854775807);

An out-of-range value written to a column is rejected (SQLSTATE 22003):

INSERT INTO mesure VALUES (2, 32768, 0);

ERROR 22003: Out of range value for column 'quantite' at row 1

Literals and calculations. A written integer that fits in 32 bits is an integer, otherwise a bigint; a number written with a decimal point or an exponent (1.5, 1e0) is a numeric.

SELECT pg_typeof(1), pg_typeof(3000000000), pg_typeof(1.5), pg_typeof(1e0);
-- integer | bigint | numeric | numeric

SELECT 2147483647 + 1;
-- ERROR 22003: integer out of range

SELECT 2147483647::bigint + 1;
-- 2147483648

An integer result returned to the client that exceeds its type is a 22003 error, as in PostgreSQL; converting an operand to bigint widens the calculation.

Between two integers, the / division is an integer division, truncated toward zero, and its result keeps the integer type; as soon as one operand is a numeric, the quotient is decimal:

SELECT 7 / 2 AS quotient, -7 / 2 AS negatif, 7.0 / 2 AS decimal, div(7, 2) AS entier, 7 % 2 AS reste;

 quotient | negatif | decimal            | entier | reste
----------+---------+--------------------+--------+-------
 3        | -3      | 3.5000000000000000 | 3      | 1

The quotient of a numeric has at least sixteen significant digits (1 / 3.0 is 0.33333333333333333333), and never fewer decimals than one of its operands. A division or modulo by zero is a 22012 error (division by zero), for integers, numeric and floating-point numbers alike.

PostgreSQL has no unsigned integer type and no one-byte integer; the corresponding MIRAJ types are still accepted (see 32.11).

32.3 Exact numbers and floating point#

INSERT INTO prix VALUES (1, 1234.567, 0.1, 0.1, 1.5),
                        (2, -0.005, 1.5, 1e100, 0.123456789012345);
SELECT * FROM prix ORDER BY id;

 id | montant | taux | poids  | libre
----+---------+------+--------+-------------------
 1  | 1234.57 | 0.1  | 0.1    | 1.5
 2  | -0.01   | 1.5  | 1e+100 | 0.123456789012345

numeric(p,s) and decimal(p,s)#

Exact decimal number of p digits, s of them after the decimal point. A value written with more decimals is rounded to the column scale (to the nearest, halves rounding away from zero: -0.005 becomes -0.01, 1.005::numeric(10,2) is 1.01); a value whose integer part does not fit is rejected:

INSERT INTO prix VALUES (3, 123456789.00, NULL, NULL, NULL);
-- ERROR 22003: Out of range value for column 'montant' at row 1

numeric without precision keeps up to twenty digits before the decimal point and eighteen after; the value is displayed without unnecessary trailing zeros (the libre column above returns 1.5 and 0.123456789012345). A nineteenth decimal is rounded; an integer part of more than twenty digits is rejected (22003). The same applies to a conversion:

SELECT 1::numeric AS a, '12.5'::numeric AS b, 0.1234567890123456789012::numeric AS c;
-- 1 | 12.5 | 0.123456789012345679

SELECT 123456789012345678901::numeric;
-- ERROR 22003: numeric field overflow: A field with precision 38, scale 18 must round to an absolute value less than 10^20.

Give an explicit precision and scale (numeric(40,0)) for a larger number.

Calculations between numeric values are exact; sum of a numeric column is a numeric, and avg a numeric with at least sixteen decimals:

SELECT 1.10 + 2.205, 2.5 * 4, round(2.5), round(-2.5), round(2.345, 2);
-- 3.305 | 10.0 | 3 | -3 | 2.35

'NaN' and infinities are not numeric values.

real and double precision#

Binary floating-point numbers on 4 and 8 bytes (IEEE 754), hence approximate: 0.1::float8 + 0.2::float8 is 0.30000000000000004. Values are displayed in their shortest form (0.1, 1e+100).

A value too large for a real column is rejected (22003). 'NaN', 'Infinity' and '-Infinity' convert to float8 in an expression, but cannot be written to a column:

SELECT 'NaN'::float8, 'Infinity'::float8, '-Infinity'::float8;
-- NaN | Infinity | -Infinity

INSERT INTO prix (id, montant, poids) VALUES (9, 0, 'NaN');
-- ERROR 22007: Incorrect double value: 'NaN' for column "ventes"."prix"."poids" at row 1

A sum of two real values is computed and announced as double precision.

32.4 Booleans#

CREATE TABLE option_client (
    client_id  integer PRIMARY KEY,
    actif      boolean NOT NULL DEFAULT true,
    newsletter boolean
);
INSERT INTO option_client VALUES (1, true, 'yes'), (2, false, 'off'), (3, 't', NULL), (4, '0', 'n');
SELECT * FROM option_client ORDER BY client_id;

 client_id | actif | newsletter
-----------+-------+------------
 1         | t     | t
 2         | f     | f
 3         | t     | NULL
 4         | f     | f

A boolean column has three states: true, false and NULL. It is returned as t or f (type bool), which drivers convert to the boolean of their language.

Texts are read case-insensitively and their prefixes are accepted ('tr', 'n'). Any other text is rejected:

INSERT INTO option_client VALUES (5, 'maybe', NULL);
-- ERROR 22P02: invalid input syntax for type boolean: "maybe"

A boolean column can be used directly as a condition, and a condition placed in the list of a SELECT is a boolean:

SELECT client_id FROM option_client WHERE actif AND newsletter IS NOT TRUE;   -- 3
SELECT client_id FROM option_client WHERE NOT actif;                          -- 2, 4
SELECT 1 = 1, 1 < 0;                                                          -- t | f

A boolean converted to text is written true or false (true::text, CAST(actif AS varchar), 'x' || true is xtrue); concat and concat_ws write it t or f (concat('x', true) is xt), as PostgreSQL does. A text converts to a boolean with ::boolean (t, true, yes, on, 1, their opposites and their prefixes; 22P02 for any other text), whether the text is written or computed ((doc ->> 'actif')::boolean). So does an integer (1::boolean); a numeric, a floating-point number, a date or a time does not convert (32.5::boolean: 42846, cannot cast type numeric to boolean). A default value written as a string is read the same way: boolean DEFAULT 'yes' is equivalent to DEFAULT true.

Differences from PostgreSQL:

• a boolean can be added like an integer: (1 = 1) + 1 is 2 (PostgreSQL rejects it);
• an integer can be written to a boolean column without conversion (INSERT … VALUES (1, 0)).

32.5 Character strings#

CREATE TABLE client (
    id     integer PRIMARY KEY,
    code   char(6) NOT NULL,
    nom    varchar(80) NOT NULL,
    notes  text
);
INSERT INTO client VALUES (1, 'C001', 'Dupont', 'Client historique'),
                          (2, 'C002', 'Élodie Martin', NULL);

Texts are UTF-8; lengths are counted in characters: length('日本語') is 3, octet_length('日本語') is 9, length('😀') is 1.

A text too long for its column is rejected; only extra trailing spaces are cut (with a notice):

INSERT INTO client VALUES (3, 'C0000003', 'Trop long', NULL);
-- ERROR 22001: Data too long for column 'code' at row 1

An explicit conversion, however, truncates: 'abcdef'::varchar(3) is abc (with a Truncated incorrect CHAR(3) value notice).

char(n) is padded with spaces on display, as in PostgreSQL: the value is sent to the client on n characters. These padding spaces count neither for length, nor in a concatenation, nor in a conversion to text or varchar, nor in comparisons (code = 'C001' and code = 'C001 ' find the same row, through the column index); octet_length and LIKE read them:

SELECT code, length(code) AS n, octet_length(code) AS octets, code || '|' AS colle,
       code LIKE 'C001' AS exact, code LIKE 'C001%' AS debut
FROM client ORDER BY id;

  code  | n | octets | colle | exact | debut
--------+---+--------+-------+-------+-------
 C001   | 4 |      6 | C001| | f     | t
 C002   | 4 |      6 | C002| | f     | f

A conversion written ::char(n) (CAST(… AS character(n)), ::bpchar(n)) follows the same rules: 'ab'::char(5) is displayed ab, length('ab'::char(5)) is 2 and octet_length('ab'::char(5)) is 5. Remaining differences from PostgreSQL:

• the value is stored without its trailing spaces and is padded only when sent to the client (PostgreSQL protocol, COPY … TO STDOUT included): the embedded interface returns it as is, just as it returns a boolean as 1 / 0;
• only a value announced as character(n) is padded: column read directly, after GROUP BY or DISTINCT, through a view, a derived table or a CTE, ::char(n) conversion. A computed value that remains a character in PostgreSQL (max(code), coalesce(code, 'x'), CASE) is announced as text and sent without padding;
• concat(code, '|'), format('%s', code), to_json(code), array_agg(code) and regular expressions (code ~ 'C$') read the value without its padding spaces.

bpchar without length declares an unlimited-length column that keeps the name of its type: bpchar in pg_attribute (format_type), character in information_schema.columns and for pg_typeof, type bpchar (1042) without a modifier for the client, bpchar in SHOW CREATE TABLE. Its trailing spaces are not significant, like those of a char(n): they are removed on write ('x ' is stored as x, length is 1, col || '|' is x|). PostgreSQL keeps them when the column is displayed on its own. Without a declared length, the value is never padded.

Comparisons: "C" collation#

Texts are compared and sorted byte by byte, as on a PostgreSQL server whose collation is C (SHOW lc_collate returns C): case and accents matter, uppercase letters sort before lowercase ones, and accented letters after plain letters. Only trailing spaces are ignored by =.

SELECT 'a' = 'A', 'e' = 'é', 'a ' = 'a';
-- f | f | t

SELECT nom FROM client WHERE nom = 'DUPONT';          -- no rows
SELECT nom FROM client WHERE nom = 'Dupont';          -- Dupont
SELECT x FROM (VALUES ('b'), ('B'), ('a'), ('É'), ('e')) t(x) ORDER BY x;
-- B, a, b, e, É

'a' and 'A' are therefore two distinct values for a primary key, a UNIQUE constraint, a GROUP BY or a DISTINCT. LIKE and ~ respect case and accents; ILIKE ignores case and accents (nom ILIKE 'elodie%' finds Élodie Martin), ~* ignores case.

COLLATE "C", COLLATE "POSIX" and COLLATE ucs_basic (with or without the pg_catalog. qualifier) force byte-by-byte comparison in an expression or a sort (ORDER BY nom COLLATE "C"). For a comparison that ignores case and accents, apply a MIRAJ collation, in the expression or on the column:

SELECT nom FROM client WHERE nom = 'DUPONT' COLLATE utf8mb4_general_ci;    -- Dupont

CREATE TABLE code_souple (code varchar(10) COLLATE utf8mb4_general_ci UNIQUE);
INSERT INTO code_souple VALUES ('a'), ('A');
-- ERROR 23505: Duplicate entry 'A' for key 'code'

MIRAJ collations are described in chapter 12.

String literals#

SELECT 'l''été' AS a, $$l'été$$ AS c, $q$un $$ dedans$q$ AS d, length('a\nb') AS n;
-- l'été | l'été | un $$ dedans | 4

"text" between double quotes is an identifier, never a string. Two strings written side by side are concatenated ('a' 'b' is ab), even on a single line. An empty string is not NULL.

32.6 Bytes: bytea#

CREATE TABLE piece_jointe (id integer PRIMARY KEY, contenu bytea);
INSERT INTO piece_jointe VALUES (1, '\x48656c6c6f'), (2, 'abc'), (3, decode('AP8Q', 'base64'));
SELECT id, contenu, length(contenu) AS octets, encode(contenu, 'base64') AS b64
FROM piece_jointe ORDER BY id;

 id | contenu      | octets | b64
----+--------------+--------+----------
 1  | \x48656c6c6f | 5      | SGVsbG8=
 2  | \x616263     | 3      | YWJj
 3  | \x00ff10     | 3      | AP8Q

A bytea column keeps a sequence of bytes of unlimited length. It is always returned in hexadecimal format \x…. On write, a text is decoded the way PostgreSQL does it:

A malformed hexadecimal text is rejected: '\xZZ' gives 22P02 (invalid input syntax for type bytea: "\xZZ"). length and octet_length count bytes; encode(b, 'hex' | 'base64' | 'escape') and decode(text, format) convert (see 36. SQL functions). The '…'::bytea conversion and the typed literal bytea '\x00' follow the same rules.

Bit strings: bit(n) and bit varying(n)#

CREATE TABLE drapeaux (id integer PRIMARY KEY, b bit(3), masque bit(8), v bit varying(8));
INSERT INTO drapeaux VALUES (1, B'101', X'0F', B'0101'), (2, '011', 'xF0', '01');
SELECT b, masque, v, b & B'110' AS et, ~masque AS inverse, b || v AS suite, b::int AS n
FROM drapeaux ORDER BY id;

 b   | masque   | v    | et  | inverse  | suite   | n
-----+----------+------+-----+----------+---------+---
 101 | 00001111 | 0101 | 100 | 11110000 | 1010101 | 5
 011 | 11110000 | 01   | 010 | 00001111 | 01101   | 3

A bit string is written B'101' (binary digits) or X'1F' (hexadecimal digits, four positions each: 00011111); it is returned as binary digits, most significant first, over all its positions. bit(n) has exactly n of them (64 at most; plain bit is a bit(1)), bit varying(n) at most n, bit varying no limit. A column accepts a bit string or a text read the same way ('101', 'x1F'):

X'DEADBEEF' is therefore a 32-bit string, not a sequence of bytes: for a bytea, write '\xDEADBEEF' or decode('DEADBEEF', 'hex').

The operators &, |, #, ~, <<, >> and ||, and the functions length, get_bit, set_bit, substring… are described in 36. SQL functions. A bit string is compared position by position, the shorter one first (B'10' < B'101'); against a bit column, a written text is read as a bit string (WHERE b = '101'). max, min, coalesce, nullif, CASE, greatest, a union, a view and a derived table keep the type; bit_and, bit_or and bit_xor of a bit column return a bit string, and a bit varying column accepts a bit(n) column without conversion.

Differences: a bit(n) is limited to 64 positions (0A000 beyond); a computed bit string kept by CREATE TABLE … AS SELECT or by a view makes a bit varying column (its length not being known in advance).

32.7 Dates and times#

CREATE TABLE evenement (
    id       integer PRIMARY KEY,
    jour     date,
    heure    time,
    saisi_le timestamp,
    recu_le  timestamptz,
    arrondi  timestamp(0)
);
SET TIME ZONE 'Europe/Paris';
INSERT INTO evenement VALUES (1, '2026-02-28', '23:59:59.5', '2026-02-28 23:59:59.123456',
                              '2026-02-28 23:59:59+02', '2026-02-28 23:59:59.7');
SELECT * FROM evenement;

 id | jour       | heure      | saisi_le                   | recu_le                | arrondi
----+------------+------------+----------------------------+------------------------+---------------------
 1  | 2026-02-28 | 23:59:59.5 | 2026-02-28 23:59:59.123456 | 2026-02-28 22:59:59+01 | 2026-02-28 23:59:59

time without time zone, timestamp without time zone and timestamp with time zone are the long forms. A precision above 6 is rejected (22023, Too big precision specified for 'a'. Maximum is 6). With a precision p, extra decimals are cut (arrondi above keeps 23:59:59, '10:00:00.123456'::time(3) is 10:00:00.123).

Accepted formats#

Dates are written in ISO format, '2026-02-28' or '20260228'; a date-time accepts a space or a T ('2026-02-28T10:00:00'). The other PostgreSQL styles ('28/02/2026', 'February 28, 2026') and the 'infinity' / '-infinity' values are not recognized.

A time column is also a duration: '25:00:00' and '-01:00:00' are accepted there, whereas PostgreSQL stops at 24:00:00.

timestamp and timestamptz#

A timestamp keeps the time as written; a written offset is removed without conversion. A timestamptz designates an instant: the written offset or time zone name fixes it, and without an offset the time is read in the session time zone.

SELECT '2026-10-11 10:15:30+02'::timestamp   AS sans_fuseau,
       '2026-10-11 10:15:30Z'::timestamptz   AS utc,
       '2026-10-11 10:15:30 Europe/Paris'::timestamptz AS paris;

 sans_fuseau         | utc                    | paris
---------------------+------------------------+------------------------
 2026-10-11 10:15:30 | 2026-10-11 12:15:30+02 | 2026-10-11 10:15:30+02

A timestamptz column is displayed in the session time zone, with its offset; changing the time zone does not change the stored instant, and does not affect a timestamp:

SET TIME ZONE 'UTC';
SELECT saisi_le, recu_le FROM evenement;

 saisi_le                   | recu_le
----------------------------+------------------------
 2026-02-28 23:59:59.123456 | 2026-02-28 21:59:59+00

At connection time, the session time zone is that of the server (SHOW TIME ZONE); SET TIME ZONE changes it (see 37. Transactions and session). Offsets (+02, -05:30, Z) and time zone names preceded by a space (… Europe/Paris) are recognized, but not abbreviations (CEST, PST).

now(), current_timestamp, transaction_timestamp(), statement_timestamp() and clock_timestamp() return a timestamptz; current_date a date, current_time a time (without time zone), localtimestamp a timestamp. In a transaction block, all of them return the transaction start time, except statement_timestamp() (statement start) and clock_timestamp() (wall clock), see 37.9.

The timetz type (time with time zone) is accepted and stored as a time: the time zone is not kept. '10:00:00+02'::timetz is 10:00:00, and a time or timetz column likewise accepts a text carrying an offset, which it removes (INSERT INTO t VALUES ('10:00:00+02') stores 10:00:00).

Typed literals and calculations#

SELECT DATE '2026-10-11', TIME '10:15:30', TIMESTAMP '2026-10-11 10:15:30',
       TIMESTAMPTZ '2026-10-11 10:15:30+02';

SELECT DATE '2026-03-01' - DATE '2026-02-01'  AS jours,
       DATE '2026-02-28' + 1                  AS lendemain,
       DATE '2026-02-28' + INTERVAL '1 month' AS plus_un_mois,
       TIMESTAMP '2026-02-28 10:00:00' - TIMESTAMP '2026-02-26 08:00:00' AS duree;

 jours | lendemain  | plus_un_mois        | duree
-------+------------+---------------------+-----------------
 28    | 2026-03-01 | 2026-03-28 00:00:00 | 2 days 02:00:00

Intervals#

An interval keeps its months, days and duration separately, like PostgreSQL, and is written like it; it can be the type of a column (stored as rewritten text, '25 hours' becoming 25:00:00; an unreadable text: 22007). The ISO 8601 format is also read ('P1D', 'P1Y2M3DT4H5M6S', 'P0001-02-03T04:05:06').

SELECT INTERVAL '1 day', INTERVAL '2 hours 30 minutes', '45 days'::interval,
       INTERVAL '1 year 2 months', INTERVAL '1 day 02:00:00', INTERVAL '1 month 1 day';
-- 1 day | 02:30:00 | 45 days | 1 year 2 mons | 1 day 02:00:00 | 1 mon 1 day

SELECT DATE '2026-01-01' + INTERVAL '3' DAY,
       TIMESTAMP '2026-01-31 10:00:00' + INTERVAL '1 month';
-- 2026-01-04 00:00:00 | 2026-02-28 10:00:00

Intervals can be added, multiplied or divided by a number, compared and sorted by duration (see chapter 36, Intervals). In the MIRAJ language, an interval column keeps its name (SHOW CREATE TABLE, DESCRIBE, dump reloaded in either language); there it remains a text, which only the PostgreSQL language checks and computes as an interval. A primary key or UNIQUE on an interval column is based on the duration, in both languages: '24:00:00' is rejected there (23505) if '1 day' exists.

Date functions (extract, date_trunc, to_char, age, AT TIME ZONE…) are described in chapter 36. SQL functions.

32.8 json and jsonb#

CREATE TABLE document (id integer PRIMARY KEY, brut json, doc jsonb);
INSERT INTO document VALUES
  (1, '{"a": 1, "b": [1, 2]}', '{"client": {"nom": "Dupont"}, "tags": ["or", "vip"]}');

SELECT doc -> 'client' ->> 'nom' AS nom,
       doc #>> '{tags,0}'        AS tag,
       doc @> '{"tags": ["vip"]}' AS vip,
       pg_typeof(doc) AS type_doc, pg_typeof(brut) AS type_brut
FROM document;

 nom    | tag | vip | type_doc | type_brut
--------+-----+-----+----------+-----------
 Dupont | or  | t   | jsonb    | json

Both types keep a JSON document: object, array, or a single value ('"texte"', '12.50', 'null'). A text that is not JSON is rejected on write:

INSERT INTO document VALUES (2, '{pas du json}', NULL);
-- ERROR 22P02: invalid input syntax for type json: "{pas du json}"

json and jsonb have the same storage and the same functions. A jsonb column is announced as jsonb to the client, in the catalog and by pg_typeof, and doc -> 'key' on it returns jsonb; a json column stays json. As in PostgreSQL:

• a json returns the document as it was written (spaces, key order, duplicate keys);
• a jsonb is normalized when written to the column and by ::jsonb: in each object, a repeated key keeps only its last value and keys are sorted by length then in binary order; spacing is canonical and a number written with an exponent is expanded (1e2: 100). Two documents that differ only in key order are equal by =;
• two jsonb values are compared by value, in the order specific to the type: null, then strings, numbers, booleans, arrays and objects; numbers by their value ('1.0'::jsonb = '1'::jsonb is true, and so is '2'::jsonb < '10'::jsonb), arrays by their number of elements then element by element, objects by their number of members then key and value of each member; an empty top-level array precedes everything. ORDER BY, DISTINCT, GROUP BY, UNION / INTERSECT / EXCEPT, IN, BETWEEN, count(DISTINCT …) and a join key follow this comparison. A document written as a string against a jsonb (doc = '1', doc IN ('{"a": 1}')) is read as jsonb.

SELECT '{"b": 1,   "a": 2, "a": 3}'::json AS brut, '{"b": 1,   "a": 2, "a": 3}'::jsonb AS doc,
       '{"a": 1, "b": 2}'::jsonb = '{"b": 2, "a": 1}'::jsonb AS egaux;

 brut                       | doc              | egaux
----------------------------+------------------+-------
 {"b": 1,   "a": 2, "a": 3} | {"a": 3, "b": 1} | t

A constant default value of a jsonb column is read as jsonb (DEFAULT '{"b": 1, "a": 2}' stores {"a": 2, "b": 1}); it is read back as '…'::jsonb, as in PostgreSQL. In the expression of a generated column or a CHECK constraint, a jsonb column keeps its type (GENERATED ALWAYS AS (doc || '{"k": 1}') STORED merges the documents).

Differences from PostgreSQL: a document stored in a jsonb column by an earlier version of the server, or written in the MIRAJ language, keeps its text (key order and duplicates) until its next write in the PostgreSQL language (UPDATE t SET doc = doc::jsonb normalizes it); it is already compared and sorted by value. A primary key and a UNIQUE constraint on a jsonb column are based on its value: 1.0 and 1, or two objects whose members are in a different order, are duplicates there (23505), including for a document stored by an earlier version (the index is rebuilt on open); an index lookup gives the same result as a doc = … filter, evaluated by value. PARTITION BY and ORDER BY in a window function, of greatest, least, nullif and of doc > ALL (SELECT …). min and max of a jsonb, which PostgreSQL lacks, follow the type's order.

Operators: -> (element, as a document), ->> (element, as text; NULL for a JSON null), #> and #>> (path written '{a,0}'), @> and <@ (containment), || (merge), - 'key' (removal), ? (the key or element exists), ?| (one of the keys exists) and ?& (all exist), the last three on a jsonb only:

SELECT doc ? 'tags' AS a, doc ?| '{x,tags}' AS b, doc ?& '{x,tags}' AS c, doc -> 'tags' ? 'vip' AS d
FROM document;

 a | b | c | d
---+---+---+---
 t | t | f | t

The key list of ?| and ?& is written as array text ('{x,tags}') or as ARRAY['x', 'tags']. The functions (jsonb_build_object, jsonb_set, jsonb_typeof, jsonb_agg…) are described in chapter 36. SQL functions.

An index can be defined on a value extracted from a document: see 33.6.

32.9 uuid#

CREATE TABLE jeton (id uuid PRIMARY KEY DEFAULT uuid(), libelle text);
INSERT INTO jeton (libelle) VALUES ('auto');
INSERT INTO jeton VALUES ('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11', 'écrit');
SELECT id, libelle FROM jeton WHERE libelle = 'écrit';

 id                                   | libelle
--------------------------------------+---------
 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | écrit

A uuid column keeps the canonical lowercase form, whatever the form received: uppercase, braces ('{a0ee…}') or thirty-two digits without hyphens. A text that is not a UUID is rejected:

INSERT INTO jeton VALUES ('pas-un-uuid', 'x');
-- ERROR 22P02: invalid input syntax for type uuid: "pas-un-uuid"

The column is announced as uuid to the client. gen_random_uuid() (like uuid_generate_v4() and uuid()) returns a random UUID, in a query as well as a default value:

CREATE TABLE jeton2 (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), libelle text);
INSERT INTO jeton2 (libelle) VALUES ('a'), ('b');      -- two distinct UUIDs

32.10 vector(n)#

CREATE TABLE article_vecteur (article_id integer PRIMARY KEY, e vector(3));
INSERT INTO article_vecteur VALUES (1, '[1,2,3]'), (2, '[0.5, 0, -1]');
SELECT article_id, e, e <-> '[1,2,3]' AS distance FROM article_vecteur ORDER BY distance;

 article_id | e          | distance
------------+------------+----------
 1          | [1,2,3]    | 0
 2          | [0.5,0,-1] | 4.5

vector(n) keeps a vector of n floating-point numbers, written '[x,y,…]'; it is announced under the vector type of the pgvector extension. A vector of another dimension is rejected (22000, expected 3 dimensions, not 2). The distance operators are <->, <#> and <+>; vector indexes are a MIRAJ extension (see 33.6).

32.11 MIRAJ types accepted#

Types specific to the MIRAJ language remain usable in a column definition. They are announced to the client under the closest PostgreSQL type:

CREATE TABLE commande_etat (
    id   integer PRIMARY KEY,
    etat enum('nouvelle', 'expediee', 'annulee') NOT NULL DEFAULT 'nouvelle'
);
INSERT INTO commande_etat (id) VALUES (1);
INSERT INTO commande_etat VALUES (2, 'perdue');
-- ERROR 22P02: invalid input value for enum commande_etat_etat_enum: "perdue"

A MIRAJ enum column serves as an enumerated type: CREATE TYPE … AS ENUM does not exist (see 32.17). It is announced to the client under the OID of its enumerated type, the one given by pg_attribute.atttypid and pg_type; this type follows the value through a view, a derived table, CASE, coalesce and max / min, and pg_typeof names it (commande_etat_etat_enum). These types are described in chapter 5.

32.12 Conversions#

Three notations convert a value:

SELECT '42'::integer AS a, CAST('3.14' AS numeric(5,2)) AS b, 2.5::integer AS c, 42::text AS d,
       '2026-10-11'::date AS e, 'yes'::boolean AS f, integer '7' AS g, date '2026-10-11' AS h;

 a  | b    | c | d  | e          | f | g | h
----+------+---+----+------------+---+---+------------
 42 | 3.14 | 3 | 42 | 2026-10-11 | t | 7 | 2026-10-11

:: binds tighter than the minus sign: -1.5::integer reads as -(1.5::integer) and is -2; write (-1.5)::integer to convert the negative number.

Conversion types#

MIRAJ conversion names remain accepted (CAST(x AS SIGNED), CAST(x AS DATETIME)), and the pg_catalog. prefix is ignored.

Text to number#

The conversion is strict, as in PostgreSQL: the whole text must be a number, surrounding spaces allowed.

SELECT '  42  '::int, '0x1F'::int, '0o17'::int, '0b101'::int, '1_000'::int;
-- 42 | 31 | 15 | 5 | 1000

-- The same forms without quotes are integers; the underscore also separates the digits
-- of a decimal or an exponent
SELECT 0x1F, 0o17, 0b101, 1_000, 0X_ff, 1_000.5, 1e1_0;
-- 31 | 15 | 5 | 1000 | 255 | 1000.5 | 10000000000

SELECT 1__0;            -- ERROR 42601 (doubled underscore; likewise 1_, 0x, 0b2)
SELECT 'abc'::int;      -- ERROR 22P02: invalid input syntax for type integer: "abc"
SELECT '1.5'::int;      -- ERROR 22P02: invalid input syntax for type integer: "1.5"
SELECT 'abc'::numeric;  -- ERROR 22P02: invalid input syntax for type numeric: "abc"
SELECT 99999::int2;     -- ERROR 22003: smallint out of range

The other types check the converted text the same way: '2026-02-30'::date (22008, date/time field value out of range), 'abc'::date and CAST('abc' AS timestamp) (22007, invalid input syntax for type …), '{bad'::jsonb and 'zz'::uuid (22P02).

Number to integer#

A decimal or floating-point number converted to an integer is rounded to the nearest, halves rounding away from zero: 32.5::int is 3, 3.5::int is 4, (-2.5)::int is −3. PostgreSQL rounds a numeric the same way, but rounds a double precision to the nearest even number (32.5::float8::int is 2 there; 3 here).

Implicit conversions#

They are broader than in PostgreSQL: a text can be compared with a number and added to it, and a number can be concatenated.

SELECT 1 + '1' AS a, '1' || 1 AS b, 1 = '1' AS c, 1 + 1.5 AS d,
       pg_typeof(1 + 1.5) AS td, pg_typeof(1 + 1.5::float8) AS te;

 a | b  | c | d   | td      | te
---+----+---+-----+---------+------------------
 2 | 11 | t | 2.5 | numeric | double precision

An integer and a numeric give a numeric; as soon as an operand is a floating-point number, the result is a double precision. 1 + '1' is computed as double precision.

32.13 NULL#

NULL represents the absence of a value, in all types. A comparison with NULL returns NULL; it is tested with IS NULL, IS NOT NULL or IS [NOT] DISTINCT FROM.

SELECT NULL = NULL AS a, NULL IS NULL AS b, 1 IS DISTINCT FROM NULL AS c,
       coalesce(NULL, 'x') AS d, nullif(1, 1) AS e, NULL || 'a' AS f, '' IS NULL AS g;

 a    | b | c | d | e    | f    | g
------+---+---+---+------+------+---
 NULL | t | t | x | NULL | NULL | f

• || returns NULL as soon as one operand is NULL.
• Aggregates ignore NULLs: count(column) does not count them, sum of no values returns NULL.
• A UNIQUE constraint allows several NULLs; a CHECK constraint whose condition is NULL is satisfied.
• Sorting: unless specified, NULLs come last in ascending order and first in descending order, as in PostgreSQL; NULLS FIRST and NULLS LAST change this (see 35. SELECT queries).

Writing NULL to a NOT NULL column, or omitting it with no default value, is a 23502 error:

ERROR 23502: Column 'a' cannot be null
ERROR 23502: Field 'a' doesn't have a default value

32.14 DEFAULT#

The default value of a column applies when an INSERT does not mention it or writes DEFAULT in its place. Without a DEFAULT clause, it is NULL.

CREATE TABLE article (
    id       serial PRIMARY KEY,
    ref      varchar(20) NOT NULL UNIQUE,
    libelle  text NOT NULL,
    prix_ht  numeric(10,2) NOT NULL DEFAULT 0,
    tva      numeric(4,2) NOT NULL DEFAULT 20.00,
    prix_ttc numeric(12,2) GENERATED ALWAYS AS (prix_ht * (1 + tva / 100)) STORED,
    actif    boolean NOT NULL DEFAULT true,
    cree_le  timestamptz NOT NULL DEFAULT now(),
    CHECK (prix_ht >= 0)
);
INSERT INTO article (ref, libelle, prix_ht) VALUES ('STY-01', 'Stylo', 1.50), ('CAH-01', 'Cahier', 3.20);
INSERT INTO article (ref, libelle, prix_ht, tva) VALUES ('LIV-01', 'Livre', 12.00, DEFAULT);
SELECT id, ref, prix_ht, tva, prix_ttc, actif FROM article ORDER BY id;

 id | ref    | prix_ht | tva   | prix_ttc | actif
----+--------+---------+-------+----------+-------
 1  | STY-01 | 1.50    | 20.00 | 1.80     | t
 2  | CAH-01 | 3.20    | 20.00 | 3.84     | t
 3  | LIV-01 | 12.00   | 20.00 | 14.40    | t

Verified forms:

An expression can refer to another column of the row (b integer DEFAULT a + 1: a row inserted with a = 5 gets b = 6), which PostgreSQL does not allow; for a value that must follow changes to the row, use a generated column (32.16). A volatile function is evaluated for each row: DEFAULT random(), DEFAULT gen_random_uuid().

A lone string is read in the column type, as PostgreSQL reads an untyped constant: boolean DEFAULT 'yes' is DEFAULT true (22P02 for a text that is not a boolean), the constant of a jsonb column is normalized (it is read back as '…'::jsonb) and that of an interval column is rewritten (DEFAULT '25 hours' stores 25:00:00), that of a uuid column is lowercased, that of a bytea column decoded ('\x0aff'). A written time zone offset is ignored for a timestamp, time or timetz column (timetz DEFAULT '10:15:00+02' stores 10:15:00, a timetz column being stored as time). A string converted to the column type ('…'::uuid, ('…'::timestamptz), '…'::date, '…'::text, with or without parentheses) is read the same way and remains a constant. The same holds for ALTER TABLE … ALTER COLUMN … SET DEFAULT.

The constant of a timestamptz column designates an instant, fixed at creation as in PostgreSQL: that of the written time zone ('2026-10-08 10:15:00+02', '… Europe/Paris', '…Z'), or, with no written time zone, that of the TimeZone of the session creating the column (CREATE TABLE, ADD COLUMN, SET DEFAULT). A row written later receives the same instant, whatever the time zone of the session writing it; pg_get_expr, column_default and SHOW CREATE TABLE display it in the time of the reading session ('2026-10-08 10:15:00+02'::timestamp with time zone in Paris, '2026-10-08 08:15:00+00'::… in UTC). Decimals beyond the column precision are cut, as for a written value (PostgreSQL rounds). In the MIRAJ language, this default reads DEFAULT '2026-10-08 08:15:00+00' (the instant in UTC). '…'::timestamptz in an expression (DEFAULT ('…+02'::timestamptz + interval '1 day'), CHECK) keeps its time zone, and is evaluated for each row.

SET TimeZone = 'Europe/Paris';
CREATE TABLE rdv (id int PRIMARY KEY, debut timestamptz DEFAULT '2026-10-08 10:15:00');
SET TimeZone = 'UTC';
INSERT INTO rdv (id) VALUES (1);
SELECT debut, pg_get_expr(adbin, adrelid) FROM rdv, pg_attrdef WHERE adrelid = 'rdv'::regclass;

         debut          |                    pg_get_expr
------------------------+---------------------------------------------------
 2026-10-08 08:15:00+00 | '2026-10-08 08:15:00+00'::timestamp with time zone

Default values can be read back in information_schema.columns.column_default, with pg_get_expr (\d) and with SHOW CREATE TABLE, which return them in the PostgreSQL language: constant converted to the column type ('x'::text, '2026-10-08'::date, '1 day'::interval, '{"a": 2}'::jsonb, 7, true). INSERT INTO t DEFAULT VALUES sets all default values; it fails (23502) if a NOT NULL column has none.

32.15 Identity columns: serial and GENERATED … AS IDENTITY#

The column receives 1, 2, 3… (or n, n + 1… with START WITH n) when the INSERT does not mention it or writes DEFAULT. The assigned value is retrieved with INSERT … RETURNING id (see 34. DML language).

INSERT INTO article (id, ref, libelle) VALUES (10, 'GOM-01', 'Gomme');
INSERT INTO article (ref, libelle) VALUES ('REG-01', 'Règle');
SELECT id, ref FROM article ORDER BY id;

 id | ref
----+--------
 1  | STY-01
 2  | CAH-01
 3  | LIV-01
 4  | REG-01
 10 | GOM-01

Good to know:

• A written value does not advance the counter, like a PostgreSQL sequence: after the INSERT of identifier 10, the next row receives 4. The generated numbers eventually reach the written value, which then returns 23505; as with nextval, the number is consumed by that failure and the next insert receives the following number.
• Catalog: for a serial column, information_schema.columns returns column_default = nextval('article_id_seq'::regclass), is_identity = NO; for a GENERATED … AS IDENTITY column, column_default is NULL, is_identity = YES and identity_generation is BY DEFAULT or ALWAYS. pg_attribute.attidentity is d (serial, BY DEFAULT) or a (ALWAYS).
• SHOW CREATE TABLE writes the next counter number: integer GENERATED BY DEFAULT AS IDENTITY (START WITH 5) NOT NULL for a table whose next row will receive 5; a reloaded dump therefore resumes the numbering.

• GENERATED ALWAYS rejects a written value, as in PostgreSQL: INSERT and UPDATE accept only DEFAULT there (428C9), except INSERT … OVERRIDING SYSTEM VALUE and COPY (see 34.1):

  ERROR 428C9: cannot insert a non-DEFAULT value into column "id"
  DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
  HINT: Use OVERRIDING SYSTEM VALUE to override.

  A serial or BY DEFAULT column accepts a written value.

• Options: START WITH n sets the first number, and numbering advances by 1 (GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10) numbers 100, 101, 102…).
• Only one identity column per table, and it must carry a key: if it is not the primary key by itself, it automatically receives a uniqueness constraint. Two serial columns in a table are rejected (42000, Incorrect table definition; there can be only one auto column and it must be defined as a key).
• TRUNCATE keeps the counter; TRUNCATE … RESTART IDENTITY resets it to its start (see 33.5).

For adjustable numbering (start, step, cycle) or numbering shared between tables, create a sequence (see 33.8).

32.16 Generated columns#

column type GENERATED ALWAYS AS (expression) STORED

The value is computed on every write of the row from the other columns, and stored with it. In the article table above, prix_ttc follows prix_ht and tva:

UPDATE article SET prix_ht = 2.00 WHERE ref = 'STY-01';
SELECT ref, prix_ht, prix_ttc FROM article WHERE ref = 'STY-01';
-- STY-01 | 2.00 | 2.40

• The expression can refer to columns of the row, constants and deterministic functions; it cannot refer to the identity column (Generated column 'x' cannot refer to auto-increment column.), nor to now() or another function whose result changes (Expression of generated column 'x' contains a disallowed function.). Both rejections carry SQLSTATE XX000.
• A generated column can be indexed and can carry a UNIQUE or CHECK constraint.
• A value written to a generated column is rejected, as in PostgreSQL; only DEFAULT is accepted there:

  INSERT INTO article (ref, libelle, prix_ttc) VALUES ('Y', 'Forcé', 99);
  -- ERROR 428C9: cannot insert a non-DEFAULT value into column "prix_ttc"
  -- DETAIL: Column "prix_ttc" is a generated column.
  UPDATE article SET prix_ttc = 1;
  -- ERROR 428C9: column "prix_ttc" can only be updated to DEFAULT

• The MIRAJ forms GENERATED ALWAYS AS (expr) VIRTUAL and GENERATED ALWAYS AS (expr) (column computed on read, not stored) are also accepted.
• The expression keeps the rules of the PostgreSQL language (functions, ||, counting in characters) even if the instance is later reopened in the MIRAJ language.

pg_attribute.attgenerated is s for a STORED column, and stays empty for a column computed on read.

32.17 Unsupported types and differences#

Missing types#

Array values in an expression are described in 32.18.

Behavior differences#

Points to watch#

The following behaviors have been observed; they differ from PostgreSQL without returning an error.

• Value outside an enumeration: SQLSTATE 22P02 (invalid input value for enum …), like PostgreSQL; the row is not written. The MIRAJ error number remains 1265.
• Order of an enumeration: max(etat), min(etat) and the < / > comparisons follow the text of the value, not the rank of its declaration. Sorting (ORDER BY etat) follows the rank, for the column read directly as well as through a view or a derived table.
• Union of strings of different types: a varchar(n) united with a char(n) is announced as text (PostgreSQL announces character varying or character). The values of a union of char(n) of different lengths (announced as character without a modifier) are not padded with spaces. CREATE TABLE … AS on a union of varchar(n) without a common modifier creates a text column (PostgreSQL: character varying without a length).

32.18 Arrays#

An array is built in an expression with ARRAY[…] or from a converted array text ('{…}'::type[]). It is returned to the client in PostgreSQL's text form ({1,2,3}, elements quoted as needed, NULL for a missing element) and announced under the array type of its elements (int4[], text[], numeric[]…). The element type is chosen as in PostgreSQL: the widest of the numbers, an untyped text read in the type of the other elements.

SELECT ARRAY[1,2,3] AS a, '{1,2}'::int[] AS b, ARRAY['a', 'b c'] AS c, (ARRAY[10,20,30])[2] AS d,
       pg_typeof(ARRAY[1,2]) AS ta, pg_typeof(ARRAY[1.5, 2]) AS tb;

 a       | b     | c         | d  | ta        | tb
---------+-------+-----------+----+-----------+-----------
 {1,2,3} | {1,2} | {a,"b c"} | 20 | integer[] | numeric[]

SELECT 2 = ANY (ARRAY[1,2,3]) AS a, 5 <> ALL ('{1,2}'::int[]) AS b;
-- t | t
SELECT x FROM unnest(ARRAY[1,2]) AS x;
-- 1, 2

Array functions (array_length, cardinality, array_to_string…) are described in chapter 36. SQL functions.

See also#

• 33. DDL language: tables, constraints, indexes, sequences.
• 34. DML language: writing values, RETURNING, COPY.
• 36. SQL functions: string, number, date and JSON functions.
• 37. Transactions and session: session time zone.
• 40. Catalog and tools: pg_type, pg_attribute, types announced to drivers.
• Chapter 29.