Thread: beginner Table data type question

beginner Table data type question

From
Richard Smith
Date:
Hi, I just start using pgsql and I am new to SQL also.  I have read lots
of
documents on this and I cant seem to find the answer, perhaps you can
help me.

Say I have a table:

pgsql=> \d test_table

test_table
------------------------------
f1| CHAR (40)
f2| CHAR (40)

Somewhere down the line I need to change the data type on f2 to CHAR
(50) and add constrains like UNIQUE.  What is the best was to change
tables in this way?
Can I just make a new table the way I want and COPY the data out of the
old table and COPY it back in?  do I use CAST ?  Is there some ALTER
TABLE tablename MODIFY command I can use?

This question has been bugging me for awhile. Because I am new to SQL I
find that I make bad design choices about data types and such all the
time. So I need to know the best way to change them.

Richard

Re: beginner Table data type question

From
davidb@vectormath.com
Date:
Richard,

For constraints see:
http://www.postgresql.org/doxlist.html
then select Documentation
then select Integrated Document
then select Alter Table

For Column definitions:
I believe some other implementations of SQL allow you to alter
columns as long as you are keeping the same basic datatype and
increasing the size, but require a copy-drop-add-copy if you are
changing the basic datatype or decreasing the size.
Postgres requires a copy-drop-add-copy for any change to column
definitions.

David Boerwinkle

-----Original Message-----
From: Richard Smith <ozric@tampabay.rr.com>
To: pgsql-general@postgresql.org.pgsql-questions@postgreSQL.org
<pgsql-general@postgresql.org.pgsql-questions@postgreSQL.org>
Date: Friday, May 19, 2000 11:12 AM
Subject: [GENERAL] beginner Table data type question


>Hi, I just start using pgsql and I am new to SQL also.  I have read lots
>of
>documents on this and I cant seem to find the answer, perhaps you can
>help me.
>
>Say I have a table:
>
>pgsql=> \d test_table
>
>test_table
>------------------------------
>f1| CHAR (40)
>f2| CHAR (40)
>
>Somewhere down the line I need to change the data type on f2 to CHAR
>(50) and add constrains like UNIQUE.  What is the best was to change
>tables in this way?
>Can I just make a new table the way I want and COPY the data out of the
>old table and COPY it back in?  do I use CAST ?  Is there some ALTER
>TABLE tablename MODIFY command I can use?
>
>This question has been bugging me for awhile. Because I am new to SQL I
>find that I make bad design choices about data types and such all the
>time. So I need to know the best way to change them.
>
>Richard