Thread: alter table add column is broken in 6.5

alter table add column is broken in 6.5

From
"Bryan White"
Date:
I posted this yesterday and got no response, so I will try again.

Alter table add column does not set the default value for new rows added to
a table.  I can except that it does not copy the new default value for
existing rows. That is easy to work around.  My problem is that the database
does not reflect the default value for new rows added after the alter
statement.

I could work around this is someone could tell me how to modify the system
tables to specify a default value.  This does not seem to be much
documentation for the layout of the system tables.

Bryan White
ArcaMax Inc.
Yorktown VA
www.arcamax.com


Re: [GENERAL] alter table add column is broken in 6.5

From
"Dan Wilson"
Date:
It's not too practical to insert a new row into the system table that
handles the defaults, but here it goes.  The table you are looking for is
pg_attrdef and contains the following columns.

adrelid -- The relation id for the table of the default value
adnum -- The column number of the default column
adbin -- The source (what returns the default value when a row is inserted)
adsrc -- The actual default value that you give postgres (ie nextval
('sequence'))

If you have another default that is exactly the same then I think it is
possible.  You should figure out the relid of the table you are working on
and the column number is not difficult.

To obtain the table relid run this query:

select relname, oid from pg_class where relname = '<name of your table>'

The oid is the value for which you are looking. Then the column (adnum) id
is just the in order column number when you do a \d <tablename>. Then be
sure to copy the adbin and adsrc from an identical default.

Hopefully that helped.

I have never tried this and so if any of you know that this won't work or if
you know that I have made some mistakes in my judgement, then please let me
know.  I am a fairly new beginner at postgres (about 2 months).

-Dan Wilson

Bryan White wrote:
> I posted this yesterday and got no response, so I will try again.
>
> Alter table add column does not set the default value for new rows added
to
> a table.  I can except that it does not copy the new default value for
> existing rows. That is easy to work around.  My problem is that the
database
> does not reflect the default value for new rows added after the alter
> statement.
>
> I could work around this is someone could tell me how to modify the system
> tables to specify a default value.  This does not seem to be much
> documentation for the layout of the system tables.
>
> Bryan White
> ArcaMax Inc.
> Yorktown VA
> www.arcamax.com