CODEWITHSUNDEEP
postgresqlplpgsql
manish1706
manish1706

Reputation: 1599

How can i Alter TABLE all column from not null to null by one query of PostgreSQL 9.1

I migrate my data from MySQL Database to PostgreSQL Database and by mistaken i have all my column set not-null in PostgreSQL database.

After this i am facing issue for inserting data and have to uncheck not null manually, so is there any way to do for all column in table ( except id(PRIMARY KEY) ).

i have this query also for single column but its also time consuming,

ALTER TABLE <table name> ALTER COLUMN <column name> DROP NOT NULL;

Upvotes: 0

Views: 342

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Just perform a new CREATE TABLE noNULL using the same structure as your current table and modify the NULLable field you need.

Then insert from old table

INSERT INTO noNULL 
      SELECT *
      FROM oldTable

then delete oldTable and rename noNull -> oldTable

Upvotes: 0

Ildar Musin
Ildar Musin

Reputation: 1468

I don't think there is built in functionality for this. But it's easy to write a function to do this. For example:

CREATE OR REPLACE FUNCTION set_nullable(relation TEXT)
RETURNS VOID AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (SELECT * FROM pg_attribute
                WHERE attnotnull = TRUE AND attrelid=relation::regclass::oid AND attnum > 0 AND attname != 'id')
    LOOP
        EXECUTE format('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL', relation, rec.attname);
        RAISE NOTICE 'Set %.% nullable', relation, rec.attname;
    END LOOP;
END
$$
LANGUAGE plpgsql;

Use it like this:

SELECT set_nullable('my_table');

Upvotes: 1

Related Questions