[SQL] Methods declaration
Hello, I've just started working with ordbms. I'm searching for differences between postgresql and oracle... Btw, I've found it's possible to declare a method for a specific type in oracle...so if I create a type MyType, I can associate it also a method MyMethodusing the costructors: CREATE TYPE MyType AS OBJECT ( ... ... ... MEMBER FUNCTION MyMethod(parameterName parameterType) RETURN ReturnType); Is it possible to do the same with postgreSql? I've found the CREATE FUNCTION command...but it seems it creates global functions, not associated to a particular type... Thanks
[SQL] diff b/w varchar(N) & text
Hi., Is there any big difference between selecting the data type as varchar(N) and text. What is the difference of it when seeing from the performance side ? I know that, when we know that the number of character will not exceed N, we can use the varchar as datatype. If we dont know we can use this text as datatype. But other than this anyother difference is there, and particularly in performance, and also if the number of rows crosses 100 thousand... Any other views ? Any advise on this is appreciated. Thanks & Regards SathiyaMoorthy.
Re: [SQL] Exclude fields from SELECT command
In what way it is devil Clarifications. .. > I know better solution. Use only thin tables. Wide tables are devil. :) >
Re: [SQL] diff b/w varchar(N) & text
sathiya psql wrote: Is there any big difference between selecting the data type as varchar(N) and text. What is the difference of it when seeing from the performance side ? Check the manual. There is no difference! http://www.postgresql.org/docs/8.3/static/datatype-character.html -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
Dear all, I have a table that records User Login Sessions with two timestamp fields. Basically Start of Session and End of a Session (start_ts and end_ts). Each row in the table identifies a session which a customer has used. Data from the table (session): - customer_id | log_session_id | start_ts | end_ts -+-++ 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182 100608 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.947509 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 13:56:58.394577 The requirement is as follows, I have to find out how many User Sessions that were present in any given "1 HOUR TIME PERIOD". A single User Session can span across many days. Example: start_ts | end_ts 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218 - Let me explain a scenario, I have to find out the number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. If I have to find the number of sessions present at any instant time say '07/01/2009 11:25:25', i can easily find out using the following Query, select count(log_session_id) from session where '07/01/2009 11:25:25' between start_ts and end_ts ; But, I have to find the number of sessions present during the "HOUR INTERVAL" '07/01/2009 11:00:00' to '07/01/2009 11:59:59'. I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either. I feel this is a general requirement and this topic should have already been discussed. Could someone help me solve this please ? Any lead would do, like some special postgres-function or any other means. Many Thanks, ../rssrik Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/
Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
Srikanth wrote: > Dear all, > > I have a table that records User Login Sessions with two timestamp fields. > Basically Start of Session and End of a Session (start_ts and end_ts). Each > row in the table identifies a session which a customer has used. > > Data from the table (session): > - > customer_id | log_session_id | start_ts | end_ts > -+-++ > 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 > 16:58:52.665327 > 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 > 22:59:02.770218 > 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 > 14:58:59.989182 > 100608 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 > 12:07:15.947509 > 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 > 13:56:58.394577 > > > The requirement is as follows, > > I have to find out how many User Sessions that were present in any given "1 > HOUR TIME PERIOD". A single User Session can span across many days. SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (, ); > I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either. I'd have thought OVERLAPS would be mentioned in the date/time handling sections of the manual. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Methods declaration
> Is it possible to do the same with postgreSql? No, but you can overload function names in Postgres, which can provide a lot of the same notational conveniences. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Exclude fields from SELECT command
At 05:20 PM 3/16/2009, [email protected] wrote: In-Reply-To: <1992170861895942...@unknownmsgid> References: <1992170861895942...@unknownmsgid> Date: Mon, 16 Mar 2009 22:45:54 +0100 Message-ID: <[email protected]> Subject: Re: Exclude fields from SELECT command From: Pavel Stehule To: Charles Tam Cc: [email protected] 2009/3/16 Charles Tam : > Hi Everybody > > > > Iâve a table with 35 fields and would like to perform a SELECT command > without specifying every single field. > > As such, Iâve use the SELECT * command. Is there an approach to exclude 5 > fields from being returned? > > > hello no, there are no way regards Pavel Stehule I think Pavel is right for 99% of the cases. But there is a "cure that is worse than the disease." You could select all columns from a bunch of tables without knowing what the column names were, excepting N columns, by iterating through the info schema data and building a SQL select appropriately (sql meta-programming I guess you would call it). But it's a real chore to do manually. If you have this need for some programmatic purpose (where some initial investment in effort will pay future dividends), then check out the info schema options: http://developer.postgresql.org/pgdocs/postgres/infoschema-columns.html In your case, I think you'd be looking for five values of "table_name" and then looking at all the "column_name" fields, building your column list, excepting the column_names you wish to exclude.. Best, Steve -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join
I'm trying to get rid of the side effect of the left join catalog_promosimple That means I'm expecting at least one row no matter if there is an entry in left join catalog_promosimpleitem but I don't want "duplicates" from catalog_promosimple create table catalog_promosimple ( PromoSimpleID int primary key, IsPromo boolean not null default true, Discount numeric(19,4) not null default 0 ); create table catalog_promosimpleitem ( PromoSimpleID int references catalog_promosimple (PromoSimpleID), ItemID int references catalog_items (ItemID) ); so eg. I've 1,true 2,false 1,1,10 1,1,20 2,1,0 2,1,5 when I'm looking for ItemID=1 the query should return: ItemID,Discount 1,20 and skip the entries with IsPromo=false If I've just 2,false 2,1,0 2,1,5 the query should return: ItemID,Discount 2,null If there are no items at all in catalog_promosimpleitem (but there is a corresponding one in catalog_items eg. ItemID=5) the query should return: ItemID,Discount 5,null select i.ItemID as _ItemID, i.Code as _Code, i.ISBN as _ISBN, i.CodeAlt as _CodeAlt, i.Name as _Name, i.ListPrice as _ListPrice, DiscountedPrice( i.qty, i.StatusID, max(pi.Discount), p.Percent, p.OnListPrice, p.Vendible, p.OnStock,i.ListPrice, ip.Price ) as _DiscountedPrice, i.valIva as _Tax, i.StatusID as _StatusID, i.qty as _qty, b.Name as _Brands, i.BrandID as _BrandID, i.Authors as _Authors, b.Delivery as _Delivery, extract(year from i.dataPub) as _YearPub, s.FamID as _FamID, st.Name as _SName from catalog_items i join catalog_categoryitem s on i.ItemID=s.ItemID join catalog_item_status st on st.StatusID=i.StatusID left join catalog_brands b on b.BrandID=i.BrandID left join catalog_itemprice ip on ip.ItemID=i.ItemID and ip.DiscountClass=10 left join catalog_promosimpleitem pi on pi.ItemID=i.ItemID left join catalog_promosimple p on pi.PromoSimpleID=p.PromoSimpleID and p.PromoStart=now() and p.IsPromo=true where i.ItemID=102020 group by i.ItemID, i.Code, i.ISBN, i.CodeAlt, i.Name, i.ListPrice, i.qty, i.StatusID, p.Percent, p.OnListPrice, p.Vendible, p.OnStock, ip.Price, i.valIva, b.Name, i.BrandID, i.Authors, i.dataPub, s.FamID, st.Name, b.Delivery -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do I optimize this?
Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; The queries are run on the same . Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do I optimize this?
I made a mistake in the queries: They should be update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 1) as b where tkey = ; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 2) as b where tkey = ; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 10) as b where tkey = ; Thanks Wei On 03/17/2009 05:43 PM, Wei Weng wrote: Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = ) as b where tkey = ; The queries are run on the same . Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
