PostgreSQL tana samar da auto-incrementing IDs (yawancin lokaci don primary keys) ta hanyar amfani da sequences — abubuwa na musamman da ke samar da lambobi masu sauwa waɗanda suke ƙarawa. SERIAL wata hanyar gajeriya ce da ke samar da sequence ta atomatik, kuma IDENTITY ita ce matashin SQL-standard na zamani.
users (
id SERIAL ,
name TEXT
);
users (name) ();
SERIAL ta atomatik ta samar da sequence kuma ta saita babbar jiya na ginshiki don zaiwa jiyun gaba daga gida. BIGSERIAL tana amfani da BIGINT don manyan dabugawa (mahimmi ne ga teburan da wata iya wuce ~2 billion rows).
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 ita ce hanyar zamani, SQL-standard (da aka zaɓi a gida akan SERIAL a sabon code) — ita ce tsami kuma ta guje wa wasu SERIAL quirks (kamar ma'aunin mallakar/izini jangali).
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
Sequence batu ne mai zaman kansa. SERIAL/IDENTITY suna amfani da ɗaya ƙarƙari. Lura: sequences ba a soke su ta hanyar transactions ba (don haka IDs na iya samun raunuka bayan soke — gida kuma inazo).
-- RETURNING — get the generated id back from an INSERT (very useful)
INSERT INTO users (name) VALUES ('Ann') RETURNING id;
Auto-incrementing IDs su ne tushe — kusan kowane tebu yana buƙatar unikwon primary key, kuma auto-generated sequential IDs su ne hanyar gida, don haka fahimtar yaya PostgreSQL ke sarrafa su shine ilimi mai mahimmanci na yau-yau.
Sanin SERIAL (hanyar gajeriya ta gida, wacce ta gaje jiya da ke samar da sequence ta atomatik) kuma musamman IDENTITY (GENERATED ... AS IDENTITY — hanyar zamani, SQL-standard, da aka gida don sabon code, wacce ta tsami kuma ta guje wa SERIAL's quirks) shi ne muhimmi don bayyana primary keys daidai.
Fahimtar ƙarƙarin sequences (masu zaman kansu counters da ke samar da unikwon ƙarƙarin lambobi) yana ɓata rai game da yaya auto-increment ke aiki kuma yana daci don iko mai yawa (nextval, setval).
Jiyoyi biyu masu amfani suna dace: zaɓar BIGSERIAL/BIGINT` don teburan da wata iya girma (guje wa integer overflow — alaƙa ta gida), kuma sanin cewa sequences ba a soke su ta hanyar transactions ba (don haka IDs na iya samun raunuka bayan soke ko karkace inserts — jida, ilimi da ake tsammani da ke bugi wasu).
Clause RETURNING don samun sabuwar ID jiya daga INSERT shima yana da kima, ilimin yau-yau.
Tun kowane tebu ya buƙata primary keys kuma auto-incrementing IDs su ne gida, kuma tun fahimtar SERIAL da sa'an nan IDENTITY, ƙarƙarin sequences, girman (BIGSERIAL), jajaɓincin raunuka, kuma RETURNING ya rufe abubuwan amfani, sanin yaya PostgreSQL ke samar da auto-incrementing IDs shine tushe, ilimi da aka yi amfani a yau-yau don aiki da Postgres teburan kuma wani wurin gida inda sanin hanyar zamani ta gida (IDENTITY) yana nuna Postgres competence na zamani.