Re: Unable to Increase the column which was part of Primary key - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: Unable to Increase the column which was part of Primary key
Date
Msg-id 20111229102530.GA8647@tux
Whole thread Raw
In response to Unable to Increase the column which was part of Primary key  (karthikeyan <karthi@relationalsolutions.com>)
List pgsql-general
karthikeyan <karthi@relationalsolutions.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°

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Refine Form of My querry
Next
From: saurabh gupta
Date:
Subject: Postgresql Replication Comparison Required