pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ALTER TABLE - ALTER COLUMN question
3+ messages / 2 participants
[nested] [flat]

* ALTER TABLE - ALTER COLUMN question
@ 2007-05-29 20:42  Keaton Adams <kadams@mxlogic.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Keaton Adams @ 2007-05-29 20:42 UTC (permalink / raw)
  To: pgsql-admin

When I issue an alter table ..... alter column command, the table
contents are copied from the old structure to the new, correct?  So I
need 2x the table space available so the old and new tables can exist at
the same time?  PostgreSQL doesn't use an in-place alter-table for these
types of changes, correct?  

In this example the created and msg_datetime columns are currently
defined as TIMESTAMP WITHOUT TIME ZONE and no default.  If the alter
table for these changes is not in-place then I would want to run it as a
single alter with all of the changes, such as this, instead of
individual alters for each change, correct?  (Because each individual
alter would require another table copy?)

ALTER TABLE my_table ALTER COLUMN created TYPE TIMESTAMP WITH TIME
ZONE, 
  ALTER COLUMN created SET DEFAULT   ('now'::text)::TIMESTAMP(6) WITH
TIME ZONE, 
  ALTER COLUMN msg_datetime TYPE TIMESTAMP WITH TIME ZONE, 
  ALTER COLUMN msg_datetime SET DEFAULT ('now'::text)::TIMESTAMP(6) WITH
TIME ZONE;

Thanks,

Keaton

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ALTER TABLE - ALTER COLUMN question
@ 2007-05-29 21:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Keaton Adams <kadams@mxlogic.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2007-05-29 21:16 UTC (permalink / raw)
  To: kadams@mxlogic.com; +Cc: pgsql-admin

Keaton Adams <kadams@mxlogic.com> writes:
> In this example the created and msg_datetime columns are currently
> defined as TIMESTAMP WITHOUT TIME ZONE and no default.  If the alter
> table for these changes is not in-place then I would want to run it as a
> single alter with all of the changes, such as this, instead of
> individual alters for each change, correct?  (Because each individual
> alter would require another table copy?)

That's why ALTER TABLE has the ability to do more than one thing in
parallel ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ALTER TABLE - ALTER COLUMN question
@ 2007-05-29 21:19  Keaton Adams <kadams@mxlogic.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Keaton Adams @ 2007-05-29 21:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

After I sent my request I found the notes related to ALTER TABLE on page
1,331 of the 8.1 manual, so that helped to explain it.  Your reply to my
post confirmed what I thought I was reading.

Thanks for the reply,

Keaton



On Tue, 2007-05-29 at 17:16 -0400, Tom Lane wrote:

> Keaton Adams <kadams@mxlogic.com> writes:
> > In this example the created and msg_datetime columns are currently
> > defined as TIMESTAMP WITHOUT TIME ZONE and no default.  If the alter
> > table for these changes is not in-place then I would want to run it as a
> > single alter with all of the changes, such as this, instead of
> > individual alters for each change, correct?  (Because each individual
> > alter would require another table copy?)
> 
> That's why ALTER TABLE has the ability to do more than one thing in
> parallel ...
> 
> 			regards, tom lane

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-05-29 21:19 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-29 20:42 ALTER TABLE - ALTER COLUMN question Keaton Adams <kadams@mxlogic.com>
2007-05-29 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-29 21:19   ` Keaton Adams <kadams@mxlogic.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox