[Helma-user] Support for PostgreSQL sequences

[Julian Tree]

Thanks a Hannes, I meant to test it much sooner, just have being
distracted by another project for a few weeks.


BTW, I just want to clearfiy some thing about PostgreSQL.

Since postgresql is already managing the incrementing of the id, there
is really no need to put the id in the INSERT statement.

So simple EXCLUDE the primary key  (or the key that is defined as _id)
in the insert statement will fix this problem. This will also allow
postgres to hand race conditions regarding id generation.

INSERT INTO company ( company_type, creator,
modifier, contact_name, company_name, email, phone_main, phone_fax,
phone_mobile, phone_other, web_site, comments ) VALUES ( ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ? )


If you need to grab the id of the last inserted record  then use this
statement instead

SELECT CURRVAL('name_of_seq');


starting with postgresql 8, there is a better way to do get the next id,

SELECT CURRVAL(pg_get_serial_sequence('name_of_table','name_of_column'));


WIth this method the you only have to provide the table and column
name, and the postgresql will figure out the seq_name.  This way the
user doesn't have to define the line such as

_idgen     = seq_name

however, this only works in postgresql 8 and above, see this article
for more detail
http://sql-info.de/postgresql/notes/postgresql-8.0-serial-functionality.html


On 8/8/06, Hannes Wallnoefer <hannesw at gmail.com> wrote:
> Finally some Postgresql testing! Great!
>
> 2006/8/8, Julian Tree <julian.tree at gmail.com>:
> >
> > I get following error
> > java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR:
> > relation "dual" does not exist
> > (/helma-1.5.0/apps/filepress/Root/temp.hac#10)
> > the sql Log gives me this :  SELECT 'company_company_id_seq'.nextval FROM dual
>
> That's weird, because this is the Oracle syntax. What you should get
> is "SELECT nextval('sequence_name')".
>
> What is the class name of the JDBC driver you're using? Are you using
> the latest Helma 1.5 CVS snapshot?
>
> > if I comment out the _idgen line I get this error
> > ERROR: column "company_id" is of type integer but expression is of
> > type character varying
> >
>
> OK, this is to be expected, I'm fixing it now.
>
> hannes
> _______________________________________________
> Helma-user mailing list
> Helma-user at helma.org
> http://helma.org/mailman/listinfo/helma-user
>