Обсуждение: inserting data with unique index

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

I have a small table where I wanted to make changes,
update the columns and insert new data.  Because it
was small, I just did a:

pg_dump -t table -U joe_user -d database > out.sql

and edit the out.sql file to add two more columns and
put 'null' in the areas where data matching to the column
would go.

That seemed to work well.

However, when I want to insert a new record into the
table, I get this:

[snip error]

ERROR:  Cannot insert a duplicate key into unique index trend_load_id_key

[/snip error]

Initially, I thought that I had to drop and create my sequence
id for the column 'load_id' ; then I tried it after I recreated
the table.  Neither worked.

[snip table info]

bcn=> \z trend
       Access privileges for database "bcn"
           Table            |  Access privileges
----------------------------+----------------------
        trend             | {=rw,shaunn=arwdRxt}
        trend_load_id_seq |
(2 rows)

  Column  |           Type           |                           Modifiers
----------+--------------------------+----------------------------------------------------------------
 load_id  | integer                  | not null default nextval('"trend_load_id_seq"'::text)
 sum_2000 | numeric(12,2)            |
 cnt_2000 | double precision         |
 sum_2001 | numeric(12,2)            |
 cnt_2001 | double precision         |
 sum_2002 | numeric(12,2)            |
 cnt_2002 | double precision         |
 sum_2003 | numeric(12,2)            |
 cnt_2003 | double precision         |
 count_dt | timestamp with time zone |
Unique keys: trend_load_id_key

[/snip table info]

How does this work?  Do I have to specify an increment starting point
when I reload data or recreate the sequence id?

Thanks!

-X

On Tue, Jan 14, 2003 at 04:15:58PM -0500, Johnson, Shaunn wrote:
> However, when I want to insert a new record into the
> table, I get this:
>
> [snip error]
>
> ERROR:  Cannot insert a duplicate key into unique index trend_load_id_key
>
> [/snip error]

Did you do a setval() on the sequence after you reloaded?  If not,
that may be it.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110