Обсуждение: Changing from NOT NULL to NULL

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?

Thanks,
Rob Mosher

> 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

On Feb 16, 2004, at 4:57 AM, Rob Mosher wrote:

> 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?

<http://www.postgresql.org/docs/current/static/sql-altertable.html>

Michael Glaesemann
grzm myrealbox com

Rob Mosher wrote:
> 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?

ALTER TABLE tab ALTER COLUMN col DROP NOT NULL;

HTH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de

Rob Mosher <mosher@andrews.edu> writes:
> 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?

ALTER TABLE ... DROP NOT NULL does the trick in recent releases.  Before
that, you could resort to manually poking the attnotnull flag in
the pg_attribute row for the field.

            regards, tom lane

I will try this out as soon as I can. Thanks to all who replied.

Rob Mosher

Tom Lane wrote:

>Rob Mosher <mosher@andrews.edu> writes:
>
>
>>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?
>>
>>
>
>ALTER TABLE ... DROP NOT NULL does the trick in recent releases.  Before
>that, you could resort to manually poking the attnotnull flag in
>the pg_attribute row for the field.
>
>            regards, tom lane
>
>