Importing CSV Data into a PostgreSQL Table

How to import CSV File Data into PostgreSQL table?

In PostgreSQL, importing data from a CSV file into a table is straightforward using the COPY command or the \COPY meta-command in psql. These methods efficiently load large datasets into PostgreSQL tables with minimal configuration.

Method 1: Using the COPY Command from a File Path

The COPY command is executed within SQL and requires that PostgreSQL has direct access to the file path. This method works well if you are loading the CSV file from the server where PostgreSQL is hosted.

Syntax:

COPY table_name (column1, column2, ...)
FROM '/path/to/your/file.csv'
WITH (FORMAT CSV, HEADER);

Explanation:

• table_name (column1, column2, ...): The target table and its columns where the data will be imported.
• FROM '/path/to/your/file.csv': Specifies the file path to the CSV file.
• WITH (FORMAT CSV, HEADER): Indicates the file format and whether the CSV file includes a header row.

Example Code:

-- Import data from a CSV file into a PostgreSQL table
COPY employees (id, name, department, salary)     -- Target table and columns
FROM '/tmp/employees.csv'   -- File path to CSV file
WITH (FORMAT CSV, HEADER);   -- CSV format and includes header row

Explanation:

• COPY employees (id, name, department, salary): Defines employees as the target table and specifies columns for data mapping.
• FROM '/tmp/employees.csv': Points to the employees.csv file in the /tmp directory.
• WITH (FORMAT CSV, HEADER): Indicates that the file is in CSV format and includes a header row.

Note: This approach requires read access to the file on the server’s filesystem.

Method 2: Using \COPY in psql for Local File Imports

The \COPY meta-command in psql allows you to load a CSV file from your local machine. It reads the file on the client side and then sends the data to the PostgreSQL server, which is helpful when running imports from a remote database.

Syntax:

\COPY table_name (column1, column2, ...)
FROM '/local/path/to/your/file.csv'
WITH (FORMAT CSV, HEADER);

Example Code:

# Connect to the database with psql and run \COPY
psql -U postgres -d my_database -c "\COPY employees (id, name, department, salary) FROM '/local/path/employees.csv' WITH (FORMAT CSV, HEADER);"

Explanation:

• psql -U postgres -d my_database: Connects to PostgreSQL as the postgres user on my_database.
• \COPY employees (id, name, department, salary) FROM '/local/path/employees.csv': Loads data from the CSV file on the client machine.
• WITH (FORMAT CSV, HEADER): Specifies CSV format and that the first row is a header.

Additional Options for COPY and \COPY

• Delimiter: If the file uses a delimiter other than a comma, specify it with DELIMITER.
• COPY employees FROM '/tmp/employees.csv' WITH (FORMAT CSV, DELIMITER ';', HEADER);
• NULL Representation: Define how NULL values appear in the CSV, e.g., NULL 'NULL'.
• COPY employees FROM '/tmp/employees.csv' WITH (FORMAT CSV, HEADER, NULL 'NULL');
• Logging Errors: Log any errors by redirecting error output, useful for large files or critical data loads.

Important Notes:

• File Permissions: Ensure the PostgreSQL user has read permissions for the file path.
• Data Validation: The CSV should match the table structure, including data types, to avoid import errors.
• Encoding: If the file uses a specific encoding, you can specify it with ENCODING, e.g., ENCODING 'UTF8'.