PostgreSQL compatibility

Type Aliases

For compatibility with PostgreSQL, CrateDB supports several type aliases. These aliases can be used interchangeably with CrateDB’s native type names in SQL statements, including casts.

Example:

cr> SELECT 10::INT2 AS int2;
+------+  
| int2 |  
+------+  
|   10 |  
+------+  
SELECT 1 row in set (... sec)

Alias Mapping

Alias	CrateDB Type
SHORT	SMALLINT
INT	INTEGER
INT2	SMALLINT
INT4	INTEGER
INT8	BIGINT
LONG	BIGINT
STRING	TEXT
VARCHAR	TEXT
CHARACTER VARYING	TEXT
NAME	TEXT
REGPROC	TEXT
"CHAR"	BYTE
FLOAT	REAL
FLOAT4	REAL
FLOAT8	DOUBLE PRECISION
DOUBLE	DOUBLE PRECISION
DECIMAL	NUMERIC
TIMESTAMP	TIMESTAMP WITHOUT TIME ZONE
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE

Tip: Use the PG_TYPEOF() system function to check the data type of any expression.

---

Internal-Use Types

These are special PostgreSQL system types that CrateDB supports for catalog compatibility. They are generally used internally and are not needed for typical queries.

"CHAR"

• A one-byte character used internally for enumeration items in PostgreSQL system catalogs.

• Represented as a signed integer in the range -128 to 127.

OID

• Object Identifier, used internally as a primary key in PostgreSQL system catalogs.

• Mapped to the INTEGER type in CrateDB.

REGPROC

• Alias for OID.

• Used by pg_catalog tables to reference functions in the pg_proc table.

• Casting REGPROC:

  • To TEXT → returns the function name.

  • To INTEGER → returns the function’s OID.

REGCLASS

• Alias for OID.

• Used by pg_catalog tables to reference relations in the pg_class table.

• Casting REGCLASS:

  • To TEXT → returns the relation name.

  • To INTEGER → returns the relation’s OID.

• Note: Strings cast to REGCLASS must follow valid identifier naming rules. The name is not validated against existing relations.

OIDVECTOR

• Represents one or more OID values.

• Similar to an array of integers, but cannot be used with scalar functions or expressions.

---

See also: PostgreSQL Documentation: Object Identifier (OID) Types