[Tip] PostgreSQL sequences in Servoy

by Christian Batchelor
Batchelor Associates Ltd

Last week I wasted about half a day trying to work out why Servoy was refusing to save my records.

I had created a database in PostgreSQL 8.0 and told Servoy that I wanted the primary key to be managed by the database by selecting "db seq" in "define data providers" window.

Background:
In PostgreSQL, the simplest way to create a primary key, is to define a field as a SERIAL.
In a third-party tool like Aqua Data Studio or DBVizualiser you can create a table with a primary key by executing

CREATE TABLE mytable (
my_id SERIAL,
my_field TEXT,
PRIMARY KEY(my_id));

SERIAL is PostgreSQL shorthand for
1. creating a SEQUENCE (an object that generates unique identifiers)
2. telling the primary key field to use unique values from the SEQUENCE

in other words:

CREATE SEQUENCE public.mytable_my_id_seq;
CREATE TABLE public.mytable (
my_id int4 NOT NULL DEFAULT nextval('public.mytable_my_id_seq'::text),
my_field text NULL,
PRIMARY KEY(my_id)
);

To make sure Servoy could see the new table, I restarted Servoy.
However, every time it tried to save a new record, Servoy forwarded a message from PostgreSQL saying:

"ERROR: null value in column "my_id" violates not-null constraint"

If I created records in any of my third-party tools everything worked fine.

Why was this happening?

If you use database managed keys in PostgreSQL, Servoy needs to know the name of the SEQUENCE.
Select the primary key field in the "define data providers" window and in the window which appears, click the "Database Sequence" tab. In my case, Servoy had tried to guess the sequence name, but got it wrong. I corrected the sequence name to "mytable_my_id_seq".

As soon as the SEQUENCE name had been corrected everything worked fine.