Companies regularly face the complexity of importing and exporting data to PostgreSQL. As a company expands its business, its branches often need to update local access databases with data coming from the central PostgreSQL server. From time to time, this data needs to be exported in CSV or XML formats.
Another use of data extracted from PostgreSQL is to create ready-made HTML pages, spreadsheets, presentations, or text documents that are used in the company. All this makes the problem of importing and exporting data from this DBMS extremely relevant for entrepreneurs. Let's consider how to properly import and export databases in PostgreSQL.
Importing a database into PostgreSQL
You can import data into PostgreSQL in several formats: from the database itself (PL/pgSQL dialect) and from .csv, which is also often used to store datasets. Technically, you can also work with the popular .json, but it is easier to extract values from it and use them in queries in Python rather than SQL.
In format in PostgreSQL
It is recommended to import data into PostgreSQL from an SQL file as follows.
1. Download the demo_1 database file to the required directory, e.g. C:\Users\User-N\Desktop\Database.
2. Run the Cmd.exe terminal in Windows or the SQL shell console client (psql). Run the command cd C:\Users\User-N\Desktop\ Now navigate to the directory with the database given.
3. Load the database from the SQL file using the bash command. To do this, type the path to the PostgreSQL directory, the user name (-U), and the database name (-f) in the terminal: "C:\Program Files\PostgreSQL\10\bin\psql" -U postgres -f demo_1.sql
4. Note that the larger the database, the longer the download may take. After successful completion, the line C:\User-N\Desktop\DB> will appear in the console.
5. Now, if you go to pgAdmin, the graphical PostgreSQL client, you can see the imported database with all the tables.
From a .csv file
● If you already have a dataset file in .csv format, go to pgAdmin and either use one of the existing databases or create a new one (in our example it will be demo_1 again).
● In this database, you need to create a table whose field types will match the "columns" in the .csv file from which the data is being imported.
● Now open the SQL shell (psql) console, which will ask for the server name, port, user, and the database you plan to connect to - you will only be required to enter the user password, demo-1 and press Enter. The connection to the database is established.
● To start importing data from the .csv file into table_1, use the command: \COPY table_1 FROM 'C:\Users\User-N\Desktop\CSV\demo_1_MOW.csv' DELIMITER ',' CSV HEADER;
● In this command:
➡️ table_1 is the name of the table in the database.
➡️ FROM - the path to the .csv file.
➡️ DELIMITER ',' - the delimiter in the .csv file (in this case, a comma).
➡️ CSV HEADER - A pointer that the first line in the .csv file contains the column headers.
● After running the command, verify that the file import into PostgreSQL was successful by going to pgAdmin and checking the data in the table.
Exporting the database to PostgreSQL
Sometimes it is necessary to export data from PostgreSQL to a .csv file. It can be a whole table or some data that meets certain conditions. Let's consider both of these options.
Exporting data to .csv format
● If the table_1 table from the demo_1 database is to be migrated in its entirety, you need to connect to the latter via the SQL shell (psql), following the scheme described in the previous section.
● Now correctly enter the command \COPY table_1 TO 'C:\Users\User-N\Desktop\CSV\table.csv' DELIMITER ',' CSV HEADER in the console This command will copy the data from table_1 to the specified .csv file.
● Export can also be done from pgAdmin: the export function is available by right-clicking on table_1 in the table list.
Exporting sample data to a .csv file
To export only certain fields (e.g. table_1_id and table_1_age) where table_1_age = '18' from table_1, you can use the following SQL query:
\COPY (SELECT table_1_id, table_1_age FROM table_1 WHERE table_1_age = '18') TO 'C:\Users\User-N\Desktop\CSV\table_1.csv' CSV HEADER DELIMITER DELIMITER ',';
```
In this query:
● table_1_id, table_1_age are the selected fields to export.
● table_1 is the name of the table from which the data is selected.
● table_1_age = '18' - condition for data selection.
● TO - path to the .csv file where the data will be uploaded.
● CSV HEADER DELIMITER ',' - pointer to .csv format, presence of headers, and use of the comma as a separator.
After executing this SQL query in SQL shell (psql), the data will be exported to the specified .csv file. Congratulations, the PostgreSQL export is complete.
When do we use import/export in PostgreSQL?
Data import and export play an important role in businesses dealing with large amounts of customer information. These functions help to easily manage and update data and ensure the integrity of data storage. In addition, the ability to automatically replace text fragments or use constants when importing data into PostgreSQL gives you additional opportunities to improve your data processes.
You, as an entrepreneur, are provided with our expert PostgreSQL support. We can help with:
● Data migration: migrating data from different sources into PostgreSQL. This will ensure data security and accuracy during the migration process.
● Data Integrity Auditing: checking and ensuring data integrity. This is critical for retail, online shops, banks, and telecom services that handle sensitive customer data.
This support will enable you to manage your customer data effectively, ensuring it is stored and used securely. We are here to help you with any PostgreSQL-related issues so you can focus on your business with peace of mind!
