Thread: SQL syntax rowcount value as an extra column in the result set

SQL syntax rowcount value as an extra column in the result set

From
"Snyder, James"
Date:
<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> 

Re: SQL syntax rowcount value as an extra column in the result set

From
Thomas Kellerer
Date:
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



Re: SQL syntax rowcount value as an extra column in the result set

From
"Jayadevan M"
Date:
<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> 

Re: SQL syntax rowcount value as an extra column in the result set

From
"Jayadevan M"
Date:
<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> 

Re: SQL syntax rowcount value as an extra column in the result set

From
"A. Kretschmer"
Date:
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


Re: SQL syntax rowcount value as an extra column in the result set

From
"Jayadevan M"
Date:
<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> 

Re: SQL syntax rowcount value as an extra column in the result set

From
Thomas Kellerer
Date:
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



Re: SQL syntax rowcount value as an extra column in the result set

From
"Snyder, James"
Date:
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

Re: SQL syntax rowcount value as an extra column in the result set

From
Thomas Kellerer
Date:
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