As mentioned in this article on exporting data to CSV files, CSV files are a format for storing data. They are usually human readable and are useful for data storage. As such, it is important to be able to read data from CSV files and store the data in tables. This can be done in psql with a few commands.
There are a few things to keep in mind when copying data from a csv file to a table before importing the data:
Take this list of items as an example:
This data contains two columns: ‘name’ and ‘price.’ Name appears to be a VARCHAR due to it’s different lengths. Price appears to be MONEY. This will help in creating the table to load the CSV file into.
The first step, as stated before, is to create the table. It must have at least two columns, one a VARCHAR type and the other a MONEY type:
Note: It is also possible to import the csv data to a table with more than 2 columns, however the columns that should be copied to will need to be specified in the query (e.g. COPY items(item, value) FROM...).
Now that a table, ‘items,’ has been created to house the data from the csv file, we can start writing our query. The second step in copying data from CSV is to check the delimiter and the third step is to check for a header. In this case, the delimiter is ‘,’ and there is a header in the file:
Since the header and the delimiter is known, the query can now be written. As before, the syntax is:
So in order to import the csv we will fill out the necessary parts of the query:
So the final query will be:
Running this query will look like:
The message COPY 31 indicates that 31 rows were successfully copied from the CSV file to the specified table.