Data type apa saja sing ditawakake PostgreSQL saliyane SQL standar?

Question

Accepted Answer

Beyond standard SQL types, PostgreSQL offers a rich set of **specialized data types** — `JSONB`, arrays, `UUID`, ranges, `SERIAL`, and more — that make it especially capable for modern applications storing structured, semi-structured, or specialized data.

## Standard types (with Postgres notes)

```sql
INTEGER, BIGINT, SMALLINT          -- integers
NUMERIC(p,s) / DECIMAL             -- exact decimals (use for money)
REAL, DOUBLE PRECISION             -- floating point
VARCHAR(n), TEXT, CHAR(n)          -- text (TEXT for unlimited)
BOOLEAN                            -- true/false (a real boolean type)
DATE, TIME, TIMESTAMP, TIMESTAMPTZ -- TIMESTAMPTZ = timezone-aware (recommended)
```

## Postgres-specific powerful types

```sql
SERIAL / BIGSERIAL    -- auto-incrementing integer (for primary keys)
UUID                  -- universally unique id (uuid_generate_v4() / gen_random_uuid())

JSONB                 -- binary JSON — queryable, INDEXABLE, efficient (vs slower JSON)
attributes JSONB      -- store flexible/semi-structured data in a relational DB

TEXT[]                -- ARRAYS — a column holding multiple values
tags TEXT[]

INT4RANGE, TSRANGE    -- RANGE types — represent ranges (e.g. a booking time range)
period TSRANGE

INET, CIDR, MACADDR   -- network address types
POINT, POLYGON        -- geometric types (PostGIS extends this for full GIS)
ENUM                  -- custom enumerated types
```

## JSONB and arrays in action

```sql
-- JSONB — store and query structured data
INSERT INTO products (attributes) VALUES ('{"color": "red", "size": "L"}');
SELECT * FROM products WHERE attributes->>'color' = 'red';   -- extract a field
SELECT * FROM products WHERE attributes @> '{"color": "red"}';  -- containment (indexable)

-- arrays
INSERT INTO products (tags) VALUES (ARRAY['new', 'sale']);
SELECT * FROM products WHERE 'sale' = ANY(tags);
```

## Apa gunane

Sistem data type sing sugih saka PostgreSQL minangka alasan utama apa iku kapabel lan populer, lan ngerti samubarane berharga kanggo ngeksploitasi database kanthi efektif.

Meskipun standard types (integers, text, dates — kanthi catatan penting nggunakake **`TIMESTAMPTZ`** kanggo timestamps sing aware-timezone lan **`NUMERIC`** kanggo uang) nyukupi dasar, **specialized types** saka Postgres mbukak capabilities sing kuat: **`JSONB`** (binary, queryable, *indexable* JSON) ngidini sampeyan nyimpen data flexible, semi-structured ing database relasional — ngombinasikake relational integrity karo NoSQL-like flexibility, sing sangat berharga kanggo aplikasi modern (nyimpen variable attributes, settings, API payloads) lan advantage major saka Postgres; **arrays** ngidini multiple values ing kolom; **`UUID`** cocog kanggo distributed/non-sequential identifiers; **range types** ngistimewakake represent intervals (booking periods, valid ranges); lan specialized types (network addresses, geometric/PostGIS kanggo geospatial, enums) nangani data domain-specific.

Ngerti tipe-tipe iki — luwih-luwih **JSONB** (salah siji saka Postgres features paling penting kanggo handle semi-structured data kanthi query lan index support) lan arrays — ngidini sampeyan milih tipe sing paling cocog kanggo data lan ngeksploitasi Postgres strengths tinimbang nggatekake masalah.

Amarga sistem type sing sugih minangka pusat saka apa sing nggawe PostgreSQL powerful (nangani traditional relational lan modern semi-structured data needs), ngerti Postgres-specific data types minangka berharga, practically-relevant knowledge kanggo design schemas sing ngeksploitasi full capabilities database — utamane JSONB kanggo increasingly common need nyimpen flexible structured data ing robust relational system.