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

Re: changing column data types

From: ERIC Lawson - x52010 <eric(at)bioeng(dot)washington(dot)edu>
To: Derek <derek(dot)mailer(at)virgin(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: changing column data types
Date: 2001-02-21 16:50:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novicepgsql-sql
What command did psql complain about, and what was psql's complaint
(supposing you're using psql)?

The general procedure, AFAIK, if you don't care about getting rid of the
old column, is to use ALTER TABLE tablename ADD COLUMN columnname
DATATYPE, then UPDATE tablename SET newcolumn=oldcolumn WHERE whatever,
'casting' the data from the old column as needed and appropriate.

This seems comparatively painless to me.  It gets more painful when the
lack of an ALTER TABLE tablename DROP COLUMN columnname is felt, but even
then, using pg_dump -s along with a text editor ain't no more than a
mosquito bite.

On Wed, 21 Feb 2001, Derek wrote:

> Excuse my ignorance, but I'm used to MySQL and I'm having problems trying to
> convert a few columns from varchar to numeric.  I attempted to get around
> the problem by creating a new numeric column, but pgsql didn't like it when
> I tried to copy the varchar column info into the numeric one.
> Is there a painless way of changing a column type?
> Thanks in advance for any help offered
> Derek


James Eric Lawson
Research Publications Editor III
National Simulation Resource


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Everyday language is a part of the human organism and is no less 
complicated than it. - Ludwig Wittgenstein (1889-1951) [Tractatus 
Logico-Philosophicus, 1921]

In response to

pgsql-novice by date

Next:From: ERIC Lawson - x52010Date: 2001-02-21 17:05:23
Subject: Re: copying tables
Previous:From: DerekDate: 2001-02-21 15:45:03
Subject: changing column data types

pgsql-sql by date

Next:From: Jeff MacDonaldDate: 2001-02-21 17:39:04
Subject: pl/Perl
Previous:From: Josh BerkusDate: 2001-02-21 16:42:07
Subject: two tables - foreign keys referring to each other...]

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