The best ;-) of the old Postgres 4.2 mailing list

If you are curious about how unique keys worked in PostQUEL, look at this shameless plug. The "spog" script can be executed again and again, the table will never have more than the original five rows. One thing that was often forgotten in those times was the need to enforce integrity (= non-null primary key).

The original is in file 2435 of the Postgres 4.2 mail-archive for May 1994. 
Return-Path: owner-postman 
Delivery-Date: Fri, 06 May 94 14:10:30 -0700
Return-Path: owner-postman
Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id KAA09966 for postgres-redist; Fri, 6 May 1994 10:00:05 -0700
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199405061700.KAA09966@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from faerie.CS.Berkeley.EDU (faerie.CS.Berkeley.EDU [128.32.149.14]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with ESMTP id KAA09956 for <postgres@postgres.Berkeley.EDU>; Fri, 6 May 1994 10:00:05 -0700
Received: from localhost (localhost [127.0.0.1]) by faerie.CS.Berkeley.EDU (8.6.4/8.1B) with SMTP id KAA23657; Fri, 6 May 1994 10:00:03 -0700
Message-Id: <199405061700.KAA23657@faerie.CS.Berkeley.EDU>
X-Authentication-Warning: faerie.CS.Berkeley.EDU: Host localhost didn't use HELO protocol
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: tjw00@spg.amdahl.com (Tom Wye)
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: how to define unique keys 
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Thu, 5 May 94 21:56:51 PDT 
	     <9405060456.AA00988@petal.spg.amdahl.com> 
Date: Fri, 06 May 94 10:00:03 -0700
X-Sender: aoki@postgres.Berkeley.EDU
Resent-To: postgres-redist@postgres.Berkeley.EDU
X-Mts: smtp
Resent-Date: Fri, 06 May 94 10:00:05 -0700
Resent-XMts: smtp

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.
===============================================================================
The mail dates back from the time when ordinary folks in Switzerland accessed the Internet ("the what?") via UUCP, IP being something only for universities and rich companies. My compatriots will also notice the old phone prefixes 077 and 037.

2012-07-21/jpv