2

I am very new to using PostgreSQL. I have created a query that compiles a table with more than 100,000 rows and 20 columns of data. The query works and loads the table fine in the PostgreSQL preview window. However, when I click to export the table to CSV in PostgreSQL I get a Runtime application error that says PostgreSQL must automatically close (and I lose everything). I do not get this error when I query and CSV export much smaller tables.

I would appreciate any advice on how to overcome this Runtime application error for this large table.

I do not want to divide my large table into many small tables since this will take too much time and creates rooms for merging multiple tables errors.

Thank you for any help you can provide!

CODE:

SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t 
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5' 
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder
  • Sorry -- should have included that bit. I am using: pgAdmin 4 – Roger Clements Sep 21 '18 at 10:49
  • For exporting large results using psql and the \copy command is probably a better choice (or a different tool that doesn't load the entire result into memory in order to export it) – a_horse_with_no_name Sep 21 '18 at 10:53
  • Thank you for your suggestion. I tried searching for how to use the psql and the /copy command but I cannot figure out to put this in my query. I posted my query above in my original comment. Do you know how I would put in the psql and /copy line to export the data in CSV as you suggest? Thanks again! – Roger Clements Sep 21 '18 at 11:16
  • \copy (select * from some_table) to 'some_file.txt' – a_horse_with_no_name Sep 21 '18 at 11:18
  • I tried this but I get an ERROR: "syntax error at or near "\ – Roger Clements Sep 21 '18 at 11:21
1

This problem is not with postgresql but with the IDE that you are using to manage it - PostgreSQL has nothing to do about that.

You can try to do this with COPY command (different from \copy - because is a psql command)

COPY (
SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t 
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5' 
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder ) 
TO '/tmp/yourfile.txt' delimiter ';'; 

If you are using Windows as a server, then you just need to use a Windows path instead of /tmp.

This is good if you have postgresql in your local machine or have some access in the server that postgresql is stored, then you can also use a path in the file name to put the file in some directory that is shared (via samba, Windows Share or NFS for example) with you or with your PHP/Java system. Guarantee postgresql user has access to the directory -- tmp is world writable because that i used it as a example.

Good lucky!

  • Thank you Luciano! This solution worked perfectly and solved my problem. I really appreciate you taking the time to lend a hand! – Roger Clements Oct 1 '18 at 13:20
  • Please don't forget to select it as the right answer? You will see a confirmation icon close to the down rating icon. – Luciano Andress Martini Oct 1 '18 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.