How to export to S3 from RDS / Aurora using `aws_s3.query_export_to

Trying to run

 SELECT
     *
 FROM 
 aws_s3.query_export_to_s3(
 'SELECT * FROM <tbl> WHERE <cond>',
 aws_commons.create_s3_uri(
        '<bucket_name>',
        '<file_name>',
        '<region>'
    ),
        options :='format csv, HEADER true, delimiter $$\t$$'
    )
 ;

The custom delimiter specification follows the AWS documentation https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html#postgresql-s3-export-examples-custom-delimiter

However, it fails to export due to ERROR: COPY delimiter must be a single one-byte character The tab delimiter provided in the query complies with the Postgres COPY command.

Any ideas?

CodePudding user response：

you could use E''\t'', it worked, see code below

SELECT * from aws_s3.query_export_to_s3('select * from tb', 
   aws_commons.create_s3_uri('s3-bucket', 'data.csv', 'us-east-1'),  
   options :='format csv, HEADER true, delimiter E''\t'' '
);


 rows_uploaded | files_uploaded | bytes_uploaded 
--------------- ---------------- ----------------
             2 |              1 |             21
(1 row)