Why does the sequence get out of sequence?

[Axier]

Hi,

I wonder how this can happen.
A "not null" field is set to "" even if a sequence should set this
to next sequence number.

Ok, I know it is not set to null, it is set to '' which is proven by
the below
Select statement but it is not set to the next sequence number and the
inserts
does not specify any value in the insert statement for the id-column.

Are there any reasons for this behaviour?

/ Axier

CREATE TABLE public.mytable (
id int4 DEFAULT nextval('mytable_seq'::text) NOT NULL,
srcid int4,
name varchar(50),
date date,
"time" time,
week int4,
CONSTRAINT mytable_idx UNIQUE (name, date),
CONSTRAINT mytable_pkey PRIMARY KEY (id)
) WITH OIDS;
CREATE SEQUENCE public.mytable_seq INCREMENT 1 MINVALUE 1 MAXVALUE
2147483647 CACHE 1;
SELECT setval('public.mytable_seq', 39903);
mydatabase=# select * from mytable where length(name)<1;
id | srcid | name | date | time | week |
-------+-------+------+--------+--------+--------+---
| 12 | |2004-01-21 | 09:39:00 | 4 |
(1 rad)

mydatabase=# delete from mytable where length(name)<1;

mydatabase=# select * from mytable where id is null;
id | srcid | name | date | time | week |
----+-------+------+------+-----+-------+
(0 rows)

Here is the executed Insert-SQL.

INSERT INTO mytable ( srcid, name, date, time, week )
VALUES( $srcid, '$name', '$date', '$time', DATE_PART('week',
CURRENT_TIMESTAMP(0) ) )