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
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 |