-1

What is the benefit to use sequence rather than just insert and get generated ID in postgresql ? I mean why don't just insert a record and the rdbms generate ID for you?

J.J. Beam
  • 2,612
  • 2
  • 26
  • 55
  • 1
    Using a sequence **is** the way to have the dbms generate an ID for you, so it's unclear what you are asking. – Bergi Sep 22 '22 at 01:21
  • @Bergi Postgres has a `SERIAL` type, which avoids the need for an explicit sequence. – Tim Biegeleisen Sep 22 '22 at 01:23
  • 1
    @TimBiegeleisen Yes, and `SERIAL` is using a sequence, explicit or not, so I don't understand the question. (Btw you'd [better use an identity column instead of a `serial`](https://stackoverflow.com/q/55300370/1048572) these days, which encapsulates the underlying sequence even better) – Bergi Sep 22 '22 at 01:25
  • Here is some history about the generated ID. It was a way to conform to standards as well as associate the sequence to the table, which was always created when using serial. https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained#comment-248607 there are still use cases where you want a sequence for something other than identity columns, but other than that you should use generated ID as it is cleaner and standards conforming. – toppk Sep 22 '22 at 01:32
  • The question is unclear. using a sequence and getting the generated ID is the same as using a sequence, as far as I can tell. – Laurenz Albe Sep 22 '22 at 06:02

1 Answers1

1

For most purposes, SERIAL does the same thing as a sequence can do. However, if you wanted your auto increment column to have a custom behavior, you might have to use a sequence. For example, let's say that you wanted the sequence to start at 100. Then you could use:

CREATE SEQUENCE your_seq
    START 101
    INCREMENT 1;

INSERT INTO yourTable (id, val)
VALUES (NEXTVAL('your_seq'), 'some text here');
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • OP doesn't seem to mention `SERIAL` anywhere? – Bergi Sep 22 '22 at 01:23
  • what if I just create primary key ID? No serial, no sequence. Will it be unique after insert: e.g. table: `ID, NAME` - INSERT into table SET NAME = 'a' ? – J.J. Beam Sep 22 '22 at 01:27
  • 1
    if you don't set serial or identity, then the insert will fail with uniqueness contraint – toppk Sep 22 '22 at 01:29
  • @J.J.Beam If you have a primary key, no default value, and don't specify a value for that column, the insert will fail with the non-null constraint already. Uniqueness isn't even checked. – Bergi Sep 22 '22 at 01:36
  • This answer is misleading, as `serial` is just a pseudo-type using a `SEQUENCE` underneath. And you can start your sequence in the same fashion either way. See: https://stackoverflow.com/a/14651788/939860. For Postgres 10, consider an `IDENTITY` column instead. (Still with a `SEQUENCE` underneath.) https://stackoverflow.com/a/9875517/939860 – Erwin Brandstetter Sep 22 '22 at 01:46
  • @Erwin Is upvote possible? You still have more points than I do. – Tim Biegeleisen Sep 22 '22 at 01:47
  • @Tim I don't follow ... ? – Erwin Brandstetter Sep 22 '22 at 01:49