problems with unique key on table with serial

Поиск
Список
Период
Сортировка
От chris Günther
Тема problems with unique key on table with serial
Дата
Msg-id 20010104142150.7ed33224.guenther@uscreen.de
обсуждение исходный текст
Ответы Re: problems with unique key on table with serial  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
Dear all,

I have the following problem.

I have a table (among others of course) that has the following structure:
    CREATE TABLE "tblserver" (
    "ID_Server" oid DEFAULT nextval('tblserver_id_server_seq'),
    "Svr_Name" varchar(150) NOT NULL,
    "IP_Address" inet NOT NULL DEFAULT '0.0.0.0',
    "Location" varchar(255),
    "Description" text,
    "C_Date" date,
    "C_Time" time,
    "C_User" varchar(42),
    "L_Date" date,
    "L_Time" time,
    "L_User" varchar(42),
    "C_Update" int4,
    "TimeStamp" timestamp,
    PRIMARY KEY("ID_Server")) \g

and a sequence for that (I create the sequence first and then the table)
    CREATE SEQUENCE tblserver_id_server_seq \g

than I have two functions
    CREATE FUNCTION fncdatetime_ains() returns opaque as
    'begin
        new.TimeStamp := now();
        new.C_Date := current_date;
        new.C_Time := current_time;
        new.C_User := getpgusername();
        return new;
    end;'
    language 'plpgsql' \g

    CREATE FUNCTION fncdatetime_aupd() returns opaque as
    'begin
        new.TimeStamp := now();
        new.L_Date := current_date;
        new.L_Time := current_time;
        new.L_User := getpgusername();
        return new;
    end;'
    language 'plpgsql' \g

and two triggers calling the functions:
    CREATE TRIGGER trgInsServer before insert on tblserver
    for each row execute procedure fncdatetime_ains() \g

    CREATE TRIGGER trgUpdServer before update on tblserver
    for each row execute procedure fncdatetime_aupd() \g

When I create the structure I do an insert into the table so that
I have a default-dataset. This is because I have some dependencies
on this table and I need kind of a blind dataset in it.

    INSERT INTO tblserver ("ID_Server", "Svr_Name", "IP_Address",
    "Location", "Description")
    VALUES (1, 'slughammer', '24.3.19.73', 'slugmania',
    'db-design by chris') \g

Now, when I try to insert a second dataset in the table from my
Web application I get the error-message:

Warning: PostgreSQL query failed: ERROR:
Cannot insert a duplicate key into unique index tblserver_pkey in
/sitebuilder/_inc/fnc_server_adm.php on line 178

What is that supposed to mean??? I thought When I have a sequence
pSQL determines the new PKey by itself on an insert?

Pleeease anyone give me a hint - just a small one ;-)

    chris

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Anthony E . Greene"
Дата:
Сообщение: Re: MSSQL to PgSQL
Следующее
От: Richard Tidd
Дата:
Сообщение: well, this is a newbie list...