Обсуждение: Table X its full, what can i do now?

Hi everyone,


   I have the following sql script:

CREATE SEQUENCE "public"."teste_seq"
   INCREMENT 1  MINVALUE 1
   MAXVALUE 32767  START 1
   CACHE 1  CYCLE;

CREATE TABLE "public"."teste" (
 "id" SMALLINT DEFAULT nextval('teste_seq'::regclass) NOT NULL,
 CONSTRAINT "id_pk" PRIMARY KEY("id")
) WITHOUT OIDS;

 for($i=1;$i<32767;$i++)
 {
        $sql = "insert into teste values(DEFAULT)";
        echo pg_query($sql);
 }
$sql = "delete from teste where id=5";
pg_query($sql);
$sql = "delete from teste where id=10";
pg_query($sql);
$sql = "delete from teste where id=51";
pg_query($sql);
$sql = "delete from teste where id=35";
pg_query($sql);
$sql = "delete from teste where id=125";
pg_query($sql);
$sql = "delete from teste where id=425";
pg_query($sql);
The table teste its full but it has some holes, i can´t insert no
more, it give´s me the error: duplicate key violates unique constraint
"id_pk".
 What can i do to be able to continue inserting rows on this table?

Thanks a lot.

x asasaxax wrote:

> CREATE SEQUENCE "public"."teste_seq"
>    INCREMENT 1  MINVALUE 1
>    MAXVALUE 32767  START 1
>    CACHE 1  CYCLE;


>  What can i do to be able to continue inserting rows on this table?
>
> Thanks a lot.


Make the max value of your sequence larger.  A lot larger.

- Dan "Heron" Myers

x asasaxax wrote:
> Hi everyone,
>
>
>    I have the following sql script:
>
> CREATE SEQUENCE "public"."teste_seq"
>    INCREMENT 1  MINVALUE 1
>    MAXVALUE 32767  START 1
>    CACHE 1  CYCLE;
>
> CREATE TABLE "public"."teste" (
>  "id" SMALLINT DEFAULT nextval('teste_seq'::regclass) NOT NULL,
>  CONSTRAINT "id_pk" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>  for($i=1;$i<32767;$i++)
>  {
>         $sql = "insert into teste values(DEFAULT)";
>         echo pg_query($sql);
>  }
> $sql = "delete from teste where id=5";
> pg_query($sql);
> $sql = "delete from teste where id=10";
> pg_query($sql);
> $sql = "delete from teste where id=51";
> pg_query($sql);
> $sql = "delete from teste where id=35";
> pg_query($sql);
> $sql = "delete from teste where id=125";
> pg_query($sql);
> $sql = "delete from teste where id=425";
> pg_query($sql);
> The table teste its full but it has some holes, i can´t insert no
> more, it give´s me the error: duplicate key violates unique constraint
> "id_pk".
>  What can i do to be able to continue inserting rows on this table?
>
> Thanks a lot.

Change id column type and change maxvalue in sequence too

Is there some business logic here for the cycling sequence / ID?

On Wed, Jul 9, 2008 at 9:00 AM, x asasaxax <xanaruto@gmail.com> wrote:
> Hi everyone,
>
>
>    I have the following sql script:
>
> CREATE SEQUENCE "public"."teste_seq"
>    INCREMENT 1  MINVALUE 1
>    MAXVALUE 32767  START 1
>    CACHE 1  CYCLE;
>
> CREATE TABLE "public"."teste" (
>  "id" SMALLINT DEFAULT nextval('teste_seq'::regclass) NOT NULL,
>  CONSTRAINT "id_pk" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>  for($i=1;$i<32767;$i++)
>  {
>         $sql = "insert into teste values(DEFAULT)";
>         echo pg_query($sql);
>  }
> $sql = "delete from teste where id=5";
> pg_query($sql);
> $sql = "delete from teste where id=10";
> pg_query($sql);
> $sql = "delete from teste where id=51";
> pg_query($sql);
> $sql = "delete from teste where id=35";
> pg_query($sql);
> $sql = "delete from teste where id=125";
> pg_query($sql);
> $sql = "delete from teste where id=425";
> pg_query($sql);
> The table teste its full but it has some holes, i can´t insert no
> more, it give´s me the error: duplicate key violates unique constraint
> "id_pk".
>  What can i do to be able to continue inserting rows on this table?
>
> Thanks a lot.