Postgres Pro
Downloads
Home   >   mailing lists

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

Unable to Increase the column which was part of Primary key

From
karthikeyan
Date:
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

3) I was not able to drop the column store_nbr either.

Have anyone encountered the same issue? it is expected or it is Bug?

Can anyone please help me or suggest the best way to increase the size the
column which was/is  the part of Primary key?

Thank you in Advance.

Thanks,
Karthi




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Unable-to-Increase-the-column-which-was-part-of-Primary-key-tp5105943p5105943.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

From
Tom Lane
Date:
karthikeyan <karthi@relationalsolutions.com> writes:
> I am trying to increase size of the column which was/is part of the Primary.
> ...
> Error message:
> ERROR:  xdb-83016: cannot alter scatter column
>        relation "test_dim_store" column "store_nbr"

There is no such error text in the postgresql source code.  I am
guessing that you are using some proprietary derivative of Postgres,
so you would be best advised to consult the support people for that
derivative about what this message means and how to work around it.

            regards, tom lane

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

From
Andreas Kretschmer
Date:
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°