Create Tables

Create Tables — PostgreSQL External Database

How to create PostgreSQL tables that work correctly when connected as an external data source to NocoDB and NocoBase.

System Roles

What this means:

• Table structure and relations → NocoDB style (composite PK, physical FK constraints)
• Column types → intersection of both systems (so both UIs display data correctly)
• PK type → serial / int4 (NocoDB default for PostgreSQL), NocoBase reads any PK

Base Table Template

CREATE TABLE "public"."products" (
    "id"         serial       NOT NULL,
    "title"      text,
    "created_at" timestamp    DEFAULT now(),
    "updated_at" timestamp    DEFAULT now(),
    PRIMARY KEY ("id")
);

NocoDB default for PostgreSQL: PK = serial (int4 + auto-increment), timestamps = timestamp. NocoBase as external source correctly reads serial PK.

BIGSERIAL Variant (for large IDs)

CREATE TABLE "public"."products" (
    "id"         bigserial    NOT NULL,
    "title"      text,
    "created_at" timestamp    DEFAULT now(),
    "updated_at" timestamp    DEFAULT now(),
    PRIMARY KEY ("id")
);

Both serial and bigserial are compatible with both systems. NocoDB uses serial by default but supports bigserial. NocoBase reads both.

PK Conventions

• Always use serial or bigserial with name id
• Never use UUID or text-based primary keys — NocoDB expects auto-increment integers
• FK columns must match the parent PK type (int4 for serial, int8 for bigserial)

Timestamp Conventions

• Use timestamp (without timezone) — NocoDB default
• Use timestamptz if timezone matters — both systems support it
• Always add DEFAULT now() for created_at and updated_at

Quick Reference

┌────────────────────────────────────────────────────┐
│  TABLE STRUCTURE (NocoDB + NocoBase compatible)     │
├────────────────────────────────────────────────────┤
│  PK:        serial / bigserial, column name "id"   │
│  FK:        int4 / int8 + CONSTRAINT + INDEX       │
│  Timestamps: timestamp DEFAULT now()               │
├────────────────────────────────────────────────────┤
│  RELATION STRUCTURE (NocoDB style):                │
│  • FK constraint ON DELETE/UPDATE NO ACTION        │
│  • INDEX on every FK                               │
│  • One-to-One = UNIQUE on FK                       │
│  • M2M junction = composite PK                     │
├────────────────────────────────────────────────────┤
│  FORBIDDEN: ARRAY[], ENUM, POINT, POLYGON,         │
│             PATH, CIRCLE, INHERITS, jsonb           │
└────────────────────────────────────────────────────┘

For the full column type compatibility table, see the column-types skill.