[SQL] Case-insensitive
Someone knows how config the postgresql for case-insensitive mode ? Pedro Igor
Re: [SQL] Case-insensitive
On Friday 06 Dec 2002 12:33 pm, Pedro Igor wrote: > Someone knows how config the postgresql for case-insensitive mode ? There isn't really a case-insensitive mode (for various reasons to do with locales AFAICT). There are various case-insensitive comparisons: ILIKE instead of LIKE etc. A very common approach is to use PG's functional indexes: SELECT * FROM foo WHERE lower(foo_col) LIKE 'blah%'; along with CREATE INDEX foo_lwr_col ON foo (lower(foo_col)); -- Richard Huxton ---(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
[SQL] Accent-insensitive
Does have PostgreSQL some option to allow me execute selects accent-insensitive ? I can´t find any reference, including the manual Pedro Igor
[SQL] Rules/Trigges Trade-offs
Hi all! Is there a guideline on the use of rules compared to triggers when both can be use to achieve the same result? JLL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote: > Hi all! > > Is there a guideline on the use of rules compared to triggers when both > can be use to achieve the same result? If I can use rules I do. Rules rewrite the query so are processed once, whereas triggers get processed for every row. -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rules/Trigges Trade-offs
My book has a section on that: http://www.postgresql.org/docs/awbook.html Triggers are mostly for testing/modifying the row being inserted/updated, while rules are better for affecting other rows or other tables. Jean-Luc Lachance wrote: > Hi all! > > Is there a guideline on the use of rules compared to triggers when both > can be use to achieve the same result? > > JLL > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rules/Trigges Trade-offs
Thanks for the info. Do you mean that if an update affects more than one row I should use triggers because the rules will be executed only once? JLL Richard Huxton wrote: > > On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote: > > Hi all! > > > > Is there a guideline on the use of rules compared to triggers when both > > can be use to achieve the same result? > > If I can use rules I do. Rules rewrite the query so are processed once, > whereas triggers get processed for every row. > -- > Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rules/Trigges Trade-offs
No, the rule will affect all the rows using one query. --- Jean-Luc Lachance wrote: > Thanks for the info. > > Do you mean that if an update affects more than one row I should use > triggers because the rules will be executed only once? > > JLL > > > Richard Huxton wrote: > > > > On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote: > > > Hi all! > > > > > > Is there a guideline on the use of rules compared to triggers when both > > > can be use to achieve the same result? > > > > If I can use rules I do. Rules rewrite the query so are processed once, > > whereas triggers get processed for every row. > > -- > > Richard Huxton > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] order by a "select as" determined by case statement
I'm not a db guru by any means so please forgive me if this has an easy solution. Scenario: Want to sort by an alias for SELECT AGE(). Problem is the timestamps for the SELECT AGE will be determined by the value of another column, thus the use of CASE. What I thought would work. SELECT tblticket.ticketid,tblstatus.statusname, CASE WHEN tblstatus.statusname = 'Closed' THEN (SELECT AGE(tblticket.ticketclosed,tblticket.ticketcreated) as ticketage) ELSE (SELECT AGE(CURRENT_TIMESTAMP,tblticket.ticketcreated) as ticketage) END FROM tblticket CROSS JOIN tblstatus WHERE (tblticket.reasonid = 2) AND blah, blah, blah ORDER BY ticketage ASC CASE apparently completely ignores the ticketage alias. Anyone know how to get around this? Thank You. ---(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] order by a "select as" determined by case statement
On Fri, 6 Dec 2002, FatTony wrote: > I'm not a db guru by any means so please forgive me if this has an easy > solution. > > Scenario: > > Want to sort by an alias for SELECT AGE(). Problem is the timestamps for > the SELECT AGE will be determined by the value of another column, thus > the use of CASE. > > What I thought would work. > > SELECT tblticket.ticketid,tblstatus.statusname, > CASE WHEN tblstatus.statusname = 'Closed' > THEN (SELECT AGE(tblticket.ticketclosed,tblticket.ticketcreated) as > ticketage) > ELSE (SELECT AGE(CURRENT_TIMESTAMP,tblticket.ticketcreated) as > ticketage) > END > FROM tblticket CROSS JOIN tblstatus > WHERE (tblticket.reasonid = 2) AND blah, blah, blah > ORDER BY ticketage ASC > > CASE apparently completely ignores the ticketage alias. Anyone know how > to get around this? Probably what you want is: case when ... then ... else ... end as ticketage The aliases on the result columns of the subquery don't translate into the alias of the case expression, but you can give the case expression an alias instead. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] SELECT FOR UPDATE locks whole table
We have the following query: SELECT certificate_id INTO TEMP TABLE x_certs FROM certificate WHERE cert_status = 0 AND certificate_id BETWEEN AND AND client_id IN (1, 2, 3) ORDER BY certificate_id FOR UPDATE; Is there any reason that this query should lock the entire certificate table? Is there something strange because of the IN clause or because it is going into a temporary table? This is a production server running 7.2.2 so perhaps it is fixed in 7.3. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SELECT FOR UPDATE locks whole table
It should lock only the rows you retrieved, but I have no idea how FOR UPDATE and INTO TEMP behave. My guess is that it should work fine, but I have never seen those two used together before. --- D'Arcy J.M. Cain wrote: > We have the following query: > > SELECT certificate_id > INTO TEMP TABLE x_certs > FROM certificate > WHERE cert_status = 0 AND > certificate_id BETWEEN AND AND > client_id IN (1, 2, 3) > ORDER BY certificate_id > FOR UPDATE; > > Is there any reason that this query should lock the entire certificate table? > Is there something strange because of the IN clause or because it is going > into a temporary table? This is a production server running 7.2.2 so perhaps > it is fixed in 7.3. > > -- > D'Arcy J.M. Cain| Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Accent-insensitive
Use the 'to_ascii' function to convert your string to the "no accent"
ASCII equivalent before accomplishing your comparison
SELECT foo FROM table WHERE ( to_ascii(foo) ILIKE to_ascii('caractères
accentués') );
This does not work with all database locale (LATIN1 is OK, but LATIN9 is
not).
I was actually wondering if this is efficient enough or if there is any
more efficient method to accomplish this...
Cédric D.
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Pedro IgorSent: Friday, December 06, 2002
14:31To: [EMAIL PROTECTED]Subject: [SQL]
Accent-insensitive
Does have PostgreSQL some option to allow me
execute selects accent-insensitive ?
I can´t find any reference, including the manual
Pedro Igor
Re: [SQL] Accent-insensitive
On Fri, Dec 06, 2002 at 09:33:10PM +0100, Cédric Dufour (public) wrote:
> Use the 'to_ascii' function to convert your string to the "no accent" ASCII
> equivalent before accomplishing your comparison
>
> SELECT foo FROM table WHERE ( to_ascii(foo) ILIKE to_ascii('caractères
> accentués') );
>
> This does not work with all database locale (LATIN1 is OK, but LATIN9 is
> not).
>
> I was actually wondering if this is efficient enough or if there is any more
> efficient method to accomplish this...
I'd think that something like:
CREATE FUNCTION lower_ascii (text) RETURNS text AS '
BEGIN
RETURN lower(to_ascii($1));
END
' language 'plpgsql';
CREATE INDEX table_lower_ascii ON table(lower_ascii(field));
would perform better, since we can now use this index, whereas we
couldn't do this with ILIKE to_ascii(...).
Also, not sure it's a good idea to use ILIKE simply to get
lower-case-matching. If the user string ends with '%', for instance, it
will match everything-starting-with, which is probably not what the user
meant. Better the check against lower().
There might be a better way specifically-oriented toward
de-accentuation; this is just generic PG advice.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SELECT FOR UPDATE locks whole table
"D'Arcy J.M. Cain" <[EMAIL PROTECTED]> writes: > We have the following query: > SELECT certificate_id > INTO TEMP TABLE x_certs > FROM certificate > WHERE cert_status = 0 AND > certificate_id BETWEEN AND AND > client_id IN (1, 2, 3) > ORDER BY certificate_id > FOR UPDATE; > Is there any reason that this query should lock the entire certificate > table? It should only lock the selected rows ... and does, in a quick test here. Would you provide the test case that makes you think it's doing otherwise? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
Bruce, Richard, > Triggers are mostly for testing/modifying the row being > inserted/updated, while rules are better for affecting other rows or > other tables. Hmmm. Thought that there were also some other criteria: 1) Rules can't use indexes to do their processing, so Rules which query large secondary tables can be a bad idea (maybe this has changed?) 2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining Read/Write views. 3) There are no AFTER Rules, making, for example, a rule with a table check on the new data impractical, so you'd want to use Triggers or Constraints etc. There are, IMHO, some things Rules are better for, and some things Triggers are better for. I tend to use all Triggers except for updatable views, simply because using a mix of Rules and Triggers can be very hard to keep track of, but YMMV. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Rules/Trigges Trade-offs
Josh Berkus wrote: > > Bruce, Richard, > > > Triggers are mostly for testing/modifying the row being > > inserted/updated, while rules are better for affecting other rows or > > other tables. > > Hmmm. Thought that there were also some other criteria: > > 1) Rules can't use indexes to do their processing, so Rules which query large > secondary tables can be a bad idea (maybe this has changed?) I don't think this is true. Rewrite is before optimizer so it should be optimized just the same. > > 2) Only Rules can "DO INSTEAD"; thus, only Rules are good for defining > Read/Write views. True. > 3) There are no AFTER Rules, making, for example, a rule with a table check on > the new data impractical, so you'd want to use Triggers or Constraints We have changed ordering in 7.3 where I think INSERT rules are _after_ the insert. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Rules/Trigges Trade-offs
Bruce, > > 1) Rules can't use indexes to do their processing, so Rules which query large > > secondary tables can be a bad idea (maybe this has changed?) > > I don't think this is true. Rewrite is before optimizer so it should be > optimized just the same. I was speaking if the Rule has to do a lookup on a 2nd table in the course of its processing; it used to be that such a lookup could not use indexes because Rules are pre-optimizer. This could have changed since 7.1, though. > > 3) There are no AFTER Rules, making, for example, a rule with a table check on > > the new data impractical, so you'd want to use Triggers or Constraints > > We have changed ordering in 7.3 where I think INSERT rules are _after_ > the insert. How would that work? What if I want to reject the insert? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
Josh Berkus wrote: > > Bruce, > > > > 1) Rules can't use indexes to do their processing, so Rules which query > large > > > secondary tables can be a bad idea (maybe this has changed?) > > > > I don't think this is true. Rewrite is before optimizer so it should be > > optimized just the same. > > I was speaking if the Rule has to do a lookup on a 2nd table in the course of > its processing; it used to be that such a lookup could not use indexes > because Rules are pre-optimizer. This could have changed since 7.1, though. As I remember, the RULE is processed just like an ordinary query. I wasn't aware it was looking up anything. > > > 3) There are no AFTER Rules, making, for example, a rule with a table > check on > > > the new data impractical, so you'd want to use Triggers or Constraints > > > > We have changed ordering in 7.3 where I think INSERT rules are _after_ > > the insert. > > How would that work? What if I want to reject the insert? Uh, good question. I can't remember now what we re-ordered in 7.3, but I thought it had to do with rules. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Rules/Trigges Trade-offs
Bruce Momjian wrote: > > > > 3) There are no AFTER Rules, making, for example, a rule with a table > > check on > > > > the new data impractical, so you'd want to use Triggers or Constraints > > > > > > We have changed ordering in 7.3 where I think INSERT rules are _after_ > > > the insert. > > > > How would that work? What if I want to reject the insert? > > Uh, good question. I can't remember now what we re-ordered in 7.3, but > I thought it had to do with rules. OK, I found what I wanted. We made rules fire in alphabetical order in 7.3, and INSTEAD rules are not reordered to the font/back --- they are treated just like any other rule and executed alphabetically. I thought we had some problem with rules or triggers and OLD/NEW handling, but I can't see any mention of that, and it must have been in an older release. The question of firing order is a good one and one that isn't asked very often. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
