How to change a PG column to NULLABLE TRUE?

Before tossing tables: Verify the existing constraints

Before you embark on this journey, it's good to know your enemy - the pesky constraints connected to your column. You fetch this knowledge by consulting the information_schema.columns table where the blueprints of schema dwell:

SELECT is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table'
AND column_name = 'your_column';


If the oracle returns NO, the prophecy declares the presence of the NOT NULL constraint.

The binary NULLability conundrum

Before you decide to change a column to nullable, let's put it under the microscope:

• Data Authenticity: Is this field absolutely necessary? Would allowing NULL equate to opening Pandora's box?
• Logic, dear Watson!: Does welcoming NULL correspond to your application's reasoning?
• Performance: Are you prepared for the different behaviour of indexes on nullable columns?

The Holy Grails' standpoints & caveats

• Data transliteration: Could be welcoming NULL is like inviting a Trojan horse and you end up altering existing precious data? Always, I repeat, always reconnoitre your battleground - audit your columns thoroughly before the action commences.
• Chain of reactions: Nations don't go to war without allies. Similarly, ensure no dependent database features like triggers or functions will sabotage your victory, post-change.
• Safety Visa: If it's within your reach, always house your operations within a transaction, hence providing an easy escape route - the rollback if anything goes BOOM!:

BEGIN;

-- Attention! Code about to change...
ALTER TABLE your_table ALTER COLUMN your_column DROP NOT NULL;

-- Let's have a sneak peek at what we've done...
SELECT * FROM your_table WHERE your_column IS NULL;

-- If satisfied, sign the peace treaty - otherwise, sound the retreat
COMMIT;
-- Or
ROLLBACK;


The Matrix: Visualisation

To paint a better picture of this column nullability switcheroo:

Before (🚧 Behold the wall):
| Column        | Nullability   |
| ------------- | ------------- |
| example_col 🌳 | NOT NULL 🔒   |

After (🚪 Welcome, Passersby):
| Column        | Nullability   |
| ------------- | ------------- |
| example_col 🌳 | NULLABLE 🛅   |

The Big Red Button to open the floodgates:

ALTER TABLE your_table ALTER COLUMN example_col DROP NOT NULL;


Like Neo in The Matrix, be ready to see beyond the code.

The aftermath: Performance implications

• The classic Trojan horse: Changing a nullable column might just be a masquerade for stirring up your existing indexes. Brace yourself and be ready to rebuild or tweak them as the need arises.
• He-Who-Must-Not-Be-VACUUMed: Keep an eye out for signs that Postgres is running a clandestine VACUUM process post-change to tidy up the table or update stats.

Troubleshooting: A cheat sheet

• The name game: To play better, know your opponents better. Get all your constraints in a row, by querying pg_constraints.
• Access Denied: Make sure you command the appropriate clearance level to alter the table schema.
• Occupied Column: If a column is kept busy by active queries or connections, schedule your motions during the night. Well, not exactly, but during slack hours.