PostgreSQL Data Types
PostgreSQL-compatible TiDB Cloud Starter supports common PostgreSQL data types for numeric, text, binary, date and time, JSON, array, vector, and full-text search workloads.
Supported data types
The following data types are supported:
The following PostgreSQL data types are accepted but have compatibility differences:
SMALLINTandINT2: stored internally asINTEGER. The PostgreSQLSMALLINTrange is not enforced.REALandFLOAT4: stored internally asDOUBLE PRECISION, without PostgreSQL single-precision floating-point semantics.CHAR(n)andCHARACTER(n): stored internally asVARCHAR, without PostgreSQL fixed-length blank-padding semantics.
Numeric types
Use INTEGER or BIGINT for integer values:
CREATE TABLE counters (
id BIGSERIAL PRIMARY KEY,
count INTEGER NOT NULL,
total BIGINT
);
Use NUMERIC when exact decimal values are required:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
price NUMERIC(10, 2)
);
PostgreSQL-compatible TiDB Cloud Starter stores NUMERIC values using a 96-bit coefficient, which holds approximately 28–29 significant decimal digits. The decimal point can be placed to allow up to 28 fractional digits.
These limits apply even if you declare a larger precision or scale:
NUMERIC(38,0)does not allow storing a 38-digit integer. The largest supported integer is79228162514264337593543950335; larger integers cannot be stored.- A declared scale greater than 28 does not preserve more than 28 fractional digits. For example, storing
0.12345678901234567890123456789012345in aNUMERIC(60,35)column rounds it to0.1234567890123456789012345679.
Before migrating data from PostgreSQL, verify that your numeric values are within the supported range.
Use DOUBLE PRECISION for floating-point values:
CREATE TABLE measurements (
id BIGSERIAL PRIMARY KEY,
value DOUBLE PRECISION
);
Character and binary types
Use TEXT for variable-length text:
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT,
body TEXT
);
Use VARCHAR(n) when you want to enforce a maximum character length:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(64) NOT NULL
);
Use BYTEA to store binary data:
CREATE TABLE files (
id BIGSERIAL PRIMARY KEY,
content BYTEA
);
Date and time types
PostgreSQL-compatible TiDB Cloud Starter supports DATE, TIME, TIMESTAMP, TIMESTAMPTZ, and INTERVAL.
For example:
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
event_date DATE,
start_time TIME,
created_at TIMESTAMP,
published_at TIMESTAMPTZ,
duration INTERVAL
);
Use PostgreSQL typed literals when needed:
SELECT
DATE '2026-09-19',
TIME '15:30:00',
TIMESTAMP '2026-09-19 15:30:00',
TIMESTAMPTZ '2026-09-19 15:30:00+00',
INTERVAL '2 hours';
Timestamp precision
Timestamps are stored with microsecond precision.
Precision parameters from 0 to 6 are accepted in the type syntax, but they do not truncate the stored fractional seconds.
For example, TIMESTAMP(3) can still preserve six fractional digits.
Interval operations
Intervals preserve month-based and sub-month components separately, which enables calendar-aware date arithmetic.
You can add, subtract, multiply, and divide intervals:
SELECT
INTERVAL '1 day' * 2 AS two_days,
2 * INTERVAL '1 day' AS two_days_again,
INTERVAL '1 day' / 2 AS half_day,
INTERVAL '1 day' + INTERVAL '1 hour' AS day_and_hour,
INTERVAL '3 days' - INTERVAL '1 day' AS two_days,
INTERVAL '0 day' - INTERVAL '1 day' AS negative_day,
NOW() - 7 * INTERVAL '1 day' AS one_week_ago;
UUID
Use UUID to store 128-bit UUID values:
CREATE TABLE sessions (
id UUID PRIMARY KEY,
user_id BIGINT NOT NULL
);
INET
Use INET to store IPv4 or IPv6 host or network addresses:
CREATE TABLE access_log (
id BIGSERIAL PRIMARY KEY,
client_ip INET
);
For example:
INSERT INTO access_log (client_ip)
VALUES
('192.168.1.10'),
('10.0.0.0/8'),
('::1');
Equality and ordering comparisons are supported. Some PostgreSQL network operators and functions are not supported. See PostgreSQL Compatibility.
JSON and JSONB
Use JSON when preserving the original JSON text representation is important. Use JSONB for a canonical representation and operations such as JSON containment.
For example:
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
settings JSONB
);
INSERT INTO profiles (settings)
VALUES ('{"theme":"dark","notifications":true}');
SELECT *
FROM profiles
WHERE settings @> '{"theme":"dark"}';
Arrays
Use type[] to define an array:
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
tags TEXT[]
);
Insert and access array values:
INSERT INTO articles (tags)
VALUES (ARRAY['database', 'cloud', 'postgresql']);
SELECT tags[1]
FROM articles;
Vector and full-text search types
VECTOR(n) is available for vector search:
CREATE TABLE embeddings (
id BIGSERIAL PRIMARY KEY,
embedding VECTOR(3)
);
For vector operators and HNSW indexes, see Vector Search.
TSVECTOR and TSQUERY are available for PostgreSQL-compatible full-text search:
SELECT
to_tsvector('simple', 'PostgreSQL full text search')
@@ plainto_tsquery('simple', 'PostgreSQL');
For more information, see Full-Text Search.
Type coercion
PostgreSQL-compatible TiDB Cloud Starter supports PostgreSQL-style type coercion in common SQL contexts.
UNION, CASE, COALESCE, and VALUES
When text is combined with another typed value, the expression can resolve to text.
For example:
SELECT 1
UNION
SELECT 'a';
Comparisons
When a text literal is compared with a typed value, the text literal is coerced to the type of the other operand when possible.
For example:
CREATE TABLE example (
id INTEGER PRIMARY KEY
);
SELECT *
FROM example
WHERE id = '42';
In this example, '42' is coerced to INTEGER.
Numeric type promotion
When numeric types are combined, the result is promoted according to the following general precedence:
INTEGER -> BIGINT -> DOUBLE PRECISION -> NUMERIC
Unlike PostgreSQL, combining DOUBLE PRECISION and NUMERIC produces a NUMERIC result. For example:
SELECT pg_typeof(1::double precision + 2::numeric);
This query returns numeric.
If your application requires a DOUBLE PRECISION result, cast the NUMERIC operand to DOUBLE PRECISION explicitly.
Temporal type promotion
Common temporal expressions are promoted as follows:
Type casts
Use either PostgreSQL cast syntax:
SELECT CAST('42' AS INTEGER);
SELECT '42'::INTEGER;
Explicit casts are more permissive than assignment and implicit casts. For example, explicit casts can perform some conversions that an INSERT or UPDATE assignment rejects.