[SQL] ORDER BY CASE ...
Am I misusing the ORDER BY with CASE, or, what? :) I have a table, messages, half dozen of columns, exposing here just three of them: pulitzer2=# select id, "from", receiving_time from messages where service_id = 20 order by case when 5=5 then 2 else 3 end desc limit 5; id | from | receiving_time +---+ 869585 | +385989095824 | 2005-12-08 16:04:23+01 816579 | +385915912312 | 2005-11-23 17:51:06+01 816595 | +38598539263 | 2005-11-23 17:58:21+01 816594 | +385915929232 | 2005-11-23 17:57:30+01 816589 | +385912538567 | 2005-11-23 17:54:32+01 (5 rows) pulitzer2=# select id, "from", receiving_time from messages where service_id = 20 order by case when 5=6 then 2 else 3 end desc limit 5; id | from | receiving_time +---+ 869585 | +385989095824 | 2005-12-08 16:04:23+01 816579 | +385915912312 | 2005-11-23 17:51:06+01 816595 | +38598539263 | 2005-11-23 17:58:21+01 816594 | +385915929232 | 2005-11-23 17:57:30+01 816589 | +385912538567 | 2005-11-23 17:54:32+01 (5 rows) I tought I'd get differently sorted data, since in the first query I said 5=5, and in second I said 5=6. Is this a right way to use CASE on ORDER BY, or? I need to sord the data in the function depending on the function parametar. If it's true, randomize the sort, if not, sort by receiving_time, newest first. So, can I do it with ORDER BY CASE ... END, or do I need to have two querries, and then first check for the value of the parametar, and then, according to that value, call the SELECTs wich sort randomly, or by receiving_time. Mario P.S. The postgres is 8.1.2. -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 1: 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 CASE ...
+-le 13/02/2006 16:35 +0100, Mario Splivalo a dit : | Am I misusing the ORDER BY with CASE, or, what? :) | | I have a table, messages, half dozen of columns, exposing here just | three of them: | | pulitzer2=# select id, "from", receiving_time from messages where | service_id = 20 order by case when 5=5 then 2 else 3 end desc limit 5; |id | from | receiving_time | +---+ | 869585 | +385989095824 | 2005-12-08 16:04:23+01 | 816579 | +385915912312 | 2005-11-23 17:51:06+01 | 816595 | +38598539263 | 2005-11-23 17:58:21+01 | 816594 | +385915929232 | 2005-11-23 17:57:30+01 | 816589 | +385912538567 | 2005-11-23 17:54:32+01 | (5 rows) | | | pulitzer2=# select id, "from", receiving_time from messages where | service_id = 20 order by case when 5=6 then 2 else 3 end desc limit 5; |id | from | receiving_time | +---+ | 869585 | +385989095824 | 2005-12-08 16:04:23+01 | 816579 | +385915912312 | 2005-11-23 17:51:06+01 | 816595 | +38598539263 | 2005-11-23 17:58:21+01 | 816594 | +385915929232 | 2005-11-23 17:57:30+01 | 816589 | +385912538567 | 2005-11-23 17:54:32+01 | (5 rows) | | | I tought I'd get differently sorted data, since in the first query I | said 5=5, and in second I said 5=6. Well, no, in the first, the result of the CASE is 2, and in the second 3, it means that for every line, it'll sort using "2" as value for the first, and "3" for the second query. -- Mathieu Arnold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ORDER BY CASE ...
On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote: > | > | > | I tought I'd get differently sorted data, since in the first query I > | said 5=5, and in second I said 5=6. > > Well, no, in the first, the result of the CASE is 2, and in the second 3, it > means that for every line, it'll sort using "2" as value for the first, and > "3" for the second query. > Yes, I realized just a second ago that when ORDER BY is CASED, numbers aren't the column numbers, but the integers itself. It works like this: ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE receiving_time::varchar) DESC. Is there a way to have DESC/ASC inside of a CASE? Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ORDER BY CASE ...
On Mon, Feb 13, 2006 at 04:35:30PM +0100, Mario Splivalo wrote: > Am I misusing the ORDER BY with CASE, or, what? :) > > I have a table, messages, half dozen of columns, exposing here just > three of them: > > pulitzer2=# select id, "from", receiving_time from messages where > service_id = 20 order by case when 5=5 then 2 else 3 end desc limit 5; I'm not sure what you are trying to do here, but it seems that an order by statement should at least contain something that is part of the resultrow. "case when 5=5 then 2 else 3 end desc limit 5" does not contain any column to sort on. So I think it will evaluate to some constant value and not sorting is really done -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ORDER BY CASE ...
+-le 13/02/2006 16:47 +0100, Mario Splivalo a dit : | On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote: |> | |> | I tought I'd get differently sorted data, since in the first query I |> | said 5=5, and in second I said 5=6. |> |> Well, no, in the first, the result of the CASE is 2, and in the second 3, |> it means that for every line, it'll sort using "2" as value for the first, |> and "3" for the second query. |> | | Yes, I realized just a second ago that when ORDER BY is CASED, numbers | aren't the column numbers, but the integers itself. | | It works like this: | | ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE | receiving_time::varchar) DESC. | | Is there a way to have DESC/ASC inside of a CASE? | | Mario No, you don't understand, you should do something like : case when foo = bar then from else receiving_time desc end -- Mathieu Arnold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Very slow updates when using IN syntax subselect
Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > >> Bryce Nesbitt wrote: >> >>> They occur in finite time. That's good, thanks. But jeeze, can't >>> postgres figure this out for itself? >>> >> I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan >> before each query to figure out the total size of the involved tables. >> > > It's also less than polite to complain about the behavior of > two-year-old releases, without making any effort to ascertain > whether more-current versions are smarter. > Sorry to offend. Are current versions smarter? The DB I was working with still had row counts of 1 after tens of thousands of records had been added. With new versions, must the DB still be VACUUMED, or is that a bit more automatic? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE VIEW form stored in database?
- Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> If you keep your definition in a script file, you can copy the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. It's still a pain. If I have two dozen views, it takes too much time :) You also have the option of loading script files in the Execute Arbitray SQL Queries window (File > Open, etc.). Thus you could put all your views into one script file, load the file, and then execute the query. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problems with distinct
Any idea why this works: SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2 WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WHERE child_id = g2.id) AND g2.id IN(1,2,109,105, 112); And not this: SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_group g, onp_group g2 WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WHERE child_id = g2.id) AND g2.id IN(1,2,109,105, 112); The *only* difference is that the distinct-clause changed place... -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 1: 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] Problems with distinct
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > Any idea why this works: > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2 > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children > WHERE > child_id = g2.id) > AND g2.id IN(1,2,109,105, 112); > And not this: > SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_group g, onp_group g2 > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children > WHERE > child_id = g2.id) > AND g2.id IN(1,2,109,105, 112); DISTINCT is not a function, it's a modifier attached to SELECT. The parentheses in your first example are a no-op. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Problems with distinct
Andreas Joseph Krogh wrote: > Any idea why this works: > > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, > onp_group g2 > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM > onp_group_children WHERE > child_id = g2.id) > AND g2.id IN(1,2,109,105, 112); > > And not this: > > SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_group g, > onp_group g2 > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM > onp_group_children WHERE > child_id = g2.id) > AND g2.id IN(1,2,109,105, 112); Distinct is an SQL keyword, not a function. The former is exactly equivalent to SELECT DISTINCT g.groupname, g.id, FROM except that the first field is inside a (trivial) expression that makes it look like a function call. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problems with distinct
On Monday 13 February 2006 20:22, Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > Any idea why this works: > > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2 > > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children > > WHERE child_id = g2.id) > > AND g2.id IN(1,2,109,105, 112); > > > > And not this: > > > > SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_group g, onp_group g2 > > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children > > WHERE child_id = g2.id) > > AND g2.id IN(1,2,109,105, 112); > > DISTINCT is not a function, it's a modifier attached to SELECT. The > parentheses in your first example are a no-op. Thanks! Is there any better(faster) way to achieve the same results based on these schemas: CREATE TABLE onp_group( id integer PRIMARY KEY REFERENCES onp_entity(id) on delete cascade, p_id integer REFERENCES onp_group(id) on delete cascade, groupname varchar NOT NULL unique ); CREATE TABLE onp_group_children( group_id integer NOT NULL REFERENCES onp_group(id), child_id integer NOT NULL REFERENCES onp_group(id), UNIQUE(group_id, child_id) ); select * from onp_group; id | p_id | groupname -+--+ 1 | | SuperAdmin 2 | | ONPAdmin 101 | | Ansatte 102 | 101 | Ledere 103 | 101 | IT 104 | 101 | Finans 105 | 101 | Backoffice 106 | 101 | Kunder 107 | 102 | Styre 108 | 102 | Personal 109 | 103 | Drift 110 | 103 | Strategi 111 | 103 | Software 112 | 103 | Hardware select * from onp_group_children; group_id | child_id --+-- 101 | 102 101 | 103 101 | 104 101 | 105 101 | 106 102 | 107 101 | 107 102 | 108 101 | 108 103 | 109 101 | 109 103 | 110 101 | 110 103 | 111 101 | 111 103 | 112 101 | 112 The results I'm looking for is this: SELECT distinct g.groupname, g.id, g.p_id FROM onp_group g, onp_group g2 WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WHERE child_id = g2.id) AND g2.id IN(1,2,109,105, 112); groupname | id | p_id +-+-- Ansatte| 101 | Backoffice | 105 | 101 Drift | 109 | 103 Hardware | 112 | 103 IT | 103 | 101 ONPAdmin | 2 | SuperAdmin | 1 | Which is "give me all groups, including parents, for all "id" in the given list(the IN-clause). -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ORDER BY CASE ...
On Mon, 2006-02-13 at 17:10 +0100, Mathieu Arnold wrote: > | It works like this: > | > | ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE > | receiving_time::varchar) DESC. > | > | Is there a way to have DESC/ASC inside of a CASE? > | > | Mario > > No, you don't understand, you should do something like : > > case when foo = bar then from else receiving_time desc end > Can't do so, because receiving_time is timestamptz, and "from" is varchar. There: pulitzer2=# select id, "from", receiving_time from messages order by case when 2=3 then "from" else receiving_time end desc limit 5; ERROR: CASE types timestamp with time zone and character varying cannot be matched I need to explicitly cast receiving_time into varchar. What I would like to include ASC/DESC into CASE, but I guess that's not possible. Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] group by complications
select l.lid,l.fs,max(h.obstime) from location as l inner join height as h on h.lid = l.lid where l.fs > 0.0 group by l.lid,l.fs; The above query works as expected in that is fetches the lid, fs and time of the latest observation in the height table (for the corresponding lid), but I also want to fetch (i.e., add to the select list) the corresponding reading (h.obsvalue) which occurs at max(h.obstime). I'm having trouble formulating the correct SQL syntax to pull out the l.lid, l.fs, and the most recent h.obvalue (with or without the time that it occurred). Logistically, I want to do something like this: select l.lid,l.fs,most_recent(h.obsvalue) from location as l inner join height as h on h.lid = l.lid where l.fs > 0.0 group by l.lid,l.fs; Can someone offer hints, please? Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Slow update SQL
I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';
The query just never finishes (even 1 hour later). The colum key100 is indexed, and I'm setting the value of this
column from other columns. Why is this so slow?
Re: [SQL] Slow update SQL
On Mon, Feb 13, 2006 at 05:48:45PM -0800, Ken Hill wrote:
> When I try to analyze the query plan with:
>
> EXPLAIN ANALYZE
> UPDATE ncccr10
> SET key = facilityno||'-'||
> lastname||'-'||
> sex||'-'||
> ssno||'-'||
> birthdate||'-'||
> primarysit||'-'||
> dxdate||'-'||
> morphology3
> WHERE date_part('year',dxdate) > '2000';
>
> The query just never finishes (even 1 hour later). The colum key100 is
> indexed, and I'm setting the value of this
> column from other columns. Why is this so slow?
If EXPLAIN ANALYZE is taking too long then could we at least see
the EXPLAIN output? How many rows does the condition match?
SELECT count(*) FROM ncccr10 WHERE date_part('year',dxdate) > '2000';
Do you have an expression index on date_part('year',dxdate)? Does
the table have any triggers or rules? Have you queried pg_locks
to see if the update is blocked on an ungranted lock?
Do other tables have foreign key references to ncccr10? If so then
you might need indexes on the referring columns.
What version of PostgreSQL are you running?
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] group by complications
--- Mark Fenbers <[EMAIL PROTECTED]> wrote: > select l.lid,l.fs,max(h.obstime) from location as l > inner join height as h on h.lid = l.lid > where l.fs > 0.0 > group by l.lid,l.fs; > > The above query works as expected in that is fetches the lid, fs and > time of the latest observation in the height table (for the > corresponding lid), but I also want to fetch (i.e., add to the select > list) the corresponding reading (h.obsvalue) which occurs at > max(h.obstime). I'm having trouble formulating the correct SQL > syntax > to pull out the l.lid, l.fs, and the most recent h.obvalue (with or > without the time that it occurred). > > Logistically, I want to do something like this: > > select l.lid,l.fs,most_recent(h.obsvalue) from location as l > inner join height as h on h.lid = l.lid > where l.fs > 0.0 > group by l.lid,l.fs; > use your original query as part of the from clause, then add columns to it through a subquery or a join. try something like this: select q1.*, (select obsvalue from height where lid=q1.lid and obstime=q1.obstime) as obsvalue from (select l.lid,l.fs,max(h.obstime) as obstime1 from location as l inner join height as h on h.lid = l.lid where l.fs > 0.0 group by l.lid,l.fs ) q1; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Slow update SQL
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
> count
>
> 199209
> (1 row)
You're updating about a third of the table; an expression index on
date_part probably wouldn't help because the planner is likely to
stick with a sequential scan for such a large update. Even if it
did help it's likely to be a small fraction of the total time.
The table definition you sent me showed nine indexes. You might
see a substantial performance improvement by dropping all the
indexes, doing the update, then creating the indexes again (don't
forget to vacuum and analyze the table after the update). However,
dropping the indexes has obvious implications for other queries so
you might need to do the update at a time when that doesn't matter.
> > Have you queried pg_locks
> > to see if the update is blocked on an ungranted lock?
>
> I don't know what that is. How do I query pg_locks?
SELECT * FROM pg_locks;
http://www.postgresql.org/docs/7.4/static/monitoring-locks.html
> > What version of PostgreSQL are you running?
>
> 7.4.8. Thank you for your help.
Newer versions generally perform better; consider upgrading to 8.0
or 8.1 if possible.
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
