Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Altering a table with a rowtype column

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com>
Cc: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Altering a table with a rowtype column
Date: 2012-03-07 17:51:56
Message-ID: CAHyXU0yBCz5t8BqfBK7M9dSWNw4+hvV9BWAizy9ZXhNYxTBAww@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
>
> works for me -- what version are you on?
>
> merlin
>
> ------
>
> [wcs1459(at)aclnx-cisp01 ~]$ psql --version
> psql (PostgreSQL) 9.1.1
> contains support for command-line editing
>
>
> [wcs1459(at)aclnx-cisp01 ~]$ cat x
> create table a (
>   id serial,
>   stuff text,
>   more_stuff text
> );
>
> create table a_audit (
>   id serial,
>   a_old a,
>   a_new a
> );
>
> alter table a add column even_more_stuff boolean not null default false;
>
>
> [wcs1459(at)aclnx-cisp01 ~]$ psql -f x
> psql:x:5: NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for
> serial column "a.id"
> CREATE TABLE
> psql:x:11: NOTICE:  CREATE TABLE will create implicit sequence
> "a_audit_id_seq" for serial column "a_audit.id"
> CREATE TABLE
> psql:x:13: ERROR:  cannot alter table "a" because column "a_audit.a_new"
> uses its row type

aha! that's not what you posted last time.  you appended 'not null
default false'; which inexplicably breaks the ALTER.

try this:
ALTER TABLE a ADD COLUMN even_more_stuff text not null;
ALTER TABLE a ALTER even_more_stuff set default false;
ALTER TABLE a DROP COLUMN even_more_stuff;
ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false;

(this really looks like a bug in postgres, cc-ing to bugs)

merlin

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-03-07 18:46:38
Subject: Re: Extension tracking temp table and causing update failure
Previous:From: Mike BlackwellDate: 2012-03-07 17:46:59
Subject: Re: Altering a table with a rowtype column

pgsql-general by date

Next:From: Andrey ChursinDate: 2012-03-07 17:52:24
Subject: Advisory transaction lock for 128-bit space
Previous:From: Mike BlackwellDate: 2012-03-07 17:46:59
Subject: Re: Altering a table with a rowtype column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group