Thread: Multiple Columns Keys - Good or Bad idea?
Hi, this is not really a postgres question, more of a design question so I hope no one minds... I have to intergrate data from several sites, updates and new data, etc etc, so I have come up with the following idea: all tables will have a sequence and location column, these two columns are the primary key. each site has it's own location number and own sequence inserted by default. Is this a good idea? would it be better to have just the sequence as primary key, and make sure each site has a different sequence (ie: site one starting at 1, site 2 starting at 10,000,000) The second idea seems a bit kludgy to me, but if I go the first way I have two have two cloumns as links in each table, you know SELECT * FROM foo WHERE key1=77 and key2=4 Stuck and awaiting help... Thanks -- Rob Brown-Bayliss ---======o======---
hi, i'm trieing to do a case-insensitive SELECT but i can't find a tag preposed to this scope. i tried to use ILIKE but i get a pg_error.... maybe it's not implemented in version 7.0.3 ? if so, have i any alternative way to do this case-insensitive SELECT ? tnx to all Giorgio A.
"Giorgio A." wrote: > hi, i'm trieing to do a case-insensitive SELECT but i can't find a tag > preposed to this scope. > > i tried to use ILIKE but i get a pg_error.... maybe it's not implemented in > version 7.0.3 ? if so, have i any alternative way to do this > case-insensitive SELECT ? > > tnx to all > > Giorgio A. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly Postgres has a regexp case insensitive comparision operator: '~*' See : http://www.fr.postgresql.org/users-lounge/docs/7.1/user/functions-matching.html#FUNCTIONS-REGEXP Good luck... -- _/ /_/_/_/ Johan Daine _/ /_/ _/ mailto:isis@wanadoo.be _/ /_/ _/ http://thot.dyndns.org:6080 _/ /_/ _/ _/ /_/ _/ /_/_/_/ /_/_/_/
At 06:20 PM 6/11/01 +0200, Giorgio A. wrote: >hi, i'm trieing to do a case-insensitive SELECT but i can't find a tag >preposed to this scope. > >i tried to use ILIKE but i get a pg_error.... maybe it's not implemented in >version 7.0.3 ? if so, have i any alternative way to do this >case-insensitive SELECT ? You're referring to text comparisons like "LIKE"? For case insensitive comparisons I normally use the regular expression method, which is "~*", where "*" means "to-ignore-case". e.g. SELECT address FROM people WHERE name ~* 'sam' Regards Ryan
tnx ! the use of a regular expression is a great solution ! it gives you the power to do some very nice things ! tnx to all bye Giorgio A. ----- Original Message ----- From: "Ryan Ho" <ryanho@pacific.net.sg> To: "Giorgio A." <jh@libero.it>; "PGSQL Novice List" <pgsql-novice@postgresql.org> Sent: Tuesday, June 12, 2001 4:52 AM Subject: Re: [NOVICE] case-insensitive SELECT > At 06:20 PM 6/11/01 +0200, Giorgio A. wrote: > >hi, i'm trieing to do a case-insensitive SELECT but i can't find a tag > >preposed to this scope. > > > >i tried to use ILIKE but i get a pg_error.... maybe it's not implemented in > >version 7.0.3 ? if so, have i any alternative way to do this > >case-insensitive SELECT ? > > You're referring to text comparisons like "LIKE"? > For case insensitive comparisons I normally use the regular expression > method, which is "~*", where "*" means "to-ignore-case". > > e.g. > > SELECT address FROM people WHERE name ~* 'sam' > > Regards > Ryan > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Monday 11 June 2001 16:09, Rob Brown-Bayliss wrote: > Hi, this is not really a postgres question, more of a design question so > I hope no one minds... > > I have to intergrate data from several sites, updates and new data, etc > etc, so I have come up with the following idea: > > all tables will have a sequence and location column, these two columns > are the primary key. each site has it's own location number and own > sequence inserted by default. > > Is this a good idea? would it be better to have just the sequence as > primary key, and make sure each site has a different sequence (ie: site > one starting at 1, site 2 starting at 10,000,000) > > The second idea seems a bit kludgy to me, but if I go the first way I > have two have two cloumns as links in each table, you know SELECT * FROM > foo WHERE key1=77 and key2=4 > > Stuck and awaiting help... > > Thanks I'd go for the first method. If you need to refer to the integrated table often (e.g. as foreign key in other tables), it may be a good idea to give it a new sequence. So the integrated table will have location, the sequence number at the remote site, and a unique local sequence number. Regards -- Ho Siaw Ping, Ryan IT Consultant Database / Web Apps
> I'd go for the first method. If you need to refer to the integrated table > often (e.g. as foreign key in other tables), it may be a good idea to give it > a new sequence. So the integrated table will have location, the sequence > number at the remote site, and a unique local sequence number. Thanks folks, I have decided to mix and match, I have changed the primary key to a text field and created a function that merges the location ID and the sequence like '46-153453' So I can still get the data from specific sites if needed and have only one key... -- Rob Brown-Bayliss ---======o======--- www.ZOOstation.cc