Thread: CSV export bug?

On 06/29/2018 07:58 AM, Tracy Babiasz wrote:
> Hi there. New pgAdmin user hoping someone out there might help me figure 
> out why I can’t export a particular report.
> 
> I’ve been running a few basic SQL scrips in pgAdmin pre 1.5 successfully 
> and had no trouble exporting them as CSV files. Until one report, which 
> is a bit larger than the others (the results include about 300 rows and 
> 8 columns). The report runs fine, but I can’t export it to save my life. 

Not understanding, are you doing two steps?:

1) Create/run report

2) Export the data from the report.


It will help to see the exact steps you are taking in pgAdmin. In other 
words what parts of pgAdmin are you using and what parameters are you 
supplying it?


> We did upgrade me to 3.1, which is at least allowing me to copy the 
> results and paste them into Excel. But I’d still love to figure out why 
> I can’t export as I can with others.
> 
> I read a bit about this perhaps being a bug in early versions that was 
> resolved, but since I’m now on the latest version and seeing the same 
> thing, either the bug wasn’t resolved or it’s not the same problem.
> 
> The export process seems to work, but the resulting file is only about 
> 1KB and contains 1 of 2 possible statements, depending on whether or not 
> I commented out some info about the initial drop tables.
> 
> if the comments are in, I get:
> 
> ERROR:  syntax error at or near DROP"
> 
>                 
> 
> LINE 1: ...OR /* Drop the temp table if it already exists */ DROP TABLE...
> 
>     
> 
>                                                               ^
> 
>                 
> 
> if the comments are removed, I get:
> 
> ERROR:  syntax error at or near DROP"
> 
>                     
> 
> LINE 1: ...LARE "CURSOR:CONN:372417" CURSOR WITHOUT HOLD FOR DROP TABLE...
> 
>                                                               ^
> 
>                     
> 
> The beginning of this particular report looks like this:
> 
> /* Drop the temp table if it already exists */ DROP TABLE IF EXISTS 
> tmpholds;
> 
> /* This is query is run first and create the temp table populating with 
> bibs or items that have holds */ CREATE TEMP TABLE tmpholds AS SELECT 
> b.id AS "bib_id",
> 
>                  COUNT(DISTINCT h.id) AS "hold_count",
> 
>                  CASE
> 
>                                  WHEN COUNT(DISTINCT i.id) IS NULL THEN 0
> 
>                                  ELSE COUNT(DISTINCT i.id)
> 
>                  END
> 
>                  AS "item_count",
> 
>                                  COUNT(DISTINCT ia.id) AS 
> "avail_item_count",
> 
>                  MAX(o1.order_count) AS "order_count",
> 
>                  STRING_AGG(DISTINCT i.location_code, ' ' ORDER BY 
> i.location_code desc) AS     …and so on and so forth.
> 
> The report RUNS fine, I just can’t export the results, and based on 
> these statements, my best guess is that it’s about the drop table or the 
> comments about the drop table (though I’ve tried removing the comments).
> 
> Anybody know if this is part of a current bug or if it can be worked 
> around?  Many thanks for your help!
> 
> __________________________________________________________
> 
> email
> 
>     
> 
> *Tracy Babiasz*
> Acquisitions and Collections Manager
> Chapel Hill Public Library
> 100 Library Drive   Chapel Hill, NC 27514
> 
> Phone: 919-969-2032 |Fax: 919-968-2838
> 
> tbabiasz@townofchapelhill.org <mailto:tbabiasz@townofchapelhill.org>
> 
> /Sparking Curiosity. Inspiring Learning. Creating Connections.///
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian. Thanks for the response. I'm not sure how else to explain it. The report executes fine in the query tool and
givesme about 300 lines in data output. It even appears to export fine. I click on the download button and open in
Excel,but I get the statements I copied below. The statement is slightly different if I remove the comment statements
withinthe script. If, instead of opening in Excel, I save the file and give it a name, I have a csv file to open. But
thefile is only 1KB, and if you open it, you see the statements I copied below, rather than the results of the report.  

Does that make sense?

________________________________________________________

Tracy Babiasz
Acquisitions and Collections Manager
Chapel Hill Public Library
100 Library Drive   Chapel Hill, NC 27514
Phone: 919-969-2032 |Fax: 919-968-2838
tbabiasz@townofchapelhill.org
Sparking Curiosity. Inspiring Learning. Creating Connections.


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Friday, June 29, 2018 12:15 PM
To: Tracy Babiasz <tbabiasz@townofchapelhill.org>; pgsql-general@lists.postgresql.org
Subject: Re: CSV export bug?

