[SQL] Mag Gam
http://faribradytattoo.dk/hhmqna/sninwzk.umbobbqmel Mag Gam 7/21/2013 7:22:12 AM -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Curious about wide tables.
Any chance this could be a view? On Sun, Apr 27, 2008 at 12:06 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > On Sun, Apr 27, 2008 at 9:01 AM, Jean-David Beyer > <[EMAIL PROTECTED]> wrote: > > In another thread, the O.P. had a question about a large table with over > 100 > > columns. Is this usual? Whenever I make a database, which is not often, > it > > ends up with tables that rarely have over to columns, and usually less > than > > that. When normalized, my tables rarely get very wide. > > Yes, even in several well-normalized schemas I've seen tables with > over 250 columns. > > > Without criticising the O.P., since I know nothing about his > application, I > > am curious how it comes about that such a wide table is justified. > > The few applications I've seen with large tables were an insurance > system, an manufacturing system, and a sensor-recording system (which > was more optimal to store as an attribute-per-instance-of-time than a > separate tuple containing the time, sensor, and value). > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] > Edison, NJ 08837 | http://www.enterprisedb.com/ > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Reg:-How to make relationship more one table in PgSQL
http://www.w3schools.com/Sql/sql_join.asp hope this helps On Tue, Apr 29, 2008 at 7:21 AM, <[EMAIL PROTECTED]> wrote: > Hi Venkat, > > > > > > What kind of relation you want to create? Is it to do with the creation of > dependencies or something else? > > > > > > Regards, > > Kapil > -- > > *From:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *On Behalf Of *venkat > *Sent:* Tuesday, April 29, 2008 4:41 PM > *To:* [email protected] > *Subject:* [SQL] Reg:-How to make relationship more one table in PgSQL > > > > Dear All, > > I am new to PqSQL. I have one problem.that is i want to make relation 4 > tables.How to do in postgresql.Please any can help me. > I am waiting for your great response. > > Advanced Thanks, > > Regards > Venkat > > * Please do not print this email unless it is absolutely necessary. * > > The information contained in this electronic message and any attachments > to this message are intended for the exclusive use of the addressee(s) and > may contain proprietary, confidential or privileged information. If you are > not the intended recipient, you should not disseminate, distribute or copy > this e-mail. Please notify the sender immediately and destroy all copies of > this message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient > should check this email and any attachments for the presence of viruses. The > company accepts no liability for any damage caused by any virus transmitted > by this email. > > www.wipro.com >
Re: [SQL] columns for count histograms of values
Just curious, how are you planning to display the histogram? Are you allowed to use application code (C/C++/Perl, etc..) to generate the histogram? Personally, SQL is great for showing the data but not good for making graphs with data you can show. On Wed, Apr 30, 2008 at 5:01 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > Greetings -- I have a table of the kind > > Ratings: > id integer > rating smallint > > -- where value can take any value in the range 1 to 5. Now I want to have > a statistical table Stats of the form > > id integer > min smallint > max smallint > avg real > r1 integer > r2 integer > r3 integer > r4 integer > r5 integer > > -- how can I create it in one pass over Ratings? I can use min(), max(), > avg() for > insert into stats values (id,select min(rating), max(rating), avg(rating), > ...) from ratings > > -- but what to do for r1,..,r5, short of subselects (select count(rating) > from ratings where stats.id=ratings.id) for each, which is an overkill? > > Also, if a table Stats already exists with some more columns, and we need > to do an update, not insert, for the above, how would that work -- > > update stats set min=min(ratings), ... from ratings where stats.id= > ratings.id -- how do we do the histogram in this case, where the id is > fixed explicitly? > > Cheers, > Alexy > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Difference in columns
Hi All, I have a view that generates output similar to this. select * from foo.view; ts | size ---+- 2002-03-16| 11 2002-03-17| 16 2002-03-18| 18 2002-03-19| 12 I am trying to find the difference between the size column. So the desired output would be ts | size| Diff ---+-+-- 2002-03-16| 11 | 0 2002-03-17| 15 | 4 2002-03-18| 18 | 3 2002-03-19| 12 | -6 I need the first column to be 0, since it will be 11-11. The second colum is 15-11. The third column is 18-15. The fourth column is 12-18. Any thoughts about this? TIA
Re: [SQL] Difference in columns
Thanks all. I will try some of these suggestions. On Sun, May 11, 2008 at 3:58 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Mag Gam wrote: > > > I am trying to find the difference between the size column. So the > > desired output would be > > > >ts | size| Diff > > ---+-+-- > > 2002-03-16| 11 | 0 > > > > 2002-03-17| 15 | 4 > > 2002-03-18| 18 | 3 > > 2002-03-19| 12 | -6 > > > > > > I need the first column to be 0, since it will be 11-11. The second > > colum is 15-11. The third column is 18-15. The fourth column is 12-18. > > > > Any thoughts about this? > > Here's one way to do this with PL/PgSQL. It's probably not the most > efficient, but it does work. For this code to be safe `size' must never > be NULL and `ts' must be unique across all records in the input set. > > CREATE OR REPLACE FUNCTION x_diff( >OUT ts TIMESTAMP, >OUT size INTEGER, >OUT diff INTEGER) > RETURNS SETOF record AS $$ > DECLARE >cur_x x; >last_size INTEGER := null; > BEGIN >FOR cur_x IN SELECT * FROM x ORDER BY ts ASC LOOP >ts := cur_x.ts; >size := cur_x.size; >IF last_size IS NULL THEN >-- First record in set has diff `0' because the differences >-- are defined against the previous, rather than next, >-- record. >diff := 0; >ELSE >diff := cur_x.size - last_size; >END IF; >last_size := cur_x.size; >RETURN NEXT; >END LOOP; >RETURN; > END; > $$ LANGUAGE 'plpgsql' STRICT; > > If you need to constrain the range of values processed that's not too > tricky - either feed the function a refcursor for a query result set to > iterate over, or pass it parameters to constrain the query with a WHERE > clause. The former is more flexible, the latter is easier to use. > > -- > Craig Ringer >
Re: [SQL] Auto-formatting timestamps?
Why not create a view? On Tue, May 13, 2008 at 11:58 AM, Peter Koczan <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm undergoing a port from an old Sybase database to Postgres. It's > going surprisingly well, but I have a question regarding formatting of > timestamps. > > In Sybase, we get: > :> select date from checkoutdate; > date > 'May 1 2001 12:00AM' > ... > > In Postgres: > => select date from checkoutdate; > date > - > 2001-05-01 00:00:00 > ... > > I can properly format it using to_char: > => select to_char(date, 'Mon DD HH:MIAM') as date from checkoutdate; > date > - > May 01 2001 12:00AM > ... > > Short of creating a wrapper type for timestamp (which seems like > overkill just for modifying the output function), is there a way to > output the Sybase format automatically (i.e. without a call to > to_char)? > > I've found some code that actually somewhat depends on this format, > and one of my goals in this port is to change as little client code as > possible. Is it possible to automatically change the output like this, > preferably on a per-connection basis? I found stuff regarding the > datestyle parameter in the docs, but that doesn't quite do what I'd > like. > > Thanks much, > Peter > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Auto-formatting timestamps?
But why not make a view similar to, select * from checkoutdate do select * from checkoutdate_special_view You don't have to do it for each table, you just do it for your source table, and use the view to manipulate stuff... If you have many tables like that, you can create a view for each of them, sure its a lot of work, but its worth it HTH On Wed, May 14, 2008 at 11:46 AM, Peter Koczan <[EMAIL PROTECTED]> wrote: > On Tue, May 13, 2008 at 5:54 PM, Mag Gam <[EMAIL PROTECTED]> wrote: > > Why not create a view? > > That's a possibility, but the issue is that the output formatting is > bound to a data type (timestamp), not one or two particular tables. > Trying to create a view for all the tables with timestamps (and > creating appropriate rules for updating views) would be a huge > administrative PITA. > > > > Hi all, > > > > > > I'm undergoing a port from an old Sybase database to Postgres. It's > > > going surprisingly well, but I have a question regarding formatting of > > > timestamps. > > > > > > In Sybase, we get: > > > :> select date from checkoutdate; > > > date > > > 'May 1 2001 12:00AM' > > > ... > > > > > > In Postgres: > > > => select date from checkoutdate; > > > date > > > - > > > 2001-05-01 00:00:00 > > > ... > > > > > > I can properly format it using to_char: > > > => select to_char(date, 'Mon DD HH:MIAM') as date from > checkoutdate; > > > date > > > - > > > May 01 2001 12:00AM > > > ... > > > > > > Short of creating a wrapper type for timestamp (which seems like > > > overkill just for modifying the output function), is there a way to > > > output the Sybase format automatically (i.e. without a call to > > > to_char)? > > > > > > I've found some code that actually somewhat depends on this format, > > > and one of my goals in this port is to change as little client code as > > > possible. Is it possible to automatically change the output like this, > > > preferably on a per-connection basis? I found stuff regarding the > > > datestyle parameter in the docs, but that doesn't quite do what I'd > > > like. > > > > > > Thanks much, > > > Peter >
