Postgresql - Foreign Key after import

Question

I have a database in Firebird and I am passing the data it to Postgresql, so I made an application that reads the data from Firebird and inserts it into Postgresql.

The database is from a supermarket but has little time of use, the Bank on FB weighs just over 1Gb, is not such a small bank, but is far from being large.

Well, in the application I mentioned above, I create the database in Postgresql and soon after I do the import load are basically Inserts, kick ai about 3 million records, nothing absurd.

However, after inserting,if I try to remove a product that already has sales the bank accepts, do not check the FK... but if I drop the FK and recreate there everything works perfectly... And if I do a backup/Restore too, everything goes back to the most perfect tuning...

I believe the database is corrupting or something in the import, the application is done in C# and use Npgsql, and the Inserts are made via functions that run crud.

The question is, is this a Postgresql problem? I’m really almost giving up the bank, despite all the work I’ve had to write a bank of almost 150 tables on the nail to migrate I’m not feeling safe in the same... and look I’m coming Firebird...

Answer 1

I have worked with Firebird and Postgres for some time, and the basis of my work is to perform data extraction from Firebird databases, transform them and insert into Postgres bases.

About your problem, it may be related to users/permissions. If you create tables like postgres in an X user schema and der Grant all for user X, you can enter data without FK being respected. Source.

Postgres is a foundation with many configuration details, especially when compared to Firebird. But I love them both. Another detail that I’m sure will help you a lot. Use a data extraction tool. Here we use the Spoon of Pentaho (name modou for Hitachi Vantara). Link.