In PostgreSQL, creating a table from a CSV file means importing/loading a CSV file into the Postgres table. In PostgreSQL, creating a table from a CSV file can quickly and easily get your data into a new database. CSV files are commonly used when exchanging data between applications or systems. To achieve this task, PostgreSQL provides a convenient command named “COPY” that allows us to efficiently import/load a huge amount of data from a CSV file into a Postgres table.

This post will use practical examples to explain various methods to create a table from a CSV file. So, let’s start.

Creating a CSV File

Firstly, create a CSV file and specify some data in that file:

The above snippet shows a CSV file named "articles" that is saved at the following location:

“C:\Users\DELL\Desktop”.

Creating a Sample Table

Let’s create a sample table via the below-provided command:

CREATE TABLE articles_tab(
a_id INT PRIMARY KEY,
a_title TEXT,
p_date DATE
);

Execute the “SELECT *” command to verify the table’s creation:

The sample table has been created.

How to Create a Postgres Table From CSV Via COPY Command?

The “COPY” statement reads data from a file or standard input and inserts it into a Postgres table. The COPY command has options to specify the data format, the column names, and the delimiter used in the file.

Once a sample table is created, the next step is to load the data from a CSV file to the sample table. For this purpose, a “COPY” command is used in Postgres. For instance, the below-given command will import the selected CSV file into the “articles_tab” table:

COPY articles_tab(a_id, a_title, p_date)
FROM 'C:\Users\DELL\Desktop\articles.txt'
DELIMITER ','
CSV HEADER;

In the above snippet:

- We utilized the COPY command to load/import the CSV into the PostgreSQL table. For this purpose, we specify the COPY command, Postgres’ table name, and the columns.
- To specify the CSV file’s path, use the FROM clause.
- The delimiter identifies how the values are separated in the targeted CSV file.
- The Header keyword specifies that the selected CSV file contains a header.

The output snippet shows that the “COPY” command was executed successfully. Execute the “SELECT *” command to verify the data insertion of the CSV file into the “articles_tab” table.

The output shows that a Postgres table has been created based on the selected CSV file.

Note: To execute the “COPY” command, you must be a superuser. Moreover, the targeted file must be accessible/readable directly by the Postgres Server.

Conclusion

In PostgreSQL, creating a table from a CSV file means importing/loading a CSV file into the Postgres table. You must execute the COPY command to create a Postgres table via the CSV file. The “COPY” statement reads data from a file or standard input and inserts it into a Postgres table. The COPY command has options to specify the data format, the column names, and the delimiter used in the file. This blog explained how to create a Postgres table from a CSV file via practical examples.