Thread: SQL syntax rowcount value as an extra column in the result set
<p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">Hello</font></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">I</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">m using PostgreSQL (8.4.701) and Java (jdbc</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">,</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <font face="Calibri">postgresql-8.4-701.jdbc4.jar</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">) to connect to the database.</font></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Myquestion is:</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">what is the SQL syntax for PostgreSQL to achieve the following:</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">I want to receive the rowcountalong with the rest of a result set. For example, let</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">s say the following query returns</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select first_name from people</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">;</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">first_name</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">=========</font></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">Mary</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">Sue</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">Joe</font></span><br /><p dir="LTR"><spanlang="en-us"><font face="Calibri">and the following query returns the value</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> </span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">select count(*)</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">as ROWCOUNT</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri"></font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <fontface="Calibri">from people</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">;</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">==========</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">3</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">What I</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">’</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">m looking for is the output as</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT , first_name</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">=====================</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Calibri">3, Mary</font></span><p dir="LTR"><span lang="en-us"><font face="Calibri">3 , Sue</font></span><p dir="LTR"><spanlang="en-us"><font face="Calibri">3 , Joe</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><pdir="LTR"><span lang="en-us"><font face="Calibri">so I can use JDBC (snip-it) as follows:</font></span><pdir="LTR"><span lang="en-us"><font face="Calibri">resultSet.getInt(</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">“</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">ROWCOUNT</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">”</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">resultSet.getString(</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">“</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">first_name</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">”</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri">)</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">On a side note,</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font face="Calibri">Oracle allows the following</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"> <font face="Calibri">syntax</font></span><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"><font face="Calibri"> to achieve the above</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">:</font></span><p dir="LTR"><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri">select count(*)</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"> over ()</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">as ROWCOUNT</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Calibri"></font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">, first_name</font></span><span lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"> <font face="Calibri">from people</font></span><p dir="LTR"><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"></span><p dir="LTR"><span lang="en-us"><fontface="Calibri">Thank</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">s,</font></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><fontface="Calibri">Jim</font></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><spanlang="en-us"></span>
Snyder, James wrote on 25.03.2010 22:33: > I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 > On a side note, Oracle allows the following syntax to achieve the above: > > select count(*) over () as ROWCOUNT , first_name from people > The same syntax will work on Postgres Thomas
<font face="sans-serif" size="2">Hi,</font><br /><font face="sans-serif" size="2">Is this what you are trying to do?</font><br/><font face="sans-serif" size="2">postgres=# select * from (select count(*) from people ) p, (select firstnamefrom people)p2;</font><br /><font face="sans-serif" size="2"> count | firstname</font><br /><font face="sans-serif"size="2">-------+-----------</font><br /><font face="sans-serif" size="2"> 5 | Mary</font><br /><fontface="sans-serif" size="2"> 5 | Mary</font><br /><font face="sans-serif" size="2"> 5 | John</font><br /><fontface="sans-serif" size="2"> 5 | John</font><br /><font face="sans-serif" size="2"> 5 | Jacob</font><br /><fontface="sans-serif" size="2">(5 rows)</font><br /><font face="sans-serif" size="2">I do not know about the performanceimpact of such a query (cartesian join)</font><br /><font face="sans-serif" size="2">Regards,</font><br /><fontface="Trebuchet MS" size="2">Jayadevan</font><br /><br /><br /><br /><br /><font color="#5f5f5f" face="sans-serif"size="1">From: </font><font face="sans-serif" size="1">"Snyder, James" <jsnyde07@harris.com></font><br/><font color="#5f5f5f" face="sans-serif" size="1">To: </font><font face="sans-serif"size="1"><pgsql-sql@postgresql.org></font><br /><font color="#5f5f5f" face="sans-serif" size="1">Date: </font><font face="sans-serif" size="1">26/03/2010 03:21</font><br /><font color="#5f5f5f" face="sans-serif"size="1">Subject: </font><font face="sans-serif" size="1">[SQL] SQL syntax rowcount value as an extracolumn in the result set</font><br /><font color="#5f5f5f" face="sans-serif" size="1">Sent by: </font><font face="sans-serif"size="1">pgsql-sql-owner@postgresql.org</font><br /><hr noshade /><br /><br /><br /><font face="Calibri"size="3">Hello</font><p><font face="Calibri" size="3">I’m using PostgreSQL (8.4.701) and Java (jdbc,</font><fontsize="3"> </font><font face="Calibri" size="3">postgresql-8.4-701.jdbc4.jar) to connect to the database.</font><p><fontface="Calibri" size="3">My question is: what is the SQL syntax for PostgreSQL to achieve the following:</font><p><fontface="Calibri" size="3">I want to receive the rowcount along with the rest of a result set. Forexample, let’s say the following query returns</font><p><font face="Calibri" size="3">select first_name from people;</font><p><fontface="Calibri" size="3">first_name</font><p><font face="Calibri" size="3">=========</font><p><fontface="Calibri" size="3">Mary</font><p><font face="Calibri" size="3">Sue</font><p><font face="Calibri"size="3">Joe</font><p><p><font face="Calibri" size="3">and the following query returns the value</font><fontsize="3"></font><p><font face="Calibri" size="3">select count(*)as ROWCOUNT</font><font size="3"></font><fontface="Calibri" size="3">from people;</font><p><font face="Calibri" size="3">ROWCOUNT</font><p><fontface="Calibri" size="3">==========</font><p><font face="Calibri" size="3">3</font><p><fontface="Calibri" size="3">3</font><p><font face="Calibri" size="3">What I’m looking for is the outputas</font><p><font face="Calibri" size="3">ROWCOUNT , first_name</font><p><font face="Calibri" size="3">=====================</font><p><fontface="Calibri" size="3">3 , Mary</font><p><font face="Calibri" size="3">3 ,Sue</font><p><font face="Calibri" size="3">3 , Joe</font><p><font face="Calibri" size="3">so I can use JDBC (snip-it) asfollows:</font><p><font face="Calibri" size="3">resultSet.getInt(“ROWCOUNT”)</font><p><font face="Calibri" size="3">resultSet.getString(“first_name”)</font><p><fontface="Calibri" size="3">On a side note,</font><font size="3"> </font><fontface="Calibri" size="3">Oracle allows the following</font><font size="3"> </font><font face="Calibri" size="3">syntaxto achieve the above:</font><p><font face="Calibri" size="3">select count(*) over ()</font><font size="3"></font><fontface="Calibri" size="3">as ROWCOUNT</font><font size="3"> </font><font face="Calibri" size="3">, first_name</font><fontsize="3"> </font><font face="Calibri" size="3">from people</font><p><font face="Calibri" size="3">Thanks,Jim</font><p><fontface="sans-serif"></font><img src="http://www.ibsplc.com/images/email_footer_final.gif"/> <font color="#999999" face="Tahoma" size="1"><u> DISCLAIMER:</u></font> <font color="#999999" face="Tahoma" size="1"> "The information in this e-mail and any attachment isintended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you havereceived this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makesno warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information containedin this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant lossor damage, if any, direct or indirect."</font><br /><font size="1"> </font>
<font face="sans-serif" size="2">Hi,</font><br /><font face="sans-serif" size="2">I don't think so.</font><br /><font face="sans-serif"size="2">Oracle - </font><br /><font face="sans-serif" size="2">SQL> select count(*) over () as ROWCOUNT, first_name from people;</font><br /><br /><font face="sans-serif" size="2"> ROWCOUNT FIRST_NAME</font><br /><fontface="sans-serif" size="2">---------- ---------------------------------------------------------------------</font><br/><font face="sans-serif" size="2">-------------------------------</font><br/><font face="sans-serif" size="2"> 6 Mary</font><br /><font face="sans-serif"size="2"> 6 Mary</font><br /><font face="sans-serif" size="2"> 6 John</font><br /><fontface="sans-serif" size="2"> 6 John</font><br /><font face="sans-serif" size="2"> 6 John</font><br/><font face="sans-serif" size="2"> 6 Jacob</font><br /><br /><font face="sans-serif" size="2">6 rowsselected.</font><br /><br /><font face="sans-serif" size="2">PostgreSQL</font><br /><font face="sans-serif" size="2">postgres=#select count(*) over () as ROWCOUNT , first_name from people;</font><br /><font face="sans-serif" size="2">ERROR: syntax error at or near "over"</font><br /><font face="sans-serif" size="2">LINE 1: select count(*) over() as ROWCOUNT , first_name from people...</font><br /><font face="sans-serif" size="2"> ^</font><br/><font face="sans-serif" size="2">Regards,</font><br /><font face="Trebuchet MS" size="2">Jayadevan</font><br/><br /><br /><br /><font color="#5f5f5f" face="sans-serif" size="1">From: </font><fontface="sans-serif" size="1">Thomas Kellerer <spam_eater@gmx.net></font><br /><font color="#5f5f5f" face="sans-serif"size="1">To: </font><font face="sans-serif" size="1">pgsql-sql@postgresql.org</font><br /><font color="#5f5f5f"face="sans-serif" size="1">Date: </font><font face="sans-serif" size="1">26/03/2010 03:26</font><br/><font color="#5f5f5f" face="sans-serif" size="1">Subject: </font><font face="sans-serif" size="1">Re:[SQL] SQL syntax rowcount value as an extra column in the result set</font><br /><font color="#5f5f5f" face="sans-serif"size="1">Sent by: </font><font face="sans-serif" size="1">pgsql-sql-owner@postgresql.org</font><br/><hr noshade /><br /><br /><br /><tt><font size="2">Snyder, James wroteon 25.03.2010 22:33:<br /><br />> I’m using PostgreSQL (8.4.701)<br />There is no such version.<br />The currentversion is 8.4.3<br /><br /><br />> On a side note, Oracle allows the following syntax to achieve the above:<br/>><br />> select count(*) over () as ROWCOUNT , first_name from people<br />><br />The same syntax willwork on Postgres<br /><br />Thomas<br /><br /><br />-- <br />Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br/>To make changes to your subscription:<br /></font></tt><a href="http://www.postgresql.org/mailpref/pgsql-sql"><tt><font size="2">http://www.postgresql.org/mailpref/pgsql-sql</font></tt></a><tt><fontsize="2"><br /></font></tt><br /><font face="sans-serif"></font><img src="http://www.ibsplc.com/images/email_footer_final.gif" /> <font color="#999999" face="Tahoma"size="1"><u> DISCLAIMER:</u></font> <font color="#999999" face="Tahoma" size="1"> "The information in thise-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/orprivileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies ofthe original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completenessof the information contained in this email or any attachment and is not liable for any errors, defects, omissions,viruses or for resultant loss or damage, if any, direct or indirect."</font><br /><font size="1"> </font>
In response to Jayadevan M : > Hi, > I don't think so. > Oracle - > SQL> select count(*) over () as ROWCOUNT , first_name from people; > > ROWCOUNT FIRST_NAME > ---------- > --------------------------------------------------------------------- > ------------------------------- > 6 Mary > 6 Mary > 6 John > 6 John > 6 John > 6 Jacob > > 6 rows selected. > > PostgreSQL > postgres=# select count(*) over () as ROWCOUNT , first_name from people; > ERROR: syntax error at or near "over" > LINE 1: select count(*) over () as ROWCOUNT , first_name from people... It works, but you should use a recent version: test=*# select count(1) over (), i from foo;count | i -------+---- 8 | 1 8 | 2 8 | 3 8 | 6 8 | 7 8 | 9 8 | 13 8 | 14 (8 rows) test=*# select version(); version --------------------------------------------------------------------------------------------------------PostgreSQL 8.4.2on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit (1 row) test=*# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
<tt><font size="2">Hi,</font></tt><br /><tt><font size="2">>It works, but you should use a recent version:<br /><br />>test=*#select count(1) over (), i from foo;<br />> count | i<br />>-------+----<br />> 8 | 1<br />> 8 | 2<br />> 8 | 3<br />> 8 | 6<br />> 8 | 7<br />> 8 | 9<br />> 8 | 13<br/>> 8 | 14<br />>(8 rows)<br /><br />> test=*# select version();<br />> version<br />> --------------------------------------------------------------------------------------------------------<br/>> PostgreSQL8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real<br />> (Debian 4.3.2-1.1) 4.3.2, 64-bit<br />>(1 row)<br /></font></tt><br /><font face="sans-serif" size="2">Thank you for setting that right. Apologies for notchecking version.</font><br /><font face="sans-serif" size="2">Is this approach better compared to </font><br /><fontface="sans-serif" size="2">postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2;</font><br/><font face="sans-serif" size="2"> count | firstname</font><br /><font face="sans-serif" size="2">-------+-----------</font><br/><font face="sans-serif" size="2"> 5 | Mary</font><br /><font face="sans-serif"size="2"> 5 | Mary</font><br /><font face="sans-serif" size="2"> 5 | John</font><br /><font face="sans-serif"size="2"> 5 | John</font><br /><font face="sans-serif" size="2"> 5 | Jacob</font><br /><font face="sans-serif"size="2">(5 rows)</font><br /><font face="sans-serif" size="2">This gives me</font><br /><font face="sans-serif"size="2">postgres=# explain select * from (select count(*) from people )as p, (select firstname from people)p2;</font><br/><font face="sans-serif" size="2"> QUERY PLAN</font><br /><font face="sans-serif"size="2">---------------------------------------------------------------------</font><br /><font face="sans-serif"size="2"> Nested Loop (cost=14.00..30.42 rows=320 width=226)</font><br /><font face="sans-serif" size="2"> -> Aggregate (cost=14.00..14.01 rows=1 width=0)</font><br /><font face="sans-serif" size="2"> -> Seq Scan on people (cost=0.00..13.20 rows=320 width=0)</font><br /><font face="sans-serif" size="2"> -> SeqScan on people (cost=0.00..13.20 rows=320 width=218)</font><br /><br /><font face="sans-serif" size="2">Since I don'thave 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does sequentialscans/nested loop...(if there are no indexes)</font><br /><br /><font face="sans-serif" size="2">Regards,</font><br/><font face="sans-serif" size="2">Regards,</font><br /><font face="Trebuchet MS" size="2">Jayadevan</font><fontface="sans-serif"></font> <img src="http://www.ibsplc.com/images/email_footer_final.gif" /><font color="#999999" face="Tahoma" size="1"><u> DISCLAIMER:</u></font> <font color="#999999" face="Tahoma" size="1">"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and maycontain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the senderand destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees theaccuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable forany errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."</font><br /><fontsize="1"> </font>
Jayadevan M, 26.03.2010 07:56: > Thank you for setting that right. Apologies for not checking version. The orginal poster stated that he is using 8.4, so that solution will work for him. Thomas
Thanks for all the dialog on this subject. My "version" was derived from the postgreSQL's .jar file (specifically named "postgresql-8.4-701.jdbc4.jar") that I'm using.When I do the following: select version() I get the following: PostgreSQL 8.3.6 I'm going to check this out. Thanks...Jim -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Thomas Kellerer Sent: Friday, March 26, 2010 3:15 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] SQL syntax rowcount value as an extra column in the result set Jayadevan M, 26.03.2010 07:56: > Thank you for setting that right. Apologies for not checking version. The orginal poster stated that he is using 8.4, so that solution will work for him. Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Snyder, James, 29.03.2010 18:25: > Thanks for all the dialog on this subject. > > My "version" was derived from the postgreSQL's .jar file (specifically named "postgresql-8.4-701.jdbc4.jar") that I'm using.When I do the following: > > select version() > > I get the following: > > PostgreSQL 8.3.6 > Then you cannot use the new windowing functions, you will need to upgrade to 8.4 Thomas