Re: Unable to Increase the column which was part of Primary key

karthikeyan <karthi(at)relationalsolutions(dot)com> wrote:

> Hi,
>
> I am trying to increase size of the column which was/is part of the Primary.
>
> Exiting table
> CREATE TABLE test_dim_store
> (
> storek integer NOT NULL DEFAULT
> nextval('dim_store_storek_seq1'::regclass),
> retailerk character varying(10) NOT NULL,
> store_nbr character varying(5) NOT NULL,
> store_name character varying(25) NOT NULL,
> CONSTRAINT test_dim_store_pkey PRIMARY KEY (retailerk, store_nbr)
> )
> WITHOUT OIDS;
>
> I am trying to increase size of the column - store_nbr (which was part of
> the Primary) from Varchar(5) to varchar(10).
>
> I tried the following :
> 1) drop the Primary key - ALTER TABLE test_dim_store DROP CONSTRAINT
> test_dim_store_pkey ;
> 2) Increase the size of Column - store_nbr - ALTER TABLE test_dim_store
> ALTER COLUMN store_nbr TYPE varchar(10);
>
> Error message:
>
>
> ERROR: xdb-83016: cannot alter scatter column
> relation "test_dim_store" column "store_nbr"
>
> ********** Error **********
>
> ERROR: xdb-83016: cannot alter scatter column
> relation "test_dim_store" column "store_nbr"
> SQL state: 42809

works for me:

test=# create sequence dim_store_storek_seq1;
CREATE SEQUENCE
Time: 1,029 ms
test=*# CREATE TABLE test_dim_store
test-# (
test(# storek integer NOT NULL DEFAULT
test(# nextval('dim_store_storek_seq1'::regclass),
test(# retailerk character varying(10) NOT NULL,
test(# store_nbr character varying(5) NOT NULL,
test(# store_name character varying(25) NOT NULL,
test(# CONSTRAINT test_dim_store_pkey PRIMARY KEY (retailerk,
store_nbr)
test(# )
test-# WITHOUT OIDS;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_dim_store_pkey" for table "test_dim_store"
CREATE TABLE
Time: 83,074 ms
test=*# alter table test_dim_store alter column store_nbr type
varchar(10);
ALTER TABLE
Time: 19,384 ms

I'm using 9.1.2.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°