Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Wisconsin, USA

    Question Unanswered: 0-value in serial data type

    I don't really have a problem, but I just noticed something perhaps slightly interesting about serials.

    I notice that when I have a new table with serial datatype and do my first insert, the first value used is 1. Such as:
    CREATE TABLE testing (
    testfield serial,
    otherfield text
    INSERT INTO testing (otherfield) VALUES ('testing');
    SELECT * FROM testing;

    testfield | otherfield
    1 | testing

    However, I also notice that I'm allowed to add a row with value 0 (if I explicitly say so), ala:
    INSERT INTO testing (testfield, otherfield) VALUES (0,

    Then I can:
    INSERT INTO testing (otherfield) VALUES ('2-field');
    SELECT * FROM testing ORDER BY testfield;
    testfield | otherfield
    0 | 0-field
    1 | testing
    2 | 2-field

    Anyone know if it's in error (or otherwise not recommended) to manually insert a 0 on a serial field? Anyone know why serial starts at 1 instead of 0? ... just curious

  2. #2
    Join Date
    Sep 2001


    If you work with SERIAL datatype this is nothing else than a sequence
    behind it and a default value

    if you insert the a value for this field the default value is not being affected

    so the sequence will not be activated

    try to make an \d table

    then you see whats going on

    to answer your question directly:

    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    May 2002
    Florida, USA
    Another way of saying this is "SERIAL is not really a datatype". It is just a shorthand for

    a. An integer primary key column which has a function call as its default value
    b. A stand-alone sequence, which provides a series of unique numbers, never allowing you to backtrack. The default function call in (a) above continually calls the "next value" or nextval() from the sequence, whenever an insert is done.

    So in reality, you could define a SERIAL column in a table and never once actually use the sequence. You could manually insert any numbers in any order you want, as long as they are unique. Of course, doing this will mean your sequence will bomb if it happens upon a number you have already inserted.

    On another interesting note, there is no reason why several tables cannot "share" a single sequence, meaning that you not only have unique values within a column, but you also have unique values among the tables. (of course, you could mess with this by manually inserting values).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts