Horton

Thread

  1. Multiple Columns Keys - Good or Bad idea?

    Rob Brown-Bayliss <rob@zoostation.cc> — 2001-06-11T08:09:54Z

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

  2. Re: Multiple Columns Keys - Good or Bad idea?

    Ryan Ho <ryanho@cyberronins.com> — 2001-06-11T08:48:26Z

    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

  3. case-insensitive SELECT

    Giorgio A. <jh@libero.it> — 2001-06-11T16:20:11Z

    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.

  4. Re: case-insensitive SELECT

    Johan Daine <isis@wanadoo.be> — 2001-06-12T00:04:14Z

    "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
        _/    /_/    _/
       _/    /_/    _/
 /_/_/_/    /_/_/_/

  5. Re: case-insensitive SELECT

    Ryan Ho <ryanho@pacific.net.sg> — 2001-06-12T02:52:38Z

    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

  6. Re: case-insensitive SELECT

    Giorgio A. <jh@libero.it> — 2001-06-12T07:53:38Z

    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)

  7. Re: Multiple Columns Keys - Good or Bad idea?

    Rob Brown-Bayliss <rob@zoostation.cc> — 2001-06-13T03:43:25Z

    > 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