Wane nau irin siffofin bayyanar lissafi da PostgreSQL ke ba'a gida da standard SQL?

Question

Accepted Answer

Gida da irin siffofin standard SQL, PostgreSQL yana ba'a da sattattiyar siffofin **musamman** — `JSONB`, jeri, `UUID`, karkera, `SERIAL`, da ƙari — waɗanda ke buqa shi musamman don aikace-aikacen zamani da ke adana bayyanar tsotsin, mai-tsotse, ko musamman.

## Irin siffofin standard (tare da bayanin Postgres)

```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)
```

## Irin siffofin Postgres-musamman masu karfi

```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 da jeri a aiki

```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);
```

## Me ya sa haka makawa

Irin siffofin bayyanar lissafi masu yawa na PostgreSQL shine wata muhimmar dalili ta kada shi da karfi da diwa, kuma fahimta su yana da daraja don samun abubuwan da zai yi amfani a jiya kaɗai.

Ko da yake irin siffofin standard (lambobi, rubutu, kwanaki — tare da mahimman bayanin da ke cewa sauya **`TIMESTAMPTZ`** don lambobi masu sani da yammer da **`NUMERIC`** don kudi) suna share mahimmanci, **irin siffofin musamman na Postgres** suna bubugi karfin karfin: **`JSONB`** (binary, mai neman, *mai jigon* JSON) yana ba'a ka adana bayyanar masu lalacewa, da-siffofin-data a jikida mai alaƙa — haɗa'a umarnin alaƙa tare da NoSQL-kama na linƙi, wanda ke da jajirewa sosai don aikace-aikacen zamani (adana siffofin bambance-bambance, saitunan, API payloads) kuma babbar bugi ta Postgres; **jeri** ke ba'a al'umra masu yawa a ginshiki; **`UUID`** yana dace don abubuwan halala da ba-tsari; **irin siffofin karkera** suna wakilta cikin kyau lokuta (lokutan bada, ingantattun lokuta); kuma siffofin musamman (abokan baje, fasali-geometric/PostGIS don gidan sararin samaniya, enums) suna jiyawa bayyanar da ta musamman.

Fahimta waɗannan siffofin — musamman **JSONB** (ɗaya daga cikin mahimman bubugi ta Postgres don jiyawa bayyanar da-siffofin da ke da bubugi kuma jigon damar) da jeri — yana ba'a ka zaɓi irin siffofin da ya dace ga bayyanarki kuma samun karfin Postgres maimakon aiki kewaye su.

Wadanda aka sauraro da irin siffofin bayyanar lissafi mai yawa yana da mahimmanci ga abin da ke buqa PostgreSQL da karfi (jiyawa da alaƙa da zamani semi-siffofin bayyanar bukatuwa), fahimta Postgres-musamman irin siffofin bayyanar lissafi yana da mahimmanci, abubuwan da ake amfani a kasuwanci na gida musamman ga tsarewa schemas waɗanda ke samun karfin jiya kaɗai — musamman JSONB don bukatar waɗanda kame-kame na adana siffofin tsotsin jiya a tsotsin mai karfi.