IN operator: maximum reasonable?

#11

On Mon, 05 Apr 2010 13:22:13 -0700, Karsten Wutzke wrote:

Quote:

Hello,

I was wondering about using the IN operator with check constraints to
restrict the values of a column to a certain number of elements
(strings). Is there any reasonable maximum for the number of elements in
the set?

Given:

CREATE TABLE Languages
(
iso_code CHAR(2) NOT NULL PRIMARY KEY CHECK (iso_code IN ('en',
'de', 'fr', ...))
)

I might extend the list every now and then, e.g. when supporting a new
language. But where is the point the check constraint doesn't really
serve any more purpose?

I could remove the check and allow any combination, but I'm not sure why
I should do that.

Some people believe that using ENUMs, which are similar, only makes
sense for a rather low number of elements, but I'm not sure what should
be wrong with larger sets/lists.

Any comments on that appreciated.

Karsten

From the programming point of view, I believe it would be better to wrap
it up in a Perl function. That way, the table definition never changes
and check constraints would look something like this
CHECK (IS_LEGIT_LANG(LANG) == 1);

The function would search through a hash defined like this:

my %LANG= ( 'en' => 1,
'fr' => 1,
'pt' => 1);

That would probably be faster than using
CHECK( LANG IN ('en','fr','pt'...));

As an added benefit you not only get a better performance but much better
manageability, too.

--
http://mgogala.byethost5.com

#12

On 2010-04-09, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:

Quote:

On Mon, 05 Apr 2010 13:22:13 -0700, Karsten Wutzke wrote:

From the programming point of view, I believe it would be better to wrap
it up in a Perl function. That way, the table definition never changes
and check constraints would look something like this
CHECK (IS_LEGIT_LANG(LANG) == 1);

The function would search through a hash defined like this:

my %LANG= ( 'en' => 1,
'fr' => 1,
'pt' => 1);

That would probably be faster than using
CHECK( LANG IN ('en','fr','pt'...));

having not examined the source I can't categorically state that
postgres doesn't itself use a hash to resolve the IN(list) operation.

Quote:

As an added benefit you not only get a better performance but much better
manageability, too.

you could more easily(*) use an SQL function.

(* no need to install plperl)

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

#13

On Sun, 11 Apr 2010 09:17:03 +0000, Jasen Betts wrote:

Quote:

you could more easily(*) use an SQL function.

(* no need to install plperl)

I love Perl. It's my favorite tool. I am biased.

--
http://mgogala.byethost5.com

#14

On 12 Apr., 18:36, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

On Sun, 11 Apr 2010 09:17:03 +0000, Jasen Betts wrote:
you could more easily(*) use an SQL function.

(* no need to install plperl)

I love Perl. It's my favorite tool. I am biased.

Remember I posted my question into a DB/Standard SQL group on purpose.
I need the constraint on the DB. I also don't want to synchronize
application code with DB constraints.

Karsten

#15

On 2010-04-05 22:22, Karsten Wutzke wrote:
[...]

Quote:

CREATE TABLE Languages
(
iso_code CHAR(2) NOT NULL PRIMARY KEY CHECK (iso_code IN ('en',
'de', 'fr', ...))
)

I might extend the list every now and then, e.g. when supporting a new
language. But where is the point the check constraint doesn't really
serve any more purpose?

I could remove the check and allow any combination, but I'm not sure
why I should do that.

I know you are more interested in discussing the size of the check
constraint, but if you don't mind I'd like to reflect on the purpose of
the check constraint. Couldn't you achieve what you want to do by
revoking delete/insert/update on the table from certain users?

/Lennart

#16

Karsten Wutzke wrote on 12.04.2010 19:43:

Quote:

Remember I posted my question into a DB/Standard SQL group on purpose.

No, you posted to comp.databases.postgresql which is DB-specific and not a "Standard SQL group"

#17

On 04/08/2010 12:48 AM, Karsten Wutzke wrote:

Quote:

On 7 Apr., 18:46, Andreas Kretschmer <akretsch... (AT) spamfence (DOT) net
wrote:
Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com> wrote:
Andreas Kretschmer, 07.04.2010 08:51:
Karsten Wutzke<kwut... (AT) web (DOT) de> wrote:
Any comments on that appreciated.
Use a lookup-table instead. (FK-constraint)
Andreas
I was about to answer the same, until I realized his languages table *is* the lookup table and he wants to limit the values in there.
That make no sense. Create a table with a CHECK-constraint and alter
this constraint every time for a new record?

Andreas

Yes. I know it sounds contradictory at first, but if I don't use a
lookup table, I have to adjust the CHECK for each new entry, too.

Yeah, but now you have to update the constraint AND insert a record for
each new entry. IMHO the check constraint is bad, you only want the
data in the table and a FK in other tables that need such an ISO code.

Quote:

Lookup tables are for situations where you want/need to *reuse* a
check constraint, right?

Not sure I could agree here. Basically you create such a table in order
to be able to efficiently check values against it. I believe a CHECK
constraint on the other tables has these drawbacks:

- Might be slower from a certain number of entries on.
- Redundant: *all* tables that need to check this code need the same
check constraint that you need to update whenever you add a new legal code.

Quote:

Using this kind of "double-check" clearly
flags the table as a lookup table and not a regular one (even though
the check is redundant). I mean, if it's still somewhat static, I do
not change it often - if at all - anyway. It's truly another issue I'm
very undecided about.

I am not sure I follow you here with the "flagging through double
checking". In any case, if you want to limit the number of persons that
can alter the constraint then you could handle that with proper
permissions on the table.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

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

IN operator: maximum reasonable?

comp.databases.postgresql comp.databases.postgresql

Re: IN operator: maximum reasonable? - 04-09-2010 , 11:23 AM

Re: IN operator: maximum reasonable? - 04-11-2010 , 04:17 AM

Re: IN operator: maximum reasonable? - 04-12-2010 , 11:36 AM

Re: IN operator: maximum reasonable? - 04-12-2010 , 12:43 PM

Re: IN operator: maximum reasonable? - 04-12-2010 , 01:03 PM

Re: IN operator: maximum reasonable? - 04-12-2010 , 04:31 PM

Re: IN operator: maximum reasonable? - 04-14-2010 , 02:07 PM