Thread: Undropping a column?

I just dropped a column that I wish I hadn't. Is there some simple update i
could do to the pg_* tables that would undrop it? I haven't done any other
updates though autovacuum may have run.

Even if I just manage to extract the data then have to restore from a backup
to get an uncorrupted data dictionary and reload the data it might be useful.

thanks.

--
greg

Greg Stark <gsstark@MIT.EDU> writes:

> I just dropped a column that I wish I hadn't. Is there some simple update i
> could do to the pg_* tables that would undrop it? I haven't done any other
> updates though autovacuum may have run.

Ok, I seem to have done it with this:

update pg_attribute set attisdropped = 'f',atttypid= 25
 where attrelid = 17839 and attname = '........pg.dropped.9........';

I've now updated the table to copy this data over to the new column and
redropped the old column. Have I messed up my database?

--
greg

Greg Stark <gsstark@mit.edu> writes:
> I just dropped a column that I wish I hadn't. Is there some simple update i
> could do to the pg_* tables that would undrop it?

Lessee ... unset 'attisdropped', put attname back the way you want,
restore atttype to the right thing.  You'll need to restore any
constraints, default value, etc as well.

            regards, tom lane

Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
>
>>I just dropped a column that I wish I hadn't. Is there some simple update i
>>could do to the pg_* tables that would undrop it?

You could rsync the database back from your live off-site backup, or
rifle through the piles of backups to recover from last night's archive
backup :)

Another alternative, if it's just the schema not the data you need back,
is simply to "alter table add column ..." the column back.  Note that if
you want it to be "not null" or "default x" you have to add the column,
update the table with the default value in each column, alter the column
to be not null then alter the column to be default x.

Though this probably isn't what you were looking for, it's still on the
archives for someone else to stumble across c/o Google

Alex