Import data from one PostgresSQL database to another with constraints

Question

I have two databases and two apps. Initially they had same data. Later on, it has changed. Now I want to import some data from one database to another, but getting constraint restrictions. I thought I will just delete with sql command first from one, but then I get constrain restrictions again with multiple other tables.

Tables that I want to move are like this Manufacturers has many Systems has many Products. So let's say Apple has Iphone10 and Iphone11 systems, and each of them has many parts. And they were used in other parts of application like orders.

Fields of the tables are not exactly the same.

So how could I delete data from those three tables in one database, and then import new data?

*The app is not in production

score 0 · Answer 1 · answered Dec 20 '21 at 15:57

0

To delete all data from a table or set of tables, you can use the truncate command.

TRUNCATE othertable CASCADE;

This specific command will cascade the truncate to tables with a foreign key restraint.

https://www.postgresql.org/docs/current/sql-truncate.html

To import data, you can import from a CSV file

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);

How to import CSV file data into a PostgreSQL table?

answered Dec 20 '21 at 15:57

David Weber

154
6

#David Weber If I dont use cascade parameter it will remove rows only from the one table? – Tomas Am Dec 20 '21 at 16:19
Yes, if you would like to remove from just certain tables just list them like this: ```TRUNCATE bigtable, fattable;``` – David Weber Dec 20 '21 at 16:25

Import data from one PostgresSQL database to another with constraints

1 Answers1