Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9090306

Browse files
committed
Add item for changing a column's data type.
1 parent6923ea2 commit9090306

File tree

2 files changed

+31
-9
lines changed

2 files changed

+31
-9
lines changed

‎doc/FAQ

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated:Tue Apr 22 14:02:41 EDT 2003
4+
Last updated:Mon May 26 15:25:04 EDT 2003
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -60,7 +60,8 @@
6060
4.1) What is the difference between binary cursors and normal cursors?
6161
4.2) How do I SELECT only the first few rows of a query?
6262
4.3) How do I get a list of tables or other things I can see in psql?
63-
4.4) How do you remove a column from a table?
63+
4.4) How do you remove a column from a table, or change it's data
64+
type?
6465
4.5) What is the maximum size for a row, a table, and a database?
6566
4.6) How much database disk space is required to store data from a
6667
typical text file?
@@ -644,10 +645,10 @@
644645
the -E option so it will print out the queries it uses to execute the
645646
commands you give.
646647

647-
4.4) How do you remove a column from a table?
648+
4.4) How do you remove a column from a table, or change its data type?
648649

649-
Thisfunctionality was added in release 7.3 with ALTER TABLE DROP
650-
COLUMN. In earlier versions, you can do this:
650+
DROP COLUMNfunctionality was added in release 7.3 with ALTER TABLE
651+
DROPCOLUMN. In earlier versions, you can do this:
651652
BEGIN;
652653
LOCK TABLE old_table;
653654
SELECT ... -- select all columns but the one you want to remove
@@ -657,6 +658,16 @@
657658
ALTER TABLE new_table RENAME TO old_table;
658659
COMMIT;
659660

661+
To change the data type of a column, do this:
662+
BEGIN;
663+
ALTER TABLE tab ADD COLUMN new_col new_data_type;
664+
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
665+
ALTER TABLE DROP COLUMN old_col;
666+
COMMIT;
667+
668+
You might then want to do VACUUM FULL tab to reclaim the disk space
669+
used by the expired rows.
670+
660671
4.5) What is the maximum size for a row, a table, and a database?
661672

662673
These are the limits:

‎doc/src/FAQ/FAQ.html

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
alink="#0000ff">
1111
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1212

13-
<P>Last updated:Tue Apr 22 14:02:41 EDT 2003</P>
13+
<P>Last updated:Mon May 26 15:25:04 EDT 2003</P>
1414

1515
<P>Current maintainer: Bruce Momjian (<Ahref=
1616
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
@@ -87,7 +87,7 @@ <H2 align="center">Operational Questions</H2>
8787
<Ahref="#4.3">4.3</A>) How do I get a list of tables or other
8888
things I can see in<I>psql</I>?<BR>
8989
<Ahref="#4.4">4.4</A>) How do you remove a column from a
90-
table?<BR>
90+
table, or change it's data type?<BR>
9191
<Ahref="#4.5">4.5</A>) What is the maximum size for a row, a
9292
table, and a database?<BR>
9393
<Ahref="#4.6">4.6</A>) How much database disk space is required
@@ -822,9 +822,9 @@ <H4><A name="4.3">4.3</A>) How do I get a list of tables or other
822822
execute the commands you give.</P>
823823

824824
<H4><Aname="4.4">4.4</A>) How do you remove a column from a
825-
table?</H4>
825+
table, or change its data type?</H4>
826826

827-
<P>This functionality was added in release 7.3 with
827+
<P><SMALL>DROP COLUMN</SMALL> functionality was added in release 7.3 with
828828
<SMALL>ALTER TABLE DROP COLUMN</SMALL>. In earlier versions,
829829
you can do this:</P>
830830
<PRE>
@@ -838,6 +838,17 @@ <H4><A name="4.4">4.4</A>) How do you remove a column from a
838838
COMMIT;
839839
</PRE>
840840

841+
<P>To change the data type of a column, do this:</P>
842+
<PRE>
843+
BEGIN;
844+
ALTER TABLE tab ADD COLUMN new_col<i>new_data_type</i>;
845+
UPDATE tab SET new_col = CAST(old_col AS<i>new_data_type</i>);
846+
ALTER TABLE DROP COLUMN old_col;
847+
COMMIT;
848+
</PRE>
849+
<P>You might then want to do<I>VACUUM FULL tab</I> to reclaim the
850+
disk space used by the expired rows.</P>
851+
841852
<H4><Aname="4.5">4.5</A>) What is the maximum size for a row, a
842853
table, and a database?</H4>
843854

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp