Hello.

I have been looking around in the documentation for the solution to my
problem, but I have found nothing.

I have a table lik this:

test=# \d foo;
Table "public.foo"
Column | Type | Modifiers
--------+------------------------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
val | character varying(255) | not null
cnt | integer | default 1
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
"foo_val_key" UNIQUE, btree (val)


The id column is the primary key. The val column is a unique column that
stores the value of a record. What I want to do is, that if I try to
insert a value already present in the table, I want to increment the cnt
column for that record by one insted of getting an error.

Does anybody know how to do this?


--
-the

Patience is a virtue that carries a lot of wait

Thomas H. Espe <thomases (AT) ifi (DOT) uio.no> wrote:
You could use the rule system of postgres.
Add the following rule to your table foo:

CREATE OR REPLACE RULE testrule AS ON INSERT TO foo
WHERE NEW.val IN (SELECT val FROM foo)
DO INSTEAD (UPDATE foo SET cnt=cnt+1 WHERE val=NEW.val);

Due to the evaluation of this rule you have to change the default for cnt to 0.
After inserting a new value val with cnt=0 it will automatically increment it to 1.
\d foo should show you something like:

Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
val | integer | not null
cnt | integer | default 0
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
"foo_val_key" UNIQUE, btree (val)
Rules:
testrule AS
ON INSERT TO foo
WHERE (new.val IN ( SELECT foo.val
FROM foo)) DO INSTEAD UPDATE foo SET cnt = foo.cnt + 1
WHERE foo.val = new.val

This probably isn't the optimal solution to your problem,
you could also use plpsql funtions or else.

Wilhelm

Thomas H. Espe <thomases (AT) ifi (DOT) uio.no> wrote:
Not really.

But does it solve your problem if you set the sequence to the maximum
value of 'id' as present in the table and continue from there?

Then you shouldn't get an error.

Yours,
Laurenz Albe