Received: from maynard.mail.mindspring.net (maynard.mail.mindspring.net [207.69.200.243]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1AHiVE81265 for ; Sun, 10 Feb 2002 12:44:31 -0500 (EST) (envelope-from greg@turnstep.com) Received: from user-2inib0a.dsl.mindspring.com ([165.121.44.10] helo=mindspring.com) by maynard.mail.mindspring.net with smtp (Exim 3.33 #1) id 16Zy1j-0003px-00; Sun, 10 Feb 2002 12:44:36 -0500 From: "Greg Sabino Mullane" To: pgsql-sql@postgresql.org Subject: Re: SQL Help - multi values X-Echelon: Tom Daschle oil Marines CNN Peking radar DC tobacco [Hello to all my fans in domestic surveillance] India Konduz Red Cross cracking South Africa Date: Sun, 10 Feb 2002 17:53:39 -0000 X-Mailer: GregMail 1.42 Message-Id: X-Archive-Number: 200202/102 X-Sequence-Number: 6530 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Basically the problem I am having is how best to handle multiple > values for a specific column, in this case the values in > question are coming from an HTML SELECT MULTI box Two easy, fairly portable ways that come to mind are to use boolean columns or to use the bits of a large number. If you have a very small number of possible values, you might just want to use boolean columns like this: CREATE TABLE htmlselect ( title varchar(20), choice1 bool, choice2 bool, choice3 bool ); This allows you to use SQL like this: INSERT INTO htmlselect (title,choice1,choice2,choice3) VALUES ('Pigpen','t','f','t'); SELECT title FROM htmlselect WHERE choice1 is true OR choice3 is true; Your application is responsible for keeping the actual mapping of what each column "means" of course. A better way (IMO) is to set the choices up as powers of 2, and use a number to keep track of which values are set: CREATE TABLE htmlselect ( title varchar(20), choices integer ); Your application can add up all the exponents, or you can just let SQL do it, as below. Use the binary AND operator to test the values for your WHERE clause. The SQL looks like this: INSERT INTO hmtlselect (title, choices) VALUES ('Pigpen', 2^1 + 2^3 + 2^7); SELECT title FROM htmlselect WHERE choices & 2^1 > 0 OR choices & 2^7 > 0; An integer (in postgreSQL) will let you use 31 different values, from 2^0 all the way to 2^31. Use smallint if you have 15 values or less, and bigint will get you up to 62 different values. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200202101150 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iQA/AwUBPGazL7ybkGcUlkrIEQIiOgCgwDQpNeTL2+7LDmYBrVSniCTPmF4Aniqy PXL48tR/6anaXXBKZEAdV2n1 =2/dT -----END PGP SIGNATURE-----