Kif jaħdmu l-IDs li jiżdiedu awtomatikament (SERIAL, sequences, IDENTITY)?

Question

IT Interview Practice · Accepted Answer

PostgreSQL jiġġenera IDs li jiżdiedu awtomatikament (tipikament għal primary keys) billi juża **sequences** — oġġetti speċjali li jipproduċu numri uniċi li jiżdiedu. **`SERIAL`** huwa shorthand li jinħoloq sequenza awtomatikament, u **`IDENTITY`** huwa l-ekwivalent modern tal-istandard SQL.

## SERIAL — il-shorthand klassiku

```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,      -- auto-incrementing integer (SERIAL = INT + a sequence)
  name TEXT
);
-- BIGSERIAL for larger ranges (BIGINT-backed) — use for tables expecting many rows

INSERT INTO users (name) VALUES ('Ann');   -- id auto-assigned (1, 2, 3, ...)
```

`SERIAL` jinħoloq awtomatikament sequenza u jissettja l-default tal-kolonna biex jieħu l-valur li jmiss minnha. `BIGSERIAL` juża `BIGINT` għal ranges akbar (importanti għal tabelli li jistgħu jaqbdu ~2 biljun rows).

## IDENTITY — il-mod modern, irrakkommandat (Postgres 10+)

```sql
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- SQL-standard, recommended
  name TEXT
);
-- GENERATED ALWAYS → you can't manually insert a value (cleaner)
-- GENERATED BY DEFAULT → allows manual override when needed
```

`GENERATED ... AS IDENTITY` huwa l-approċċ modern, standard SQL (preferit minn `SERIAL` f'kodiċi ġdid) — huwa aktar clean u jevita xi particularitajiet ta' `SERIAL` (bħal quirks ta' ownership/permissions).

## Kif jaħdmu l-sequences taħt il-wiċċ

```sql
CREATE SEQUENCE my_seq START 1 INCREMENT 1;
SELECT nextval('my_seq');     -- get the next value (advances the sequence)
SELECT currval('my_seq');     -- the last value YOU got
SELECT setval('my_seq', 100); -- set the current value
```

Sequenza huwa counter indipendenti. `SERIAL`/`IDENTITY` juża waħda warajha. Nota: sequences **mhumiex** irrevocati minn transazzjonijiet (għalhekk IDs jistgħu jkollhom gaps wara rollbacks — normali u mistenni).

## Jissaħħab l-ID ġdid

```sql
-- RETURNING — get the generated id back from an INSERT (very useful)
INSERT INTO users (name) VALUES ('Ann') RETURNING id;
```

## Għaliex huwa importanti

IDs li jiżdiedu awtomatikament huma fundamentali — kważi kull tabella teħtieġ primary key uniku, u IDs sekwenzjali ġenerati awtomatikament huma l-approċċ standard, għalhekk il-fhem ta' kif PostgreSQL jħandhom huwa għarfien essenzjali ta' kuljum.
");}
,

SERIAL — il-shorthand klassiku

IDENTITY — il-mod modern, irrakkommandat (Postgres 10+)

Kif jaħdmu l-sequences taħt il-wiċċ

Jissaħħab l-ID ġdid

Għaliex huwa importanti

Mistoqsijiet relatati