SEO

#1 (**permalink**) 04-17-2008, 10:51 PM

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2 (**permalink**) 04-17-2008, 10:51 PM

On Fri, Feb 29, 2008 at 10:25 AM, Erik Cederstrand <erik@cederstrand.dk> wrote:
> How would I go about this?

This smells like EAV.

Please read

http://joecelkothesqlapprentice.blog...e-vs-many.html

and consider reevaluating the schema according to valid relational
design (tables, columns, check constraints, etc.).

In any case, good luck.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Check existence of combination in an M.N relation