Thread: Newbie questions
These are probably simple questions, but I can't find the answer. A source for finding answers to this type of questions would be appreciated. Question #1 If a select might match MANY rows, how can I limit it to say 50? I thought you could do: select * from table limit 50; Question #2 In doing a select, is there a way to ignore if the case of the key? For example, if I have a table called animals with an id column and it might contain "dog" or "DOG" or "Dog" and I want to match all of them, how do I do it, with or without the "like" operator? I'd like to say: select * from animals where id='dogs' ignore case; How do I do this? Thanks you for your patience.
> Question #1
>
> If a select might match MANY rows, how can I limit it to say 50?
>
> I thought you could do:
>
> select * from table limit 50;
look at description of cursor in man - there are also examples
begin work;
declare cursor bookmark for
your select statement;
fetch 50 in bookmark;
close bookmark;
commit;B
>
> Question #2
>
> In doing a select, is there a way to ignore if the case of the key?
>
> For example, if I have a table called animals with an id column and
> it might contain "dog" or "DOG" or "Dog" and I want to match all
> of them, how do I do it, with or without the "like" operator?
>
> I'd like to say:
>
> select * from animals where id='dogs' ignore case;
look at operators in docs
~~ - LIKE operator
~* - match(regex), case insensitive operator
I have no idea which is better
Rem
p.s. I could make some mistakes in this examples - check in amnual or docs
--------------------------------------------------------------------------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl
--------------------------------------------------------------------------------
About LIMIT: it is implemented as feature add-on to 6.4 release and could be downloaded from patch directory on Postgres FTP site. Al. On Mon, 14 Dec 1998, Remigiusz Sokolowski wrote: > > Question #1 > > > > If a select might match MANY rows, how can I limit it to say 50? > > > > I thought you could do: > > > > select * from table limit 50; > > look at description of cursor in man - there are also examples > begin work; > declare cursor bookmark for > your select statement; > fetch 50 in bookmark; > close bookmark; > commit;B > > > > > Question #2 > > > > In doing a select, is there a way to ignore if the case of the key? > > > > For example, if I have a table called animals with an id column and > > it might contain "dog" or "DOG" or "Dog" and I want to match all > > of them, how do I do it, with or without the "like" operator? > > > > I'd like to say: > > > > select * from animals where id='dogs' ignore case; > > look at operators in docs > ~~ - LIKE operator > ~* - match(regex), case insensitive operator > I have no idea which is better > Rem > p.s. I could make some mistakes in this examples - check in amnual or docs > -------------------------------------------------------------------------------- > Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl > -------------------------------------------------------------------------------- > > > >
At 10:26 +0200 on 14/12/98, Remigiusz Sokolowski wrote: > > select * from animals where id='dogs' ignore case; > > look at operators in docs > ~~ - LIKE operator > ~* - match(regex), case insensitive operator > I have no idea which is better > Rem > p.s. I could make some mistakes in this examples - check in amnual or docs The like and regexp do a match rather than an equality test. For an exact equality ignoring case, simply use SELECT * FROM animals WHERE lower( id ) = 'dogs'; Note that this means an index on the id column will NOT be used, because each value has to be converted to lower before testing. When I needed this to be an indexed search, I added a column, called, say "lower_id", which contains the lowercase version of the value in the "id" column. Then I indexed that column and then you can ask WHERE lower_id = 'dogs' and get a fast answer. There is also the possibility of creating a functional index, but I haven't managed to cause this sort of index to "kick in" on my version of postgres (6.2.1). Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> > > select * from animals where id='dogs' ignore case;
> >
> > look at operators in docs
> > ~~ - LIKE operator
> > ~* - match(regex), case insensitive operator
> > I have no idea which is better
> > Rem
> > p.s. I could make some mistakes in this examples - check in amnual or docs
>
> The like and regexp do a match rather than an equality test.
> For an exact equality ignoring case, simply use
>
> SELECT *
> FROM animals
> WHERE lower( id ) = 'dogs';
[..]
So if they do match - I don't need to use with them some wild characters
(* or %)? And second question: Can I use % with = operator?
I know - simple question, but I feel now a little confused - manual
doesn't clear it either - I thought about it, but as long as all works
fine - I haven't needed this knowledge.
TIA
Rem
*
/\
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl i/ o\i
-----------------------------------------------------------------o/&&\----------
/ * \
Wesolych Swiat Bozego Narodzenia i i/_o _\i
Szczesliwego Nowego Roku o/ O\o
Marry Christmas and Happy New Year!!! / i \
i/____o___\i
||
At 15:41 +0200 on 15/12/98, Remigiusz Sokolowski wrote: > So if they do match - I don't need to use with them some wild characters > (* or %)? And second question: Can I use % with = operator? > I know - simple question, but I feel now a little confused - manual > doesn't clear it either - I thought about it, but as long as all works > fine - I haven't needed this knowledge. Make the distinction between a MATCH and an EQUALITY. A "match" is comparing to a pattern, and there can be more than one text that makes the comparison work. The '=' does not take wildcards - it is an equality test. It tests *exactly*. An equality test has an advantage in index usage. Case-insensitive matching doesn't trigger indices in any case (as far as I know). Case-sensitive matching triggers them only if the pattern is anchored to the left (That is, in a LIKE comparison, the pattern doesn't start with % or _, and in regular expression, it starts with a ^ followed by simple characters). Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma