How to import S3 data into Postgres, easily

23 February, 2024
by Mark Teisman

A neat trick used during one of the AWS trainings, which allows you to itake e.g. a CSV file stored in S3, and turn it into a table in Postgres.

The function is called table_import_from_s3 and the API is as follows.

aws_s3.table_import_from_s3 (
   table_name text, 
   column_list text, 
   options text, 
   s3_info aws_commons._s3_uri_1
) 

Here's the actual code I executed in the training.

CREATE EXTENSION aws_commons;
CREATE EXTENSION aws_s3;
SELECT aws_s3.table_import_from_s3(
    'Venues', # Table name
    '', # Column list
    '(format csv, header true)', # Options
    aws_commons.create_s3_uri('labstack-a07462a6-a926-429a-be96-499-labdatabucket-mvipkqemdwvd', 'InitialVenues.csv', 'us-east-2') # URL
);