Announcement Announcement Module
Collapse
No announcement yet.
Does anyone use PostgreSQL with spring? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Does anyone use PostgreSQL with spring?

    If so do you have any SERIAL columns? I am unable to get the generated keys returned to me.

  • #2
    To the best of my knowledge, PostgreSQL doesn't support this. I'm pretty sure it didn't in v. 7, and I'm not aware that's changed in 8.

    From the manual:
    The data types serial and bigserial are not true types, but merely a notational convenience for setting up unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying

    CREATE TABLE tablename (
    colname SERIAL
    );

    is equivalent to specifying:

    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename (
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
    );

    Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator.
    If you need to know the value for a row you insert, I beleive the recommended method is to select nextval('sequenceName') and then use that value for your insert into the field.

    HTH

    Comment


    • #3
      Originally posted by bobmanc
      If so do you have any SERIAL columns? I am unable to get the generated keys returned to me.
      PostgreSQL doesn't support SERIAL columns. This is considered as a very bad thing. PostgreSQL has support for sequnces which offers a lot more flexibility. Just ask if you have further questions...

      Comment


      • #4
        The PostgreSQL database suports the SERIAL type, but as far as I know the JDBC driver does not support returning the autogenerated keys (yet).

        Comment


        • #5
          Originally posted by croco
          The PostgreSQL database suports the SERIAL type, but as far as I know the JDBC driver does not support returning the autogenerated keys (yet).
          No, there's no serial datatype. PostgreSQL has a serial keyword that - if it is used as a type - automatically creates a sequence and sets a nextval() function call for this column.

          Example:

          Code:
          CREATE TABLE foo(id serial);
          is the same as:

          Code:
          CREATE SEQUENCE foo_id;
          CREATE TABLE foo(id integer not null default nextval('foo_id_seq'::regclass));

          The only difference is, that in the firstcase a drop table cascades and also deletes the sequence - this isn't the case if the sequence is created manually.


          So, there is a serial. But this serial is just used for creating sequences in a convenient way. What the initial author was asking for (if I understood him correctly) is "auto increment" - and that isn't supported by PostgreSQL.

          Best
          Oliver

          Comment

          Working...
          X