Postgresql – Not Null Fields and Unmatched Number of Columns

migrationnullpostgresql

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.

Best Answer

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;
Related Question