Re: [SQL] Use select and update together
On Mon, Sep 12, 2011 at 3:36 AM, Boris wrote:
>
> SELECT (
> UPDATE tbl SET val = 1
> WHERE KEY = any('{0,1,2,3,4,5}'::int[])
> returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
Just lose the select (); part.
update tbl set val = 1 where condition = true returning *;
That works just fine on 8.4. You can also specify individual columns in the
returning clause. The postgresql documentation covers the topic.
http://www.postgresql.org/docs/9.0/static/sql-update.html
It doesn't appear to work in a subquery, so you can't join to it (though you
can use a join within the update clause and then return the columns you need
from a joined table, I suspect). Simply returning 1 or more columns from
updated rows works just fine, though.
[SQL] Partition over a sliding date window
Hi, I have a simple table with 3 fields: date, value and username, which can hold identical rows. What I am trying to do is to have for each date the count of distinct username for the 30 previous days. I was hoping to get this with a partition, as in the following non-working exemple SELECT t.date , t.value , COUNT(DISTINCT t.username) OVER (PARTITION date BETWEEN t.date - INTERVAL '29 days' and t.date) FROM table t GROUP BY date , value , username ; There are many issues with this query: - distinct not implemented for window function - COUNT () OVER is not seen as an aggregate function, I thus need to add username in the GROUP BY clause, which leads to wrong result - I am not convinced that the date BETWEEN is valid either, but the other issues prevent me to check this. Is there a way to do what I am looking for with partitions, or should I just give up and use 'usual' sql? Thanks, Guillaume -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Window function sort order help
Having trouble getting a window function to sort correctly. Given this data consumer_id | move_date | history_timestamp -++ 12345| 2008-01-05 | 2007-12-11 06:02:26.842171 12345| 2008-02-29 | 2008-02-05 07:22:38.04067 12345| 2008-02-29 | 2008-07-11 09:03:42.44044 23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <-- 23456| 2009-01-28 | 2008-11-14 01:57:40.264335 23456| 2009-01-01 | 2008-12-04 17:14:20.27 <-- 23456| 2009-01-01 | 2008-12-31 00:33:37.204968 23456| 2009-01-01 | 2011-06-08 04:16:41.646521 34567| 2010-05-07 | 2010-06-08 05:14:43.842172 I'm trying to get the timestamp when the consumer last changed their move_date. (Notice consumer_id 23456 set their move_date to 2009-01-01 then changed and then changed it back. In the end, I want the timestamp from when they changed it to 2009-01-01 the second time.) My thought was to do an intermediary step to find the timestamp for each time it switched. From there I can grab the max(timestamp) for each consumer. Here's my query. select ch.consumer_id, ch.move_date,ch.history_timestamp, min(ch.history_timestamp) over w as start_time from consumer_hist ch window w as (partition by ch.consumer_id, ch.move_date order by ch.consumer_id,ch.history_timestamp asc) order by ch.consumer_id , ch.history_timestamp asc I expect the following result consumer_id | move_date | history_timestamp | start_time -+++ 12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171 12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067 12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067 23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <-- 23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335 23456 | 2009-01-01 | 2008-12-04 17:14:20.27 | 2008-12-04 17:14:20.27 <-- 23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-12-04 17:14:20.27 23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-12-04 17:14:20.27 34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172 But the windowing function appears to be doing the partitioning first then the order by. So instead, I'm getting consumer_id | move_date | history_timestamp | start_time -+++ 12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11 06:02:26.842171 12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05 07:22:38.04067 12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05 07:22:38.04067 23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12 07:33:32.656658 <-- 23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14 01:57:40.264335 23456 | 2009-01-01 | 2008-12-04 17:14:20.27 | 2008-11-12 07:33:32.656658 <-- 23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-11-12 07:33:32.656658 23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-11-12 07:33:32.656658 34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08 05:14:43.842172 Any suggestions to get the order by to occur first then the partition by or maybe there another approach that I could use? Dianna