On 06/29/2018 07:58 AM, Tracy Babiasz wrote:
> Hi there. New pgAdmin user hoping someone out there might help me
> figure out why I can't export a particular report.
>
> I've been running a few basic SQL scrips in pgAdmin pre 1.5
> successfully and had no trouble exporting them as CSV files. Until one
> report, which is a bit larger than the others (the results include
> about 300 rows and
> 8 columns). The report runs fine, but I can't export it to save my life.

Not understanding, are you doing two steps?:

1) Create/run report

2) Export the data from the report.


It will help to see the exact steps you are taking in pgAdmin. In other words what parts of pgAdmin are you using and
whatparameters are you supplying it? 


> We did upgrade me to 3.1, which is at least allowing me to copy the
> results and paste them into Excel. But I'd still love to figure out
> why I can't export as I can with others.
>
> I read a bit about this perhaps being a bug in early versions that was
> resolved, but since I'm now on the latest version and seeing the same
> thing, either the bug wasn't resolved or it's not the same problem.
>
> The export process seems to work, but the resulting file is only about
> 1KB and contains 1 of 2 possible statements, depending on whether or
> not I commented out some info about the initial drop tables.
>
> if the comments are in, I get:
>
> ERROR:  syntax error at or near DROP"
>
>
>
> LINE 1: ...OR /* Drop the temp table if it already exists */ DROP TABLE...
>
>
>
>                                                               ^
>
>
>
> if the comments are removed, I get:
>
> ERROR:  syntax error at or near DROP"
>
>
>
> LINE 1: ...LARE "CURSOR:CONN:372417" CURSOR WITHOUT HOLD FOR DROP TABLE...
>
>                                                               ^
>
>
>
> The beginning of this particular report looks like this:
>
> /* Drop the temp table if it already exists */ DROP TABLE IF EXISTS
> tmpholds;
>
> /* This is query is run first and create the temp table populating
> with bibs or items that have holds */ CREATE TEMP TABLE tmpholds AS
> SELECT b.id AS "bib_id",
>
>                  COUNT(DISTINCT h.id) AS "hold_count",
>
>                  CASE
>
>                                  WHEN COUNT(DISTINCT i.id) IS NULL
> THEN 0
>
>                                  ELSE COUNT(DISTINCT i.id)
>
>                  END
>
>                  AS "item_count",
>
>                                  COUNT(DISTINCT ia.id) AS
> "avail_item_count",
>
>                  MAX(o1.order_count) AS "order_count",
>
>                  STRING_AGG(DISTINCT i.location_code, ' ' ORDER BY
> i.location_code desc) AS     .and so on and so forth.
>
> The report RUNS fine, I just can't export the results, and based on
> these statements, my best guess is that it's about the drop table or
> the comments about the drop table (though I've tried removing the comments).
>
> Anybody know if this is part of a current bug or if it can be worked
> around?  Many thanks for your help!
>
> __________________________________________________________
>
> email
>
>
>
> *Tracy Babiasz*
> Acquisitions and Collections Manager
> Chapel Hill Public Library
> 100 Library Drive   Chapel Hill, NC 27514
>
> Phone: 919-969-2032 |Fax: 919-968-2838
>
> tbabiasz@townofchapelhill.org <mailto:tbabiasz@townofchapelhill.org>
>
> /Sparking Curiosity. Inspiring Learning. Creating Connections.///
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/29/2018 09:29 AM, Tracy Babiasz wrote:
> Hi Adrian. Thanks for the response. I'm not sure how else to explain it. The report executes fine in the query tool
andgives me about 300 lines in data output. It even appears to export fine. I click on the download button and open in
Excel,but I get the statements I copied below. The statement is slightly different if I remove the comment statements
withinthe script. If, instead of opening in Excel, I save the file and give it a name, I have a csv file to open. But
thefile is only 1KB, and if you open it, you see the statements I copied below, rather than the results of the report.
 
> 
> Does that make sense?
> 

Well I can replicate it so, yes.

Using pgAdmin4 3.1.

My 'report':

DROP TABLE IF EXISTS tmp_test;
CREATE table tmp_test(id integer, fld_1 varchar);
select * from cell_per;

The error I get when doing the export:

postgres-2018-06-29 12:39:16.349 PDT-0ERROR:  syntax error at or near 
"DROP" at character 55
postgres-2018-06-29 12:39:16.349 PDT-0STATEMENT:  DECLARE 
"CURSOR:CONN:8564986" CURSOR WITHOUT HOLD FOR DROP TABLE IF EXISTS tmp_test;
         CREATE table tmp_test(id integer, fld_1 varchar);
         select * from cell_per;

Looks like pgAdmin4 is running all the queries using a declared 
cursor(https://www.postgresql.org/docs/10/static/sql-declare.html). This 
fails because the CURSOR can only take a SELECT or VALUES as the query. 
The DROP TABLE will not work with a CURSOR.

To me it looks like a bug. I would file an issue here:

https://redmine.postgresql.org/projects/pgadmin4

You will need a Postgres community account to access the issue tracker. 
If you do not have one there will be instructions on how to set one up 
when you click on the link above.

For now what seems to work is to open another tab and then open another 
Query Tool. In that Tool do the DROP TABLE/CREATE TABLE and then in the 
other Tool do the SELECT and export from there.




> Tracy Babiasz
> Acquisitions and Collections Manager
> Chapel Hill Public Library
> 100 Library Drive   Chapel Hill, NC 27514
> Phone: 919-969-2032 |Fax: 919-968-2838
> tbabiasz@townofchapelhill.org
> Sparking Curiosity. Inspiring Learning. Creating Connections.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 06/29/2018 12:53 PM, Adrian Klaver wrote:
> On 06/29/2018 09:29 AM, Tracy Babiasz wrote:
>> Hi Adrian. Thanks for the response. I'm not sure how else to explain 
>> it. The report executes fine in the query tool and gives me about 300 
>> lines in data output. It even appears to export fine. I click on the 
>> download button and open in Excel, but I get the statements I copied 
>> below. The statement is slightly different if I remove the comment 
>> statements within the script. If, instead of opening in Excel, I save 
>> the file and give it a name, I have a csv file to open. But the file 
>> is only 1KB, and if you open it, you see the statements I copied 
>> below, rather than the results of the report.
>>
>> Does that make sense?
>>
> 
> Well I can replicate it so, yes.
> 
> Using pgAdmin4 3.1.
> 
> My 'report':
> 
> DROP TABLE IF EXISTS tmp_test;
> CREATE table tmp_test(id integer, fld_1 varchar);
> select * from cell_per;
> 
> The error I get when doing the export:
> 
> postgres-2018-06-29 12:39:16.349 PDT-0ERROR:  syntax error at or near 
> "DROP" at character 55
> postgres-2018-06-29 12:39:16.349 PDT-0STATEMENT:  DECLARE 
> "CURSOR:CONN:8564986" CURSOR WITHOUT HOLD FOR DROP TABLE IF EXISTS 
> tmp_test;
>          CREATE table tmp_test(id integer, fld_1 varchar);
>          select * from cell_per;
> 
> Looks like pgAdmin4 is running all the queries using a declared 
> cursor(https://www.postgresql.org/docs/10/static/sql-declare.html). This 
> fails because the CURSOR can only take a SELECT or VALUES as the query. 
> The DROP TABLE will not work with a CURSOR.
> 
> To me it looks like a bug. I would file an issue here:
> 
> https://redmine.postgresql.org/projects/pgadmin4
> 
> You will need a Postgres community account to access the issue tracker. 
> If you do not have one there will be instructions on how to set one up 
> when you click on the link above.
> 
> For now what seems to work is to open another tab and then open another 
> Query Tool. In that Tool do the DROP TABLE/CREATE TABLE and then in the 
> other Tool do the SELECT and export from there.

After I posted the above I wondered how that could be as they are 
different sessions and we are dealing with a temporary table. Then I 
realized my example did not use a temporary table. So if you are going 
to use the above then you will need to use a 'permanent' table.

> 
> 
> 
> 
>> Tracy Babiasz
>> Acquisitions and Collections Manager
>> Chapel Hill Public Library
>> 100 Library Drive   Chapel Hill, NC 27514
>> Phone: 919-969-2032 |Fax: 919-968-2838
>> tbabiasz@townofchapelhill.org
>> Sparking Curiosity. Inspiring Learning. Creating Connections.
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com