Re: [SQL] We all are looped on Internet: request + transport = invariant
>you are proposing is to ... break the relational model of SQL No. I'm proposing to add new thinking about relational model (primary i want to write "to add new view", but i remember, that "view" is reserved word in SQL :) ). Сonvince yourself, please: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref num references a(id); data float; ); create table c ( id num primary key; link num references b(id); data float; ); insert into a values (1, 12.3); insert into b values (10, 1, 23.4); insert into b values (20, 1, 34.5); insert into b values (30, 1, 45.6); insert into c values (100,10,56.7); insert into c values (101,10,67.8); insert into c values (200,20,78.9); insert into c values (201,20,89.1); insert into c values (300,30,91.2); My suggest is "a.b.c"-request. Output is I wrote about that in http://sql4.by.ru/site/sql40/en/author/introduction_eng.htm http://sql4.by.ru/site/sql40/en/author/determination_eng.htm >to make it look more like XML Understand: XML is not model of data (in my suggest), XML is format of output. Can you offer better format for output of tree? If you can, write please, i listen. >because of some special purpose application Reception of tree from database is really often case. For example, open several schemes of database (in Erwin, in RationalRose, in other modeller) - scheme is net (count). After that, look at source of server programs, servicing this databases. These programs get part of net (not part of one table!, not part of joined tables! not part of one view!) and put it into program-visualizer. In back direction, server program get tree (records for several tables, not for one table!) and put it into database. >SQL ... It's ... language, unconcerned with transport I agree. Now it's so. >change the syntax of SQL SQL remains inviolate. I offer addition to it - i offer Tree Manipulation Language. Look once more: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref1 num references a(id); ref2 num references a(id); data float; ); create table c ( id num primary key; lnk1 num references b(id); lnk2 num references b(id); data float; ); insert into a values (1,12.3); insert into a values (2,23.4); insert into b values (10, 1, 2, 34.5); insert into b values (20, 1, 2, 45.6); insert into b values (30, 1, 2, 56.7); insert into b values (40, 1, 2, 67.8); insert into c values (100,10,20,78.9); insert into c values (101,10,20,89.1); insert into c values (200,30,40,91.2); insert into c values (201,30,40,88.8) But TML is: a.b/ref1.c/lnk1 --- P.S. I'm very glad to get acquainted with you, and i'm much interesting to talk with you. Let's to write maximum simply, let's to use maximum simple expressions to understand each other better. Dmitry Turin http://html60.chat.ru http://sql40.chat.ru ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] many 'OR' in WHERE-condition
A. Kretschmer wrote: in a SELECT with a condition 'WHERE foo IN (1,2,3,4,...)' with a large ... But if the list increase the planner choose an seq-scan. The amount of values in the list is about 200 and the table has about 250.000 rows. How can we avoid the seq-scan? Are you sure you'd want to? What plan have you found to be faster with 200 tests? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] We all are looped on Internet: request + transport = invariant
Dmitry Turin wrote: you are proposing is to ... break the relational model of SQL No. I'm proposing to add new thinking about relational model (primary i want to write "to add new view", but i remember, that "view" is reserved word in SQL :) ). Сonvince yourself, please: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref num references a(id); data float; ); create table c ( id num primary key; link num references b(id); data float; ); insert into a values (1, 12.3); insert into b values (10, 1, 23.4); insert into b values (20, 1, 34.5); insert into b values (30, 1, 45.6); insert into c values (100,10,56.7); insert into c values (101,10,67.8); insert into c values (200,20,78.9); insert into c values (201,20,89.1); insert into c values (300,30,91.2); My suggest is "a.b.c"-request. Output is I wrote about that in http://sql4.by.ru/site/sql40/en/author/introduction_eng.htm http://sql4.by.ru/site/sql40/en/author/determination_eng.htm to make it look more like XML Understand: XML is not model of data (in my suggest), XML is format of output. Can you offer better format for output of tree? If you can, write please, i listen. Your format seems to have thrown away all type information. How do I know with the "id=200" that 200 is an int rather than a byte or string? Also, how do we add the new table? CREATE TABLE d ( aref num references a, bref num references b, cref num references c ); because of some special purpose application Reception of tree from database is really often case. Well it's sometimes the case. I'm not sure you've given any evidence that *most* (or even many) accesses to a database want to retrieve a simple tree. And only a tree - presumably your alternative access method doesn't let me grab "nets" (directed graphs, possibly cyclic). For example, open several schemes of database (in Erwin, in RationalRose, in other modeller) - scheme is net (count). So - not a tree? After that, look at source of server programs, servicing this databases. These programs get part of net (not part of one table!, not part of joined tables! not part of one view!) and put it into program-visualizer. Also not trees (necessarily) In back direction, server program get tree (records for several tables, not for one table!) and put it into database. I'm not sure that anyone is clear why you just don't write this as a simple php/perl/ruby/whatever script? There are libraries that will output XML for you in most of these I'd suspect, and if not it's just an afternoon's work. I mean, your application is written in *some* language, isn't it? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] We all are looped on Internet: request + transport = invariant
Trees like you seem to suggest are called hierarchies. If you prefer hierarchies over relationnality you could use a hierarchical database instead of a relational database. When you search the internet you can find plenty of information on eg Adabas, GT.M., IMS, DMSII, Focus, Metakit and many others. >>> Dmitry Turin <[EMAIL PROTECTED]> 2007-04-20 11:08 >>> >you are proposing is to ... break the relational model of SQL No. I'm proposing to add new thinking about relational model (primary i want to write "to add new view", but i remember, that "view" is reserved word in SQL :) ). *onvince yourself, please: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref num references a(id); data float; ); create table c ( id num primary key; link num references b(id); data float; ); insert into a values (1, 12.3); insert into b values (10, 1, 23.4); insert into b values (20, 1, 34.5); insert into b values (30, 1, 45.6); insert into c values (100,10,56.7); insert into c values (101,10,67.8); insert into c values (200,20,78.9); insert into c values (201,20,89.1); insert into c values (300,30,91.2); My suggest is "a.b.c"-request. Output is I wrote about that in http://sql4.by.ru/site/sql40/en/author/introduction_eng.htm http://sql4.by.ru/site/sql40/en/author/determination_eng.htm >to make it look more like XML Understand: XML is not model of data (in my suggest), XML is format of output. Can you offer better format for output of tree? If you can, write please, i listen. >because of some special purpose application Reception of tree from database is really often case. For example, open several schemes of database (in Erwin, in RationalRose, in other modeller) - scheme is net (count). After that, look at source of server programs, servicing this databases. These programs get part of net (not part of one table!, not part of joined tables! not part of one view!) and put it into program-visualizer. In back direction, server program get tree (records for several tables, not for one table!) and put it into database. >SQL ... It's ... language, unconcerned with transport I agree. Now it's so. >change the syntax of SQL SQL remains inviolate. I offer addition to it - i offer Tree Manipulation Language. Look once more: create table a ( id num primary key; data float; ); create table b ( id num primary key; ref1 num references a(id); ref2 num references a(id); data float; ); create table c ( id num primary key; lnk1 num references b(id); lnk2 num references b(id); data float; ); insert into a values (1,12.3); insert into a values (2,23.4); insert into b values (10, 1, 2, 34.5); insert into b values (20, 1, 2, 45.6); insert into b values (30, 1, 2, 56.7); insert into b values (40, 1, 2, 67.8); insert into c values (100,10,20,78.9); insert into c values (101,10,20,89.1); insert into c values (200,30,40,91.2); insert into c values (201,30,40,88.8) But TML is: a.b/ref1.c/lnk1 --- P.S. I'm very glad to get acquainted with you, and i'm much interesting to talk with you. Let's to write maximum simply, let's to use maximum simple expressions to understand each other better. Dmitry Turin http://html60.chat.ru ( http://html60.chat.ru/ ) http://sql40.chat.ru ( http://sql40.chat.ru/ ) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] many 'OR' in WHERE-condition
am Fri, dem 20.04.2007, um 12:03:44 +0100 mailte Richard Huxton folgendes: > A. Kretschmer wrote: > >in a SELECT with a condition 'WHERE foo IN (1,2,3,4,...)' with a large > ... > >But if the list increase the planner choose an seq-scan. The amount of > >values in the list is about 200 and the table has about 250.000 rows. > > > >How can we avoid the seq-scan? > > Are you sure you'd want to? What plan have you found to be faster with > 200 tests? It's not my problem - i found it in a forum (german) http://pg-forum.de/showthread.php?t=1332 If the list contains up to 195 entrys -> up to 195 bitmap index scans and BitmapOr -> 7.839ms If the list contains 196 entrys -> seq-scan -> 5591.567ms Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Retrieve month from date
am Fri, dem 20.04.2007, um 17:49:33 +0530 mailte Rohit Khare folgendes:
> I am using PGSQL 8.2.3 on Windows XP.
>
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "-mm-dd". I have
> to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or whatever
> month I give.
>
> I tried:
>
> Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
> ('ReceiptMonthYear'))=4;
>
> But it is not working. How to do it?
... where extract(month from "ReceiptMonthYear") = 4
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] We all are looped on Internet: request + transport = invariant
Joe wrote: Hi Richard, On Fri, 2007-04-20 at 12:14 +0100, Richard Huxton wrote: I'm not sure that anyone is clear why you just don't write this as a simple php/perl/ruby/whatever script? There are libraries that will output XML for you in most of these I'd suspect, and if not it's just an afternoon's work. I mean, your application is written in *some* language, isn't it? It appears that Dmitry wants that an application, written in whatever language, will issue an SQL query (or maybe a TML "query" such as "a.b.c") and get back the XML. He's not concerned about loss of datatype information because from his perspective the application will just "present" the XML and similarly he's not concerned about DDL features because TML is a "transport" mechanism. I hope I haven't totally misrepresented Dmitry's words, but it seems he wants to create a universal "database to web" interface language, so that you and I won't have to deal with the pesky complications of retrieving data in C with libpq or with PHP, Python and what not libraries or "adapters", and then have to transform that data for display to the user. OK, but I'm still not seeing how this avoids me having to use PHP/Perl/etc anyway. I mean, I'm going to need some application logic at some point, in which case who cares whether the RDBMS has this specific layout as a format. I can see how it might be useful as a library, but then there are libraries that provide plenty of XML formatting. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Retrieve month from date
Please always include the error message you get when something isn't
working.
If you defined your table with quoted identifiers (create table
"StudentFeesPayment" as ...) then try
Select max("ReceiptNo") from "StudentFeesPayment" where Extract(Month
from "ReceiptMonthYear")=4;
else try
Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
from ReceiptMonthYear)=4;
>>> "Rohit Khare" <[EMAIL PROTECTED]> 2007-04-20 14:19 >>>
I am using PGSQL 8.2.3 on Windows XP.
I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
"ReceiptMonthYear".
The column, "ReceiptMonthYear" stores date in the format "-mm-dd".
I have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4.
Or whatever month I give.
I tried:
Select max(ReceiptNo) from StudentFeesPayment where
Extract(Month('ReceiptMonthYear'))=4;
But it is not working. How to do it?
Re: [SQL] We all are looped on Internet: request + transport = invariant
Just some thoughts on the matter: Dmitry, you should acknowledge the fact that in databses you have two kinds of hierarchies: a) The hierarchy denoted by the referential constraints between tables which constitute a Directed Graph (DG), with tables as nodes and FKeys as arrows. b) Hierarchies applying to data, e.g. create table ppl ( id serial PRIMARY KEY, name text not null, parentid integer REFERENCES ppl(id) ); (with GOD himself having parentid = null :). I think that the second type is of interest at the application level. (For system applications however, such as DB mirroring the first type of table hierarchy is important as well). Speaking of postgresql there is a fine package by the GiST/tsearch creators, contrib/ltree whose main task is to provide the primitives (functions/operators) for tree manipulation. Dont know if i am irrelevant in this thread, but i hope i helped a little bit. -- Achilleas Mantzios ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Function to return a multiple colmn table or view
I am new to psql , so please be patient ! Can someone please provide a small quick example of a a function that take 1 paramater and based on that parameter, returns a table or view ? Etc Note this is just a abstract of the functon, not a working function call !!! Create function viewtest( start_month) If $1 = 'April' Then Select * from april_view; Return ; Let me know how to do this. Thanks
Re: [SQL] We all are looped on Internet: request + transport = invariant
>How do I know with the "id=200" that 200 is an int rather than a byte or >string? Field (of database) and attribute (of tag) are put into one-to-one correspondence. Thus datatype of attribute is datatype of field (which is specified during "create table" or "alter table"). >how do we add the new table? by DDL, of course. TML don't create/alter/drop table/index. >accesses to a database want to retrieve a simple tree It's difficult to bring inverse example - when it's necessary to get records of only one table. Really, in life (not in math problems). >your alternative access method doesn't let me grab "nets" (directed graphs, >possibly cyclic) Yes. You must inquere records of two tables (nodes and bonds) separately. >> After that, look at source of server programs, servicing this databases. >> These programs get part of net (not part of one table!, not part of >> joined tables! not part of one view!) and put it into program-visualizer. >Also not trees (necessarily) No. Server program inqueries tree (as part of graph). >why you just don't write this as a simple php/perl/ruby/whatever script? Not me. Not programmer. I speak about appied specialists (physicists, biologists, etc), which can NOT do that. Join of SQL and php (using libraries) is problem for them. >it's just an afternoon's work Not for mentioned people. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] many 'OR' in WHERE-condition
am Fri, dem 20.04.2007, um 12:59:01 +0100 mailte Richard Huxton folgendes: > A. Kretschmer wrote: > > > >It's not my problem - i found it in a forum (german) > >http://pg-forum.de/showthread.php?t=1332 > > > >If the list contains up to 195 entrys -> up to 195 bitmap index scans > >and BitmapOr -> 7.839ms > > > >If the list contains 196 entrys -> seq-scan -> 5591.567ms > > It looks like it's getting the row estimate badly wrong. > > "Seq Scan on t_datasets_searchindices_rel (cost=0.00..129053.50 > rows=159277 width=4) (actual time=0.213..5590.435 rows=325 loops=1) Uh oh, i have overlook this. > > Probably worth increasing the statistics estimate for that column (ALTER > TABLE ... ALTER COLUMN ... SET STATISTICS=N) Thx for your hints! Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Retrieve month from date
I am using PGSQL 8.2.3 on Windows XP.
I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
"ReceiptMonthYear".
The column, "ReceiptMonthYear" stores date in the format "-mm-dd". I
have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or
whatever month I give.
I tried:
Select max(ReceiptNo) from StudentFeesPayment where
Extract(Month('ReceiptMonthYear'))=4;
But it is not working. How to do it?
Re: [SQL] many 'OR' in WHERE-condition
A. Kretschmer wrote: It's not my problem - i found it in a forum (german) http://pg-forum.de/showthread.php?t=1332 If the list contains up to 195 entrys -> up to 195 bitmap index scans and BitmapOr -> 7.839ms If the list contains 196 entrys -> seq-scan -> 5591.567ms It looks like it's getting the row estimate badly wrong. "Seq Scan on t_datasets_searchindices_rel (cost=0.00..129053.50 rows=159277 width=4) (actual time=0.213..5590.435 rows=325 loops=1) Probably worth increasing the statistics estimate for that column (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS=N) It also looks like it's getting the cost estimates badly wrong. Probably worth asking the original poster to read the tuning guide. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] We all are looped on Internet: request + transport = invariant
Hi Richard, On Fri, 2007-04-20 at 13:50 +0100, Richard Huxton wrote: > OK, but I'm still not seeing how this avoids me having to use > PHP/Perl/etc anyway. I mean, I'm going to need some application logic at > some point, in which case who cares whether the RDBMS has this specific > layout as a format. I can see how it might be useful as a library, but > then there are libraries that provide plenty of XML formatting. But if TML catches on so that "even a caveman can do it" *, i.e., query a database from just a browser, then you and I (and most everyone on this list) will be out of a job and PHP/Perl/etc. will be relegated to the dustbin of programming languages. [Sorry, couldn't resist :-) ] Joe * see http://en.wikipedia.org/wiki/Geico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] We all are looped on Internet: request + transport = invariant
Hi Richard, On Fri, 2007-04-20 at 12:14 +0100, Richard Huxton wrote: > I'm not sure that anyone is clear why you just don't write this as a > simple php/perl/ruby/whatever script? There are libraries that will > output XML for you in most of these I'd suspect, and if not it's just an > afternoon's work. > > I mean, your application is written in *some* language, isn't it? It appears that Dmitry wants that an application, written in whatever language, will issue an SQL query (or maybe a TML "query" such as "a.b.c") and get back the XML. He's not concerned about loss of datatype information because from his perspective the application will just "present" the XML and similarly he's not concerned about DDL features because TML is a "transport" mechanism. I hope I haven't totally misrepresented Dmitry's words, but it seems he wants to create a universal "database to web" interface language, so that you and I won't have to deal with the pesky complications of retrieving data in C with libpq or with PHP, Python and what not libraries or "adapters", and then have to transform that data for display to the user. However, IMHO this appears to be rather simplistic and not completely grounded in actual experience. In other words, Dmitry, have you written a TML parser or a libpq prototype that given a TML "query" produces the XML and tested it with datatypes other than integer and float. Or do you have feedback from actual average users that TML is indeed easier to use than SQL? Two other items to think about. One is that the scheme apparently requires that tables be defined in a special way, but no indication is given as to how this will be accomplished or ensured that it is correct. Second is how does TML handle relational manipulations such as restriction, projection or aggregation. It appears TML is primarily for joins. Lastly, Dmitry, I think you'll be better off discussing this in comp.databases.theory. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Function to return a multiple colmn table or view
Wilkinson, Jim wrote: I am new to psql , so please be patient ! Can someone please provide a small quick example of a a function that take 1 paramater and based on that parameter, returns a table or view ? Etc Note this is just a abstract of the functon, not a working function call !!! Create function viewtest( start_month) If $1 = 'April' Then Select * from april_view; Return ; CREATE TABLE month_data (md_year int4, md_month int4, md_data text); CREATE VIEW jan_view AS SELECT * FROM month_data WHERE md_month=1; CREATE FUNCTION viewtest(startmonth int) RETURNS SETOF month_data AS $$ DECLARE sql text; r record; BEGIN IF startmonth = 1 THEN sql := 'SELECT * FROM jan_view'; ELSE sql := 'SELECT * FROM month_data'; END IF; FOR r IN EXECUTE sql LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] We all are looped on Internet: request + transport = invariant
On Fri, Apr 20, 2007 at 05:49:08PM +0300, Dmitry Turin wrote: > I speak about appied specialists (physicists, biologists, etc), which > can NOT do that. > >it's just an afternoon's work > > Not for mentioned people. I think part of the reason I'm sceptical of your plan is that every physicist or biologist I ever knew, who had anything to do with storing large volumes of data, all knew a great deal of Perl. What they didn't know, they got the computer-support staff (that was my job) to build for them. People who work with data have to learn to use the tools for processing it. Nobody ever suggests that stats is just too hard, so we should make an easy-stats course that just covers the teeny bit that (say) biologists need to know. That seems to be what you are suggesting be done to SQL. Now, anyone who knows anything about biology -- or physics, for that matter -- knows that you actually have to have a good handle on stats to do any interesting work. You don't need everything economists use, though, so you don't learn those bits. Similarly, I don't expect a biologist to learn (for instance) the Net::DNS modules for Perl. That doesn't mean they can get away without learning a bit about XML, Perl, and Pg.pm. Anyway, I've said enough on this topic. When you have the start of a user library that implements your proposal, perhaps you can post it to -hackers for the response you'll get there. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
>have you written a TML parser No. I want, that you and other people help me in that. >do you have feedback from actual average users, >that TML is indeed easier to use than SQL? Yes. TML was born in discuss with these users (mainly nucleus physicists, optics , molecular biologists, zoologist and ecologist) >scheme apparently requires, that tables be defined in a special way No. Table must be created in traditional way (by "create table, alter table") and not through browser. User must use "create table", etc in database-terminal like "psql.exe". > you'll be better off discussing this in comp.databases.theory There are two reason to discuss TML in this forum: 1. postgres is my favorite database, 2. discussion not in theory forum, but in forum of real database approach to realization in greater degree. Dmitry Turin http://html6.by.ru http://sql4.by.ru ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Retrieve month from date
Thanks both of you,
I ran EXPLAIN command on above suggested query and got following result:
"Aggregate (cost=2.77..2.79 rows=1 width=10)"
" -> Seq Scan on studentfeespayment (cost=0.00..2.77 rows=1 width=10)"
"Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
without time zone) = 4::double precision)"
What does this mean? Can I optimize it better?
--
View this message in context:
http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10105471
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Retrieve month from date
On 4/20/07, RPK <[EMAIL PROTECTED]> wrote:
Thanks both of you,
I ran EXPLAIN command on above suggested query and got following result:
"Aggregate (cost=2.77..2.79 rows=1 width=10)"
" -> Seq Scan on studentfeespayment (cost=0.00..2.77 rows=1 width=10)"
"Filter: (date_part('month'::text, (recieptmonthyear)::timestamp
without time zone) = 4::double precision)"
What does this mean? Can I optimize it better?
create index lala on "StudentFeesPayment"(Extract(Month from
"ReceiptMonthYear"))
explain Select max("ReceiptNo") from "StudentFeesPayment" where
Extract(Month from "ReceiptMonthYear")=4;
Aggregate (cost=14.97..14.98 rows=1 width=4)
-> Bitmap Heap Scan on "StudentFeesPayment" (cost=4.33..14.94
rows=10 width=4)
Recheck Cond: (date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)
-> Bitmap Index Scan on lala (cost=0.00..4.33 rows=10 width=0)
Index Cond: (date_part('month'::text,
("ReceiptMonthYear")::timestamp without time zone) = 4::double
precision)
See:
http://www.postgresql.org/docs/8.2/static/indexes-expressional.html
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] We all are looped on Internet: request + transport = invariant
> he wants to create a universal "database to web" interface language, so > that you and I won't have to deal with the pesky complications of > retrieving data in C with libpq or with PHP, Python and what not > libraries or "adapters", and then have to transform that data for > display to the user. Yes. Especially about adapters ! >OK, but I'm still not seeing how this avoids me having to use >PHP/Perl/etc anyway. TML stored function get XML from external world (DBMS listen port #80), work, than make output of other XML into external world. Transport is executed through HTTP. >who cares whether the RDBMS has this specific layout as a format. I want to answer, but i don't understood phrase "layout as a format". Please, explain your question. >I can see how it might be useful as a library I don't see, how to use TML as library :) >there are libraries that provide plenty of XML formatting Once more: XML is side effect! If you know better format for tree, please describe it. Gold is ejection of shim (gasket): php, perl, etc. Dmitry Turin http://html6.by.ru http://sql4.by.ru ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] We all are looped on Internet: request + transport = invariant
>every physicist or biologist ... knew a great deal of Perl They spend own time and force for that. Next generation will not have this need. >they got the computer-support staff (that was my job) And your money. Besides money, they spend own time to explain task to you. >People ... have to learn They must nothing, they are forced >That doesn't mean they can get away without learning a bit about ... Perl Why? Dmitry Turin http://html60.by.ru http://sql40.by.ru ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Retrieve month from date
What this query will return:
Select Extract(Month from 4/20/2007) from dual;
I suspect "dual" is not for PGSQL but Oracle. But I need to run the above
query. What is the replacement of "dual" in PGSQL.
Bart Degryse wrote:
>
> Please always include the error message you get when something isn't
> working.
>
> If you defined your table with quoted identifiers (create table
> "StudentFeesPayment" as ...) then try
> Select max("ReceiptNo") from "StudentFeesPayment" where Extract(Month
> from "ReceiptMonthYear")=4;
> else try
> Select max(ReceiptNo) from StudentFeesPayment where Extract(Month
> from ReceiptMonthYear)=4;
>
>
>
"Rohit Khare" <[EMAIL PROTECTED]> 2007-04-20 14:19 >>>
> I am using PGSQL 8.2.3 on Windows XP.
>
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "-mm-dd".
> I have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4.
> Or whatever month I give.
>
> I tried:
>
> Select max(ReceiptNo) from StudentFeesPayment where
> Extract(Month('ReceiptMonthYear'))=4;
>
> But it is not working. How to do it?
>
>
>
--
View this message in context:
http://www.nabble.com/Retrieve-month-from-date-tf3617513.html#a10106861
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Retrieve month from date
am Fri, dem 20.04.2007, um 10:30:16 -0700 mailte RPK folgendes: > > What this query will return: > > Select Extract(Month from 4/20/2007) from dual; > > I suspect "dual" is not for PGSQL but Oracle. But I need to run the above > query. What is the replacement of "dual" in PGSQL. Simply "select extract(month from current_date);" > > > Bart Degryse wrote: please no silly fullquote below your text. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Retrieve month from date
On Fri, 2007-04-20 at 12:30, RPK wrote: > What this query will return: > > Select Extract(Month from 4/20/2007) from dual; > > I suspect "dual" is not for PGSQL but Oracle. But I need to run the above > query. What is the replacement of "dual" in PGSQL. > Well, you're going to have to create a special one row table with contraints and triggers to always keep it at one row and now allow anyone to insert into it and... just kidding. All you need is: select extract(month from '4/20/2007'::date); i.e. no need for the extraneous from dual there. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Question on interval
Hi all. How do I write a query that converts an interger to the interval type? Like convert integer 10 to INTERVAL '10 seconds'? The integer is a column in a table though, so it is more like convert integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds". Thanks! Wei ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Question on interval
On 4/20/07, Wei Weng <[EMAIL PROTECTED]> wrote: Hi all. How do I write a query that converts an interger to the interval type? Like convert integer 10 to INTERVAL '10 seconds'? The integer is a column in a table though, so it is more like convert integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds". Thanks! Wei select (10||' sec')::interval; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Question on interval
On Apr 20, 2007, at 13:53 , Wei Weng wrote: How do I write a query that converts an interger to the interval type? Like convert integer 10 to INTERVAL '10 seconds'? An easy way to do this is: SELECT 10 * INTERVAL '1 second'; The integer is a column in a table though, so it is more like convert integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds". If you'd like to change the type of the column to interval, you can use something like ALTER TABLE a_table ALTER COLUMN an_integer_column TYPE INTERVAL USING an_integer_column * INTERVAL '1 second'; You could also create a view that would present the integer column as an interval if you don't want to alter the table itself. Does this help? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Question on interval
On Fri, 2007-04-20 at 13:53, Wei Weng wrote: > Hi all. > > How do I write a query that converts an interger to the interval type? > > Like convert integer 10 to INTERVAL '10 seconds'? > > The integer is a column in a table though, so it is more like convert > integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds". A lot of the times, it's easiest to multiply it: select . where somedatefield > now() - interval '1 minute' * 5 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Question on interval
Rodrigo De León wrote: > On 4/20/07, Wei Weng <[EMAIL PROTECTED]> wrote: >> Hi all. >> >> How do I write a query that converts an interger to the interval type? >> >> Like convert integer 10 to INTERVAL '10 seconds'? >> >> The integer is a column in a table though, so it is more like convert >> integer tbl.theInteger to INTERVAL 'tbl.theInteger seconds". >> >> Thanks! >> >> >> Wei > > select (10||' sec')::interval; Or, if you prefer: select 10*'1 second'::interval; Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
