Complete reference for PostgreSQL data types supported in DYPAI.
Text Types
Type
Description
Example
text
Variable-length string (unlimited)
'Hello World'
varchar(n)
Variable-length with limit
'Short text'
char(n)
Fixed-length string
'ABC'
Use text for most string data. PostgreSQL optimizes it well and there's no performance penalty.
Numeric Types
Type
Description
Range
int2 / smallint
16-bit integer
-32,768 to 32,767
int4 / integer
32-bit integer
-2.1B to 2.1B
int8 / bigint
64-bit integer
Very large range
float4 / real
32-bit floating point
6 decimal precision
float8 / double precision
64-bit floating point
15 decimal precision
numeric(p, s)
Exact precision
User-defined precision
serial
Auto-incrementing int4
1 to 2.1B
bigserial
Auto-incrementing int8
Very large range
-- Examples
CREATE TABLE products (
id SERIAL PRIMARY KEY,
quantity INT4 DEFAULT 0,
price NUMERIC(10, 2), -- Up to 10 digits, 2 decimals
weight FLOAT4
);
Use numeric for money and financial calculations to avoid floating-point errors.
Boolean Type
Type
Values
Storage
bool / boolean
true, false, null
1 byte
-- Boolean examples
CREATE TABLE posts (
id UUID PRIMARY KEY,
published BOOL DEFAULT false,
featured BOOL DEFAULT false
);
-- Query
SELECT * FROM posts WHERE published = true AND featured = true;
Date and Time Types
Type
Description
Example
date
Date only
'2024-01-15'
time
Time only
'14:30:00'
timestamp
Date and time (no timezone)
'2024-01-15 14:30:00'
timestamptz
Date and time (with timezone)
'2024-01-15 14:30:00+00'
interval
Time duration
'1 day 2 hours'
Always use timestamptz for timestamps to avoid timezone issues.
-- Date/time examples
CREATE TABLE events (
id UUID PRIMARY KEY,
name TEXT,
event_date DATE,
start_time TIME,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Query events in the next 7 days
SELECT * FROM events
WHERE event_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days';
UUID Type
Type
Description
Example
uuid
Universally Unique Identifier
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
-- UUID as primary key (recommended)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL
);
UUIDs are recommended for primary keys as they're globally unique and don't expose row counts.
JSON Types
Type
Description
Use Case
json
Stores JSON as text
Rarely used
jsonb
Binary JSON (indexed)
Flexible data, settings
-- JSONB for flexible data
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb
);
-- Insert with JSON
INSERT INTO products (name, metadata) VALUES (
'T-Shirt',
'{"color": "blue", "sizes": ["S", "M", "L"], "price": 29.99}'
);
-- Query JSON fields
SELECT * FROM products WHERE metadata->>'color' = 'blue';
SELECT * FROM products WHERE metadata->'sizes' ? 'M';
Array Types
Any type can be used as an array:
-- Array column
CREATE TABLE posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT,
tags TEXT[] DEFAULT '{}'
);
-- Insert with array
INSERT INTO posts (title, tags) VALUES (
'Getting Started with DYPAI',
ARRAY['tutorial', 'beginner', 'dypai']
);
-- Query arrays
SELECT * FROM posts WHERE 'tutorial' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['beginner', 'dypai'];
Binary Type
Type
Description
Use Case
bytea
Binary data
Small files, hashes
For large files, use DYPAI Storage instead of storing binary data in the database.
