PostgreSQL

Overview

PostgreSQL is the default dialect in ERDHub. It offers the richest type system of all supported dialects, making it ideal for complex schemas. ERDHub generates standard PostgreSQL DDL with:

• CREATE TYPE ... AS ENUM for named enum types
• SERIAL / BIGSERIAL / SMALLSERIAL for auto-increment columns
• CREATE INDEX with btree, hash, gin, and gist index types
• Double-quoted identifiers for reserved words and mixed-case names
• COMMENT ON TABLE / COMMENT ON COLUMN for table and column notes

Type Mappings

The following table shows how DSL types map to PostgreSQL types when exporting SQL.

Numeric Types

DSL Type	PostgreSQL Type	Notes
integer, int	INTEGER	4-byte signed integer
bigint	BIGINT	8-byte signed integer
smallint	SMALLINT	2-byte signed integer
serial	SERIAL	Auto-incrementing 4-byte integer
bigserial	BIGSERIAL	Auto-incrementing 8-byte integer
smallserial	SMALLSERIAL	Auto-incrementing 2-byte integer
float	REAL	4-byte floating point
double	DOUBLE PRECISION	8-byte floating point
decimal(p,s)	DECIMAL(p,s)	Exact numeric with precision and scale
numeric(p,s)	NUMERIC(p,s)	Same as DECIMAL
money	MONEY	Currency amount

Boolean

DSL Type	PostgreSQL Type	Notes
boolean, bool	BOOLEAN	true / false

String Types

DSL Type	PostgreSQL Type	Notes
text	TEXT	Variable-length, unlimited
varchar(n)	VARCHAR(n)	Variable-length with limit
char(n)	CHAR(n)	Fixed-length, padded

Identifier and JSON Types

DSL Type	PostgreSQL Type	Notes
uuid	UUID	128-bit universally unique identifier
json	JSON	Stored as text, validated on input
jsonb	JSONB	Binary JSON, supports indexing and querying

Date and Time Types

DSL Type	PostgreSQL Type	Notes
date	DATE	Calendar date (no time)
time	TIME	Time of day (no date)
timestamp	TIMESTAMP	Date and time without timezone
timestamptz	TIMESTAMPTZ	Date and time with timezone
timetz	TIMETZ	Time with timezone
interval	INTERVAL	Time span

Binary and Network Types

DSL Type	PostgreSQL Type	Notes
bytea	BYTEA	Variable-length binary data
inet	INET	IPv4 or IPv6 host address
cidr	CIDR	IPv4 or IPv6 network address
macaddr	MACADDR	MAC address

Other Types

DSL Type	PostgreSQL Type	Notes
xml	XML	XML data
bit	BIT	Fixed-length bit string
varbit	VARBIT	Variable-length bit string
tsvector	TSVECTOR	Full-text search document
tsquery	TSQUERY	Full-text search query
hstore	HSTORE	Key-value pairs (requires extension)
citext	CITEXT	Case-insensitive text (requires extension)

Geometric Types

DSL Type	PostgreSQL Type	Notes
point	POINT	2D point
line	LINE	Infinite line
lseg	LSEG	Line segment
box	BOX	Rectangular box
circle	CIRCLE	Circle
polygon	POLYGON	Closed polygon
path	PATH	Open or closed geometric path

Enum Handling

PostgreSQL uses dedicated enum types created with CREATE TYPE ... AS ENUM. ERDHub generates the CREATE TYPE statement before any table that references the enum.

CREATE TYPE "post_status" AS ENUM ('draft', 'published', 'archived');
CREATE TYPE "user_role" AS ENUM ('admin', 'editor', 'viewer');

Enum types are reusable across multiple tables and columns.

Auto-Increment

PostgreSQL provides three serial types for auto-incrementing columns:

Type	Range
SMALLSERIAL	1 to 32,767
SERIAL	1 to 2,147,483,647
BIGSERIAL	1 to 9,223,372,036,854,775,807

These are shorthand for creating a sequence and setting the column default. ERDHub uses SERIAL by default for auto-increment primary keys.

Identifier Quoting

PostgreSQL uses double quotes for identifier quoting. ERDHub quotes all identifiers to safely handle reserved words and mixed-case names:

"my_table"."Column Name"

Table and Column Notes

DSL notes on tables and columns are exported as PostgreSQL COMMENT ON statements:

COMMENT ON TABLE "users" IS 'Application user accounts';
COMMENT ON COLUMN "users"."email" IS 'Must be unique and verified';

Index Types

ERDHub supports four PostgreSQL index types:

Index Type	Use Case
btree	Default. Equality and range queries on most data types
hash	Equality-only lookups
gin	Full-text search, JSONB, arrays, and composite values
gist	Geometric data, full-text search, range types

Example Output

CREATE TYPE "post_status" AS ENUM ('draft', 'published', 'archived');
CREATE TYPE "user_role" AS ENUM ('admin', 'editor', 'viewer');

CREATE TABLE "users" (
  "id" BIGSERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "role" "user_role" NOT NULL DEFAULT 'viewer',
  "avatar_url" TEXT,
  "is_active" BOOLEAN NOT NULL DEFAULT true,
  "metadata" JSONB,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ
);

CREATE TABLE "posts" (
  "id" BIGSERIAL PRIMARY KEY,
  "author_id" BIGINT NOT NULL,
  "title" VARCHAR(255) NOT NULL,
  "slug" VARCHAR(255) NOT NULL UNIQUE,
  "body" TEXT,
  "status" "post_status" NOT NULL DEFAULT 'draft',
  "view_count" INTEGER NOT NULL DEFAULT 0,
  "published_at" TIMESTAMPTZ,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE "comments" (
  "id" BIGSERIAL PRIMARY KEY,
  "post_id" BIGINT NOT NULL,
  "user_id" BIGINT NOT NULL,
  "body" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX "idx_posts_author" ON "posts" USING btree ("author_id");
CREATE INDEX "idx_posts_status" ON "posts" USING btree ("status");
CREATE INDEX "idx_posts_body_search" ON "posts" USING gin (to_tsvector('english', "body"));
CREATE INDEX "idx_comments_post" ON "comments" USING btree ("post_id");

ALTER TABLE "posts" ADD CONSTRAINT "fk_posts_author"
  FOREIGN KEY ("author_id") REFERENCES "users" ("id");

ALTER TABLE "comments" ADD CONSTRAINT "fk_comments_post"
  FOREIGN KEY ("post_id") REFERENCES "posts" ("id");

ALTER TABLE "comments" ADD CONSTRAINT "fk_comments_user"
  FOREIGN KEY ("user_id") REFERENCES "users" ("id");

COMMENT ON TABLE "users" IS 'Application user accounts';
COMMENT ON COLUMN "users"."email" IS 'Must be unique and verified';
COMMENT ON TABLE "posts" IS 'Blog posts written by users';