Postgresql – How to handle csv data in postgres

postgresql

I'm trying to create a simple bi tool similar to Power BI (see here).

You can see in Power BI that it allows the user to upload the CSV files (say with 10 million rows too). Once uploaded we can select a particular column in the csv files in the UI to create visualizations.

I was wondering how the database schema will look like for this particular operation? If the user could able to save 3,4,5 and n columns of csv, how the UI is so quick and response enough to get back the data? Are there any techniques for handling CSV data in Postgres sql?

Kindly help me out.

I'm a newbie to postgres and database in general.

Best Answer

1/ I was wondering how the database schema will look like for this particular operation?

To manage upload file, I use a simple table to save info of those files such as file_name, file_path, upload_date and more. The point here, I don't save those files in PG. I will upload them to other storage (Google Storage) and save path to file_path. For example: 

file_name   file_path                           upload_date
csv_01      www.storage.com/bucket/csv_01       2017-22-22 11:10:00
csv_02      www.storage.com/bucket/csv_02       2017-22-22 11:15:00

2/ If the user could able to save 3,4,5 and n columns of csv, how the UI is so quick and response enough to get back the data?

In terms of app, there are some libraries to handle UI (depends on your language). This app will retrieve CSV info from PG, base on that it will load data from storage and show to user.

3/ Are there any techniques for handling CSV data in Postgres sql?

You can use COPY command to process CSV data. But note that

COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access

Please read this article How to import CSV file data into a PostgreSQL table to know more .

Related Question