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

Re: schema rename sequence issue

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: schema rename sequence issue
Date: 2006-01-30 10:24:01
Message-ID: 11476882.20060130122401@compulab.co.il (view raw or flat)
Thread:
Lists: pgsql-general
Thank you for your thoughts, but I am hoping that I don't have to
update 400 tables manually.

The way I understand the postgresql structure is that each field is
listed in the pg_attribute table and those fields with default values
have records in the pg_attrdef table. I changed both the text and the
binary to reflect the new schema and using PGAdmin3, everything
"looks" correct. So when I click on the table it tells me the sequence
is in the correct schema.

Unfortunately, Insert doesn't work.

Thank You
Sim
________________________________________________________________________________


On Jan 30, 2006, at 19:03 , Sim Zacks wrote:

> I thought the pg_attrdef table was the defaults.
> Is there another table that contains the defaults

What I'm thinking of is this situation:

create table foo
(
        foo_id serial primary key
        , foo_value text not null unique
);

This will create a sequence (public.foo_id_key_seq, I think) as well  
as a table
public.foo
(
        foo_id integer primary key default nextval('public.foo_id_key_seq')
        , foo_value text not null unique
);

(And some indexes as well, but we'll set those aside for now.)

You've renamed the schema, but I think you need to update the default  
for the foo_id column manually, e.g.,

alter table stock.foo alter column foo_id set default = nextval 
('stock.foo_id_key_seq');

Syntax not checked.

If this isn't what it is, perhaps someone else has an idea.

Michael Glaesemann
grzm myrealbox com


In response to

Responses

pgsql-general by date

Next:From: Alban HertroysDate: 2006-01-30 10:53:28
Subject: Re: Are rules transaction safe?
Previous:From: Michael GlaesemannDate: 2006-01-30 10:11:21
Subject: Re: schema rename sequence issue

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