Skip site navigation (1) Skip section navigation (2)

Re: Changing from NOT NULL to NULL

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Cc: mosher(at)andrews(dot)edu
Subject: Re: Changing from NOT NULL to NULL
Date: 2004-02-15 23:56:57
Message-ID: 20040215235657.GA20455@www.sirfsup.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Is there anyway I can change a field to allowing nulls without dumping the table, dropping it, 
> recreating it as desired, and filling all the data back in?

I hope this gets indexed correctly as I hosed the original and tried a cut-n-paste of the subject line,

For that, I have this which I didn't write myself but snagged from somewhere.  Maybe pgsql-general???


-- This function takes a table and column and will set the column
-- to allow NULLs.
--
-- $Id$
--
DROP FUNCTION kl_setnull(name, name);
CREATE FUNCTION kl_setnull(name, name) RETURNS boolean AS '
DECLARE
	tablename ALIAS FOR $1;
	colname ALIAS FOR $2;
	rec_affected int;
BEGIN
	-- If any params are NULL, return NULL - this means function
	-- can be defined isstrict.
	IF tablename IS NULL OR colname IS NULL THEN 
		RETURN NULL;
	END IF;

	-- Lock table with standard ALTER TABLE locks
	EXECUTE ''LOCK TABLE '' || quote_ident(tablename) || '' IN ACCESS EXCLUSIVE MODE'';

	-- Update the system catalogs
	EXECUTE ''UPDATE pg_attribute SET attnotnull = false WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '' || quote_literal(tablename) || '') AND attname = '' || quote_literal(colname);

	-- Get number of rows modified
	GET DIAGNOSTICS rec_affected = ROW_COUNT;

	-- Return number of rows modified
	RETURN (rec_affected = 1);
	
END;
' LANGUAGE 'plpgsql'
WITH (isstrict);
-- 
joe speigle
www.sirfsup.com

pgsql-novice by date

Next:From: Colin GillespieDate: 2004-02-16 10:13:16
Subject: INSERT or COPY: Which one?
Previous:From: Michael GlaesemannDate: 2004-02-15 23:33:20
Subject: Re: Changing from NOT NULL to NULL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group