On 05/19/10 02:32 AM, Brett Wooldridge wrote:
> Hi All,
>
> I have a table something like this:
>
> CREATE TABLE test (
> id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
> ...
> PRIMARY KEY (id)
> );
>
> I wish to DROP the "GENERATED BY DEFAULT AS IDENTITY" from column 'id'.
> Looking at the various ALTER TABLE forms, I cannot seem to find a way
> to do it.
> There are a lot of other tables with foreign key references to this
> column, so
> dropping and recreating the column doesn't seem viable. Is there a
> supported way
> to do it, or even an unsupported way by manipulating system tables?
Hi Brett,
I don't think it is documented anywhere, but setting the default value
of a column clears its auto-increment property. So this should do the trick:
ALTER TABLE test ALTER COLUMN id SET DEFAULT NULL
--
Knut Anders
