Check existence of combination in an M.N relation

From: Erik Cederstrand <erik(at)cederstrand(dot)dk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Check existence of combination in an M.N relation
Date: 2008-02-29 15:25:42
Message-ID: 47C823F6.6000703@cederstrand.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi list

I want to store sets of configuration options in an m:n table. Each
option is defined by a parameter/value pair, and each set of options has
a unique ID (the data describes non-default hardware configuration of a
group of computers).

Now, given an arbitrary set of options, how do I check if that exact
combination already exists, and either return the ID if it exists, or
insert the set and return the new ID?

The relation is defined as:

CREATE TABLE mach_conf (
id int4 NOT NULL,
param varchar(64) NOT NULL,
value varchar(256) NOT NULL,
CONSTRAINT mach_conf_id PRIMARY KEY (id, param, value)
)

Example table:

id param value
---------------------
1 hz 2.6G
1 ncpu 8
1 ram 4G
2 hz 2.6G
3 ncpu 4
3 arch i386

If I have the set [ncpu=4, arch=i386] I want to have id=3 returned. If I
have the set [ncpu=4] I want id=4 returned.

How would I go about this? I'm on PostgreSQL8.2.

Thanks,
Erik

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Rodrigo E. De León Plicet 2008-02-29 19:57:44 Re: Check existence of combination in an M.N relation
Previous Message Sean Davis 2008-02-29 13:00:15 Re: Monitoring new records