How to Import and Export data from PostgreSQL

I am dealing with a Azure Database for PostgreSQL now and a few days ago needed to export a database table with millions of records and import them in another table. As a person coming from Microsoft SQLServer world, I though it must have been a trivial work but in the end spent a couple of hours to figure out how to do that.

I am using PgAdmin 4 as a client to manage the Postgre databases. Exporting a small table is very easy task with the tool but for larger ones it just generated an empty file with no data inside!

I looked the issue up in internet and found a lot of hacks and tips and tricks to make it work but ended up with nothing. Later I noticed it's a very typical issue in PgAdmin 4 (v 4.17.0). Nevertheless I prepared a script for the same purpose and will share it here.

First jump to the installation folder of PgAdmin4 command tools. In my Mac book it's located under the following path:

/Applications/pgAdmin 4.app/Contents/SharedSupport

How to export

Here is the script to export the data from a table called tableName to a CSV comma separated file.

./psql -h "my-postgresql-server.postgres.database.azure.com" -p "5432" -U "postgre_user@my-postgresql-server" -d "databaseName" -c "\COPY (SELECT * FROM tableName) TO /Users/morteza/file.csv DELIMITER ',' CSV"

Well, I think it's pretty self explanatory but here is command's details:

-h Postgre server
-p Port
-U database username
-d database name
-c command(select all the rows from the table)
TO local csv file address on the disk

After running the command, you will be prompted to enter the database password.

How to import

How to import data from the csv file into newTableName table.

./psql -h "my-postgresql-server.postgres.postgres.database.azure.com" -p "5432" -U "postgre_user@my-postgresql-server" -d "databaseName" -c "\COPY newTableName FROM /Users/morteza/file.csv DELIMITER ',' CSV"