pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Newbie questions
6+ messages / 4 participants
[nested] [flat]

* Newbie questions
@ 1998-12-14 02:39 Frank Morton <fmorton@base2inc.com>
  1998-12-14 08:26 ` Re: [SQL] Newbie questions Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  1998-12-15 13:13 ` Re: [SQL] Newbie questions Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 6+ messages in thread

From: Frank Morton @ 1998-12-14 02:39 UTC (permalink / raw)
  To: pgsql-sql

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.






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Newbie questions
  1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
@ 1998-12-14 08:26 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  1998-12-14 11:28   ` Re: [SQL] Newbie questions Postgres DBA <postgres@nest.bistbn.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Remigiusz Sokolowski @ 1998-12-14 08:26 UTC (permalink / raw)
  To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Newbie questions
  1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
  1998-12-14 08:26 ` Re: [SQL] Newbie questions Remigiusz Sokolowski <rems@gdansk.sprint.pl>
@ 1998-12-14 11:28   ` Postgres DBA <postgres@nest.bistbn.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Postgres DBA @ 1998-12-14 11:28 UTC (permalink / raw)
  To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: Frank Morton <fmorton@base2inc.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Newbie questions
  1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
@ 1998-12-15 13:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1998-12-15 13:41   ` Re: [SQL] Newbie questions Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  1998-12-15 14:29   ` Re: [SQL] Newbie questions Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 2 replies; 6+ messages in thread

From: Herouth Maoz @ 1998-12-15 13:13 UTC (permalink / raw)
  To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Newbie questions
  1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
  1998-12-15 13:13 ` Re: [SQL] Newbie questions Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1998-12-15 13:41   ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  1 sibling, 0 replies; 6+ messages in thread

From: Remigiusz Sokolowski @ 1998-12-15 13:41 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Newbie questions
  1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
  1998-12-15 13:13 ` Re: [SQL] Newbie questions Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1998-12-15 14:29   ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 6+ messages in thread

From: Herouth Maoz @ 1998-12-15 14:29 UTC (permalink / raw)
  To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1998-12-15 14:29 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-12-14 02:39 Newbie questions Frank Morton <fmorton@base2inc.com>
1998-12-14 08:26 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1998-12-14 11:28   ` Postgres DBA <postgres@nest.bistbn.com>
1998-12-15 13:13 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-12-15 13:41   ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1998-12-15 14:29   ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox