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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Colin Gillespie | 2004-02-16 10:13:16 | INSERT or COPY: Which one? |
Previous Message | Michael Glaesemann | 2004-02-15 23:33:20 | Re: Changing from NOT NULL to NULL |