Re: [SQL] generating date sequences

Hi! >> Is there a simple way to generate sequences of dates like the following? > Sure: > test=# select '2008-07-03'::date + s * '1day'::interval from > generate_Series(1,10) s; Thanks! Thats what I was searching for. You saved my day from manually adding missing dates in a huge excel sheet! S

[SQL] generating date sequences

Hi! Is there a simple way to generate sequences of dates like the following? "2008-07-03 00:00:00" "2008-07-04 00:00:00" "2008-07-05 00:00:00" "2008-07-06 00:00:00" I'd like to join a table to aggregate the number of items for each day (each item has a timestamp).

[SQL] fast insert-if-key-not-already-there

Hi! I have to do much inserts into a database where the key most often is already there. My current approach is to query for the key (ip-address), and if the result is null I do the insert. For every IP-Address I need the ip_addr_id from the same table. Something like this: CREATE TABLE ip_addr

[SQL] index for group by

Hi, is there a way to speedup "group by" queries with an index? In particular if I have a table like this: CREATE TABLE data ( id1 integer, id2 integer, somedata character varying, ts timestamp with time zone ); where continously data is logged about "id1" and "id2" into "somedata",

Re: [SQL] order by when using cursors

Pavel Stehule wrote: >> it's known problem - column and variable names collision, so when you >> use any SQL statement inside procedure you have to be carefully about >> using variable names. Oh, I didn't took notice of that. Now knowing it is not a bug and how it works, it makes things much easi

[SQL] order by when using cursors

Hi! I did some experiments with cursors and found that my data doesn't get sorted by the "order by"-statement. Here is what I did: CREATE TABLE ta ( a integer NOT NULL, b integer NOT NULL ); insert into ta values(3,1); insert into ta values(1,2); insert into ta values(4,3

Re: [SQL] using calculated column in where-clause

Andreas Kretschmer wrote: >> Do I have to repeat the calculation (which might be even more complex > yes. Short and pregnant! :-) Thanks! Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] using calculated column in where-clause

Hi! I'd like to do some calculation with values from the table, show them a new column and use the values in a where-clause. Something like this select a, b , a*b as c from ta where c=2; But postgresql complains, that column "c" does not exist. Do I have to repeat the calculation (which might b

Re: [SQL] returning results from an update for joining other tables

Hi! >> >> What I tried was something like this, which gave me a syntax error: >> >> >> >> SELECT * FROM >> >> ( UPDATE ta >> >> SET process_node='nodename' >> >> WHERE a>10 AND process_node is null >> >> RETURNING * >> >> ) AS ta >> >> JOIN someothertable ON ... > > > > It's a know limitation

Re: [SQL] returning results from an update for joining other tables

Hi! >> What I tried was something like this, which gave me a syntax error: >> >> SELECT * FROM >> ( UPDATE ta >> SET process_node='nodename' >> WHERE a>10 AND process_node is null >> RETURNING * >> ) AS ta >> JOIN someothertable ON ... > > It's a know limitation, see <[EMAIL PROTECTE

[SQL] returning results from an update for joining other tables

Hi! I have a table containing data and a column which holds information on which compute-node processes the data. In a given interval I'd like to request some data from this table and mark these returned rows by setting the "process_node" column to the node-name, which asked for data. There may al

[SQL] merge timestamps to intervals

Hi! I have a table where I repeatingly log the status of some service, which looks something like this: < timestamp, status > Now, everytime my service is up I save the timestamp and a status of "up", if it's down I save the timestamp with "down", eg: 10:13 up 10:14 u