agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedFrom: Paul M. Aoki <aoki@postgres.Berkeley.EDU>
To: Tom Wye <tjw00@spg.amdahl.com>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: how to define unique keys
Date: Fri, 06 May 94 10:00:03 -0700
Message-ID: <199405061700.KAA23657@faerie.CS.Berkeley.EDU> (raw)
In-Reply-To: <9405060456.AA00988@petal.spg.amdahl.com>
tjw00@spg.amdahl.com (Tom Wye) writes:
> I have a question about defining a unique key on a class.
> It appears that the KEY operand on the create class command is not
> implemented. Is this true?
yup.
> how does one create a class with a unique index?
From: jpv@gavdos.pr.net.CH (Jean-Paul Vetterli)
To: postgres@postgres.Berkeley.EDU
Subject: Re: Managing key-value pairs
Resent-Date: Tue, 23 Nov 93 08:11:32 -0800
> From: dunkel@spin.chem.utah.edu (Reinhard Dunkel)
> Message-Id: <9311220311.AA21809@spin.chem.utah.edu>
>
> [...]
> I am trying to use Postgres 4.1 (RS/6000-370, AIX 3.2.4) to manage
> key-value pairs for a graphical user interface. I am embarrassed to
> admit that I have problems to make this easiest of all database
> applications work smoothly. I would greatly appreciate any help on the
> following problems:
>
> PROBLEM 1: I have to enforce that my "param" attribute values (keys)
> are unique in a database relation. However, the command "create foo
> (param=char16, value=text) key (param)\g" is documented in the
> reference manual but the message "WARN:Nov 21 18:10:13:RelationCreate:
> KEY not yet supported" sounds discouraging. So before writing a
> key-value pair I query the database to see if the key is known and
> then either replace or append the tupel. This approach takes two
> database accesses (one second overhead with libpq each). Is there a
> better way to do this?
> [...]
One approach to enforce uniqueness is to use rules.
Example:
---- cut here ----
# this is a spog script: %spog -f <thisfile>
# it is idempotent
#
# create key-value table:
create kv (k = char16,\
v = text)
#
# uniqueness *and* integrity:
define rule kv_r is \
on append to kv \
where kv.k = new.k or new.k ISNULL \
do instead nothing
#
# data:
append kv (k = "K1", v = "K1's value")
append kv (k = "K2", v = "K2's value")
append kv (k = "K3", v = "K3's value")
append kv (k = "K4", v = "K4's value")
append kv (k = "K5", v = "etc.")
---- cut here ----
I hope this can help.
Jean-Paul Vetterli
Route des Pommiers 19 email: jpv@gavdos.pr.net.CH
CH-1723 Marly phone: ++77 34 38 27
Switzerland fax: ++37 46 53 09
--
Paul M. Aoki | CS Div., Dept. of EECS, UCB | aoki@postgres.Berkeley.EDU
| Berkeley, CA 94720 | ...!uunet!ucbvax!aoki
===============================================================================
To add/remove yourself from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
===============================================================================
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: postgres@postgres.berkeley.edu
Cc: aoki@postgres.Berkeley.EDU, tjw00@spg.amdahl.com
Subject: Re: how to define unique keys
In-Reply-To: <199405061700.KAA23657@faerie.CS.Berkeley.EDU>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox