[SQL] SELECT from a list
Hello
I am wondering if it is possible to use a SINGLE LIKE statement for a
selection from a list.
For example: If I want to return all results that a phrase starts with a
number, can I make a call similar to the following:
SELECT * FROM table WHERE phrase LIKE
{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
If not is there an easier way than having to call this:
SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
'9%';
Thank you.
Keith
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SELECT from a list
Ð ÐÑÐ, 25.07.2004, Ð 15:18, Keith Gallant ÐÐÑÐÑ:
> Hello
>
> I am wondering if it is possible to use a SINGLE LIKE statement for a
> selection from a list.
>
> For example: If I want to return all results that a phrase starts with a
> number, can I make a call similar to the following:
>
> SELECT * FROM table WHERE phrase LIKE
> {'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
>
> If not is there an easier way than having to call this:
>
> SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
> LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
> phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
> '9%';
WHERE SUBSTRING(phrase FROM 1 FOR 1) IN ('0', '1', )
--
Markus Bertheau <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] SELECT from a list
Markus Bertheau wrote:
Ð ÐÑÐ, 25.07.2004, Ð 15:18, Keith Gallant ÐÐÑÐÑ:
Hello
I am wondering if it is possible to use a SINGLE LIKE statement for a
selection from a list.
For example: If I want to return all results that a phrase starts with a
number, can I make a call similar to the following:
SELECT * FROM table WHERE phrase LIKE
{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
If not is there an easier way than having to call this:
SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
'9%';
WHERE SUBSTRING(phrase FROM 1 FOR 1) IN ('0', '1', )
Better yet:
SELECT * FROM table WHERE phrase ~ '^[0-9]';
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] SELECT from a list
Ð ÐÑÐ, 25.07.2004, Ð 16:40, Jean-Luc Lachance ÐÐÑÐÑ: > Better yet: > > SELECT * FROM table WHERE phrase ~ '^[0-9]'; Not so sure if that's better - the regex engines aren't the fastest. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SELECT from a list
Markus Bertheau <[EMAIL PROTECTED]> writes: > В Вск, 25.07.2004, в 16:40, Jean-Luc Lachance пишет: >> Better yet: >> >> SELECT * FROM table WHERE phrase ~ '^[0-9]'; > Not so sure if that's better - the regex engines aren't the fastest. [ raised eyebrow... ] I was under the impression that we had a pretty good one as of PG 7.4. Have you tested it lately? It is true that the above won't be indexable whereas the more tedious OR form potentially could use an index. This is not the fault of the regex engine however, but of limited understanding of regexes in the planner. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] regex engine
Ð ÐÑÐ, 25.07.2004, Ð 19:34, Tom Lane ÐÐÑÐÑ: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > Ð ÐÑÐ, 25.07.2004, Ð 16:40, Jean-Luc Lachance ÐÐÑÐÑ: > >> Better yet: > >> > >> SELECT * FROM table WHERE phrase ~ '^[0-9]'; > > > Not so sure if that's better - the regex engines aren't the fastest. > > [ raised eyebrow... ] I was under the impression that we had a pretty > good one as of PG 7.4. Have you tested it lately? I wasn't trying to say that pg's regex engine was particularly slow, but that regex engines in general are slower than an exact substring search. If I'm mistaken here, please tell. pg's regex engine does have one shortcoming though: it doesn't know UTF-8. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] regex engine
Markus Bertheau <[EMAIL PROTECTED]> writes: > pg's regex engine does have one > shortcoming though: it doesn't know UTF-8. Sure it does. We borrowed it from Tcl, remember? The "character class" stuff is not locale-aware at the moment, which is something that ought to get fixed eventually, but claiming it doesn't handle UTF8 at all is simply wrong. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] regex engine
Ð ÐÑÐ, 25.07.2004, Ð 21:41, Tom Lane ÐÐÑÐÑ: > Markus Bertheau <[EMAIL PROTECTED]> writes: > > pg's regex engine does have one > > shortcoming though: it doesn't know UTF-8. > > Sure it does. We borrowed it from Tcl, remember? > > The "character class" stuff is not locale-aware at the moment, > which is something that ought to get fixed eventually, but claiming > it doesn't handle UTF8 at all is simply wrong. Turns out I tested in a SQL_ASCII database. Sorry :) -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Please help - performance problems
ctrl wrote: I have news...good news for me:) even though I wasn't able to find the answers I was looking for, I did something that made a big difference: by removing the ORDER BY clause, the same function takes now 5 milliseconds (instead of sometimes 10 minutes). I have tried to vacuum, analyze, etc...nothing worked. I post this hoping it could help somebody. How many memory are you using for the sort operations, is that column indexed ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] constraitnt on case sensetive and case insensetive columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A Bruce wrote: | hello, | | I am attempting to convert a oracle database to postgresql and I am having | some problems creating a constraint across multiple columns which are a | mixture of case insensitive and case sensitive. | | The original oracle database created an index with: | CREATE UNIQUE INDEX hatidx ON hats (upper(name), upper(server), login); | | However postgresql can handle multiple columns in the index, or one function, | but not multiple functions, thus this fails. | | Queries are only done using the actual values, so the presence of the | index is not required for performance reasons, and exists only to | enforce the constraint that (upper(name), upper(server), login) is a | unique tuple. Is there anyway to create a constraint which will check | this? I suspect it would be possible to create a trigger to check this, | however this is a little ugly, and i would like something more similar to | to the original if possible. | | Any suggestions as to how to approach this would be greatly appreciated, | -bruce | I'm using the 7.4.x version and what you ask for is supported: regression=# create table test ( a varchar, b varchar, c varchar ); CREATE TABLE regression=# create unique index test_idx on test ( upper(a), upper(b), c); CREATE INDEX Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBET87UpzwH2SGd4RAvUeAJ4vG0CxIQdUe8KjsYs/kk7yC1/dLQCgsy9t IZrziKueFyht39zm+/XoD8w= =gA20 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Object Create Date
Sameer Deshpande wrote: Hello, Which data dictionary I have to query to determine the object creation date.. F.ex I would like to find out on which date table or Index has been created.. You can't. Regards Gaeatano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
