Printing and PDF export are disabled for this content. View it online at Full Stack Learning Simplified.

PostgreSQL Tables and Data Types🌱 Beginner

A table defines the shape of your data: its columns, their types, and their rules. Getting the types right keeps data correct and storage efficient.

Creating a table

sql

CREATE TABLE products (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    price    NUMERIC(10, 2) NOT NULL,
    in_stock BOOLEAN DEFAULT true,
    created  TIMESTAMPTZ DEFAULT now()
);

Note: GENERATED ALWAYS AS IDENTITY is the modern SQL-standard auto-increment. You'll also see the older SERIAL — both work, but IDENTITY is now preferred.

Common data types

Type	Holds
`INTEGER`, `BIGINT`	whole numbers
`NUMERIC(p,s)`	exact decimals (money!)
`REAL`, `DOUBLE PRECISION`	approximate floats
`VARCHAR(n)`, `TEXT`	text (TEXT = unlimited)
`BOOLEAN`	true / false
`DATE`, `TIMESTAMPTZ`	dates / timestamps with time zone
`JSONB`, arrays	structured data (later chapters)

Warning: Use NUMERIC for money, never REAL/DOUBLE — floating-point types can't represent decimals exactly, so 0.1 + 0.2 won't equal 0.3.

Changing a table

sql

ALTER TABLE products ADD COLUMN sku VARCHAR(20);
ALTER TABLE products DROP COLUMN sku;
ALTER TABLE products ALTER COLUMN price SET NOT NULL;
DROP TABLE products;                    -- delete the whole table

Tip: Prefer TIMESTAMPTZ (with time zone) over plain TIMESTAMP — it stores an unambiguous instant, saving you from painful time-zone bugs later.

Free preview. Sign in and subscribe to unlock all 982 lessons across 31 courses.

Loading lesson…

Printing and PDF export are disabled for this content. View it online at Full Stack Learning Simplified.

PostgreSQLPostgreSQL 18 Pro

PostgreSQL Tables and Data Types🌱 Beginner

A table defines the shape of your data: its columns, their types, and their rules. Getting the types right keeps data correct and storage efficient.

Creating a table

sql

CREATE TABLE products (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    price    NUMERIC(10, 2) NOT NULL,
    in_stock BOOLEAN DEFAULT true,
    created  TIMESTAMPTZ DEFAULT now()
);

Note: GENERATED ALWAYS AS IDENTITY is the modern SQL-standard auto-increment. You'll also see the older SERIAL — both work, but IDENTITY is now preferred.

Common data types

Type	Holds
`INTEGER`, `BIGINT`	whole numbers
`NUMERIC(p,s)`	exact decimals (money!)
`REAL`, `DOUBLE PRECISION`	approximate floats
`VARCHAR(n)`, `TEXT`	text (TEXT = unlimited)
`BOOLEAN`	true / false
`DATE`, `TIMESTAMPTZ`	dates / timestamps with time zone
`JSONB`, arrays	structured data (later chapters)

Warning: Use NUMERIC for money, never REAL/DOUBLE — floating-point types can't represent decimals exactly, so 0.1 + 0.2 won't equal 0.3.

Changing a table

sql

ALTER TABLE products ADD COLUMN sku VARCHAR(20);
ALTER TABLE products DROP COLUMN sku;
ALTER TABLE products ALTER COLUMN price SET NOT NULL;
DROP TABLE products;                    -- delete the whole table

Tip: Prefer TIMESTAMPTZ (with time zone) over plain TIMESTAMP — it stores an unambiguous instant, saving you from painful time-zone bugs later.

Free preview. Sign in and subscribe to unlock all 982 lessons across 31 courses.