1

I have a sql script with intend to export data from postgres and spool into a csv file which used to work fine until I added random sample into the line.

Here is the code it look like with random()

\COPY (select accountid, to_char(createtime,'YYYY-MM-DD HH24:MI:SS.ms') from accounts random()< 0.01 limit 1000) to '/home/oracle/scripts/accounts_p.csv' WITH DELIMITER ',' NULL AS ' '

ERROR MESSAGE when running this sql script:

psql:accounts_sample_p.sql:1: \copy: ERROR:  syntax error at or near ")"
LINE 1: ...from accounts random ( ) < 0.01 l...

                              ^

Appearantly it did not like the (). Tried using escape character with \ before ( and before ), but it did not help.

Can anyone give me an advice on how to overcome this? Thanks.

anubhava
  • 761,203
  • 64
  • 569
  • 643
Cat
  • 15
  • 2

1 Answers1

0

you seem to be missing a 'WHERE' in your SELECT statement. i.e.:

select accountid, to_char(createtime,'YYYY-MM-DD HH24:MI:SS.ms') from WHERE accounts random()< 0.01 limit 1000

Alternative methods on selecting a random set in PostgreSQL can be found here if you're struggling with performance:

Best way to select random rows PostgreSQL

Community
  • 1
  • 1
unfinishedmonkey
  • 76
  • 5