By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Undropping a column?

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Greg Stark <gs*****@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
Greg Stark <gs*****@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
Tom Lane wrote:
Greg Stark <gs*****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.