1

I am in charge of a task of migrating data from MySQL to PostgreSQL. I have already migrated the MySQL database to PostgreSQL with the help of pgloader. My problem is I need to insert the data from the migrated PostgreSQL to our production PostgreSQL which is using Django as backend. I have mapped the columns for each database and the numbers don't match. We have more columns in our production DB than the one that I migrated.

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.

  • When you say productiondb you mean mysql? – Evan Carroll May 4 '18 at 0:31
  • Production DB uses PostgreSQL. Client will be using our system which uses it. Now I need to migrate their data (originally from MySQL to our system ) – tango ward May 4 '18 at 1:02
  • 1
    Then what does this have to do with MySQL? Anything at all? Or is it just a history lesson? Are you just asking how do you insert data that you don't have (so it's null) into a PostgreSQL column marked as NOT NULL? The answer then is very simple drop the NOT NULL constraint on any columns that are nullable. See my post below. – Evan Carroll May 4 '18 at 1:49
  • Yes, I am playing around with your answer below. Will update soon. – tango ward May 4 '18 at 2:26
  • if the NOT NULL columns that will not be filled have defaults set (and you are not explicitly setting the value to NULL) there will be no problem. – Jasen May 4 '18 at 4:45
0

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.

Remove the NOT NULL constraint on MySQL. You have columns that you don't need -- as evidenced by wanting to do this, and you're declaring them as necessary. There is nothing wrong with null. MySQL views NULL and NOT NULL as attributes of the type, and not constraints on the table. From the docs on the ALTER TABLE Syntax,

ALTER TABLE myTable
  MODIFY myCol INT NULL;

Where INT is the column type.

With PostgreSQL

For comparison, PostgreSQL has the more sane and standardized syntax,

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

Which looks like this,

ALTER TABLE myTable
  ALTER COLUMN myColumn
  DROP NOT NULL;
  • Pardon my ignorance, ALTER TABLE myTable ALTER COLUMN myColumn DROP NOT NULL; does this mean I have to loop through the table's columns and verify if the column will be filled with data from mySQL? If it will not be filled with data, drop it? Sorry – tango ward May 4 '18 at 1:09
  • Or maybe I can check the production DB in PGAdmin and check what are the default value for the fields that are not null and manually put it to the script for the column that I will not be used? – tango ward May 4 '18 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.