Skip to main content
CockroachDB supports a rich set of SQL data types for defining column schemas and expressing values. This page documents all supported data types, their storage characteristics, and usage examples.

​
Overview

The type system differentiates between scalar types, arrays, and tuples. Each type consists of:
  • Family - equivalence group of the type
  • Oid - Postgres Object ID describing the type
  • Precision - maximum accuracy (for numeric types)
  • Width - maximum size or scale
  • Locale - location for sorting and formatting (for collated strings)

​
Numeric Types

​
Integer Types

CockroachDB supports various integer widths for different storage and range requirements. 
CREATE TABLE users (
  id INT PRIMARY KEY,
  user_count BIGINT DEFAULT 0
);

INSERT INTO users VALUES (1, 9223372036854775807);
  • Width: 64 bits
  • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • Aliases: INT8, INT64, INTEGER

​
Decimal Types

For precise decimal arithmetic, use DECIMAL or NUMERIC types. 
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2)  -- 10 total digits, 2 after decimal
);

INSERT INTO products VALUES (1, 19.99);
​
DECIMAL(N,M)
numeric
  • N (precision): Total number of digits (0 = unlimited)
  • M (width/scale): Digits after decimal point (0 = integer)

​
Floating Point Types

Floating-point types use approximate representations. Use DECIMAL for exact precision requirements like currency.
CREATE TABLE sensors (
  id INT PRIMARY KEY,
  temperature FLOAT8,    -- 64-bit IEEE 754 (default)
  humidity FLOAT4        -- 32-bit IEEE 754
);

INSERT INTO sensors VALUES (1, 23.456789, 65.2);

​
String Types

​
VARCHAR and TEXT

CREATE TABLE accounts (
  username VARCHAR(50) PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);
Variable-length string with maximum width N.

​
Collated Strings

Control string sorting and comparison using collations. 
CREATE TABLE localized_content (
  id INT PRIMARY KEY,
  title STRING COLLATE en,
  description VARCHAR(200) COLLATE de
);

-- Case-insensitive comparison
SELECT * FROM localized_content 
WHERE title COLLATE en = 'Hello';

​
Special String Types

  • NAME - PostgreSQL system identifier type (OID: T_name)
  • "char" - Single-byte internal type (OID: T_char)
  • BPCHAR - Blank-padded char (OID: T_bpchar)

​
Date and Time Types

CREATE TABLE events (
  event_date DATE DEFAULT CURRENT_DATE
);

INSERT INTO events VALUES ('2026-03-03');
TIMESTAMPTZ stores timestamps with time zone awareness. CockroachDB internally stores all timestamps in UTC.

​
Binary Types

CREATE TABLE files (
  id INT PRIMARY KEY,
  file_data BYTES,
  checksum BYTES
);

INSERT INTO files VALUES 
  (1, '\x48656c6c6f', '\xdeadbeef');

-- Decode bytes to string
SELECT id, encode(file_data, 'escape') FROM files;

​
Boolean Type

CREATE TABLE features (
  id INT PRIMARY KEY,
  enabled BOOL DEFAULT false,
  is_public BOOLEAN NOT NULL
);

INSERT INTO features VALUES 
  (1, true, false),
  (2, 't', 'f'),      -- Alternative syntax
  (3, 'yes', 'no');   -- Also supported

​
UUID Type

CREATE TABLE sessions (
  session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INT NOT NULL
);

INSERT INTO sessions (user_id) VALUES (1001);

SELECT * FROM sessions 
WHERE session_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

​
Network Address Types

​
INET

CREATE TABLE connections (
  id INT PRIMARY KEY,
  client_ip INET,
  server_ip INET
);

INSERT INTO connections VALUES 
  (1, '192.168.1.1', '10.0.0.1'),
  (2, '2001:db8::1', 'fe80::1');

-- CIDR notation supported
SELECT * FROM connections 
WHERE client_ip << '192.168.1.0/24';

​
JSON Types

CREATE TABLE api_responses (
  id INT PRIMARY KEY,
  data JSONB
);

INSERT INTO api_responses VALUES 
  (1, '{"name": "Alice", "age": 30}');

-- Query JSON fields
SELECT data->>'name' AS name FROM api_responses;

-- Index JSON fields
CREATE INVERTED INDEX ON api_responses (data);
Binary JSON format (recommended). Supports inverted indexes.

​
Bit String Types

CREATE TABLE permissions (
  user_id INT PRIMARY KEY,
  flags BIT(8),           -- Fixed 8 bits
  options VARBIT(16)      -- Variable up to 16 bits
);

INSERT INTO permissions VALUES 
  (1, B'11110000', B'101'),
  (2, B'00001111', B'1010101');

-- Bitwise operations
SELECT user_id FROM permissions 
WHERE flags & B'10000000' = B'10000000';

​
Array Types

Any scalar type can be made into an array. 
CREATE TABLE teams (
  id INT PRIMARY KEY,
  member_ids INT[],
  scores INT8[]
);

INSERT INTO teams VALUES 
  (1, ARRAY[1, 2, 3], ARRAY[100, 200, 300]);

SELECT * FROM teams WHERE 2 = ANY(member_ids);

​
Enum Types

User-defined enumerated types. 
CREATE TYPE status AS ENUM ('pending', 'active', 'inactive');

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_status status DEFAULT 'pending'
);

INSERT INTO orders VALUES 
  (1, 'active'),
  (2, 'pending');

SELECT * FROM orders WHERE order_status = 'active';

​
Special PostgreSQL Types

​
OID Type

CREATE TABLE system_objects (
  object_oid OID PRIMARY KEY,
  object_name TEXT
);

​
Vector Types

-- INT2VECTOR for system catalogs
CREATE TABLE pg_indexes (
  indkey INT2VECTOR
);

-- OIDVECTOR for system catalogs  
CREATE TABLE pg_class (
  relchecks OIDVECTOR
);

​
Type Conversion

SELECT 
  CAST('123' AS INT),
  CAST(123.456 AS INT),
  CAST('2026-03-03' AS DATE);

​
Type Compatibility

CockroachDB follows PostgreSQL’s type compatibility rules:
  • Implicit casts between compatible types (e.g., INT2 → INT4 → INT8)
  • Explicit casts required for incompatible types
  • String to any type usually requires explicit casting

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us