You can easily import data if you have access to PostgreSQL DB using pgAdmin but if you need to automate the process of importing the CSV file data then you should use psql command line to import the data. This aricle will show how it can be achieved.

Pre-requisite: Install psql, in this example, use below commands for ubuntu OS.

sudo apt-get update
sudo apt-get install postgresql-client

You can use \copy using DB client to import CSV data file. You don’t need to write big scripts. Just take care of 2 points when data is exported from the origin table to be imported later.

  1. When table data is exported make sure the settings are as shown in screen shot. Delimiter, quote and escape is very important when this data is imported in another table in some other DB.

 

2. Also export the script to create the table. Right click on Table–>scripts–>CREATE Script, this will provide the script to create table and indexes and other db objects required fro the table.

Once data is exported properly then we can follow the steps to import it. In this example the data is imported into AWS RDS PostgreSQL from laptop (WSL Ubuntu)-

#Set the RDS host endpoint name
export RDSHOST="test-db-local.cav2retyyoq6l.ap-southeast-2.rds.amazonaws.com"

#use the AWS CLI to get a signed authentication token using the generated-db-auth-token command, and store it in a PGPASSWORD environment variable.
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region ap-southeast-2 --username userAdmin)"

#Use psql command to connect to DB, in this example RDS user is adminUser and the password which was given while creating it
psql "host=$RDSHOST port=5432 dbname=postgres user=adminUser password=dfew243fds32ffdsg43"

#Once connected to postgreSQL using postgres db or the DB where you want to import the table. Run the CREATE TABLE commands to create the table

#Now exit the DB connection (\q command) and run psql command to import the CSV file data
psql -h $RDSHOST -p 5432 -d postgres -U adminUser -W  -c "\copy <TABLE NAME> (colname1,colname2,colname3,colname4,colname5) FROM <path where csv data file is placed> delimiter '|' quote '\"' escape '\' csv header"

 

Note:the order of column exported should be the order of column imported. Else the data will get into wrong columns.

For more details follow the link