[SQL] Help on a complex query (avg data for day of the week)

Hello, I have a table containing a timestamp and data usage fields (among others). This table stores amounts of data usage and the times then the data was used, eg: time | data +-- 2005-03-26 09:32:43+11 | 162 I want to form a query that retu

Re: [SQL] [GENERAL] Question on indexes

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html vishOn 12/19/05, Emil Rachovsky <[EMAIL PROTECTED]> wrote: Hi,Can anyone show me a simple way of creating an indexin PostGre like that:create index indName on someTable(som

[SQL] Problem obtaining MAX values FROM TABLE

I am having a problem with a query, I have a view which produces something like this: b_idcompany_name product_count product_type 29"company 1" 1 "a" 29"company 2" 1 "b" 29

[SQL] Commiting after certain no of rows have been deleted

Hi,   In my application I m deleteing large no of rows from table based on certain condition. This takes lot of time and if sometimes my application fails it starts all over again...since the coomit is done at the end of transactions. Is there a way i can do commit when certain no o

[SQL] Help me do a LOOP

 I need to test each record of a cursor for some requirements, something like thisDECLARE xpvnr varchar(50);  xdata date;  xcod int;  xagentd varchar(3);  xid bigint;  xserie varchar(4);  xnr varchar(7);  xsocasig varchar(3); DECLARE myCursor CURSOR FOR

Re: [SQL] Does VACUUM reorder tables on clustered indices

On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote: > Hi, > > Utilize CLUSTER; (after vacuum) to reorder the data. Why would you vacuum when cluster is just going to wipe out the dead tuples anyway? > >>Note that while reordering, CLUSTER also gets rid of dead tuples, so if > >>yo

[SQL] unsubscribe

unsubscribe ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Does VACUUM reorder tables on clustered indices

Hi, Utilize CLUSTER; (after vacuum) to reorder the data. Regards - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Martin Marques" ; "frank church" <[EMAIL PROTECTED]>; Sent: Tuesday, December 20, 2005 10:41 PM Subject: Re: [SQL] Does VACUUM reorder tables on clust

Re: [SQL] Performance of a view

John McCawley <[EMAIL PROTECTED]> writes: > I have a view which is defined as follows: > SELECT tbl_claim.claim_id, count(tbl_invoice.invoice_id) AS count, > min(tbl_invoice.invoicedate) AS invoicedate > FROM tbl_claim > LEFT JOIN tbl_invoice ON tbl_claim.claim_id = tbl_invoice.claim_id AND

Re: [SQL] Does VACUUM reorder tables on clustered indices

On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote: > Martin Marques escribi?: > > On Sun, 18 Dec 2005, frank church wrote: > > > > > > > >Does VACUUMing reorder tables on clustered indices or is it only the > > >CLUSTER > > >command that can do that? > > > > Cluster does that. Vacuu

Re: [SQL] Sub-query as function argument

Michael Burke <[EMAIL PROTECTED]> writes: > Is it possible to execute a SELECT query as an argument to a function? > SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio); You need parentheses around the sub-SELECT. SELECT my_func('Sample', NULL, (SELECT MIN(year) FROM audio)); This is

Re: [SQL] Querying date_time for date only ?

Michael Burke <[EMAIL PROTECTED]> writes: > On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: >> I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my >> table. I want to also find something just for a particular day regardless >> of the time. > You can try, > SELECT field::d

Re: [SQL] Sub-query as function argument

On December 20, 2005 10:52 am, Jaime Casanova wrote: > have you tried? Yes: => SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio); ERROR: syntax error at or near "SELECT" at character 32 PostgreSQL 7.4.9, myfunc is pl/pgsql. Just found a working method, though: => SELECT my_func('Sam

Re: [SQL] Sub-query as function argument

On 12/20/05, Michael Burke <[EMAIL PROTECTED]> wrote: > Is it possible to execute a SELECT query as an argument to a function? > have you tried? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9

[SQL] Sub-query as function argument

Is it possible to execute a SELECT query as an argument to a function? Example: SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio); In my particular case, my_func inserts columns into another table; I wish to use values from another table as the arguments. The interior SELECT will re

Re: [SQL] Querying date_time for date only ?

On Tuesday 20 December 2005 15:19, Michael Burke wrote: > On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: > > Hello List, > > > > I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my > > table. I want to also find something just for a particular day regardless > > of the ti

Re: [SQL] Querying date_time for date only ?

On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote: > Hello List, > > I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my > table. I want to also find something just for a particular day regardless > of the time. > > (Pg)SQL way to do this ? You can try, SELECT field::date F

[SQL] Querying date_time for date only ?

Hello List, I have a time stamp without time zone field, -MM-DD hh:mm:ss, in my table. I want to also find something just for a particular day regardless of the time. (Pg)SQL way to do this ? TIA, Aarni -- -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core

Re: [SQL] Rule causes baffling error

Ken Winter wrote: Richard ~ Let me zoom out for a moment, for the bigger picture. As you have inferred, what I'm trying to do is develop a history-preserving table ("my_data" in the example that started this thread). *Most* user programs would see and manipulate this table as if it contained o