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;
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');
pg_attribute; always check the attisdropped flag.
attnotnull to false so this function still working : ) But thanks for a tip, I didn't know that!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
SELECT INTO is you cant set the table properties like types, constrains, autonumeric. So I rather create the table first. Maybe you can elaborate why is discouraged?a_horse_whiit_no_name? The idea is create the table with the same script you have the current table. Select INTO doesnt do thatCREATE TABLE script, you may still need to deal with dependent views and referencing foreign keys. And probably several other things I haven't thought of. Much simpler and safer to alter the existing table.then delete oldTable and rename noNull -> oldTable I agree alter the table should be better but OP complain about timing, and In my experience with postgres this aproach is much faster. Of course in a production enviroment maybe wont be viable.