Need to do an ALTER TABLE.
Hi,
I've got a live database running PSQL 7.0.3. I need to do a couple of
changes to some of the table schema's but need to preserve the data
that currently exists in the tables. I've used pg_dump to make
backups and have verified that I can indeed restore into an empty
database from those backups; I'm glad I did this as I found out I have
to use 'pg_dump -d' to get a useful backup. =)
I need to perform three slightly different ALTER TABLE type jobs:
1. I need to change a column from numeric(8,2) to text.
2. I need to add a column or two to a couple of tables.
3. I need to drop a column or two from a couple of tables.
Initially my thought was to create a new temporary table, SELECT INTO
it from my original table, drop the original table, re-create it as I
need it and do a SELECT INTO from the temporary table back to the new
table. The thing I'm unsure of is what will happen to referential
integrity? The tables I need to modify are referenced by other
tables- will those other tables realise that they should re-establish
foreign key references? If not automatically, will VACUUM ANALYZE do
this for me?
I'm going to experiment in my test database but figured this might be
an interesting topic to discuss anyway. Also, if any good
advice/answers exist perhaps they should go in the FAQ?
Any suggestions would be appreciated.
Cheers,
Jamu.
--
Jamu Kakar (Developer) Expressus Design Studio, Inc.
jkakar@expressus.com 708-1641 Lonsdale Avenue
V: (604) 903-6994 North Vancouver, BC, V7M 2J5
Good day,
We're running a week-old CVS snapshot of PostgreSQL 7.1, and I'm not sure
if this performance inconsistency is specific to it, or if this is just
something in PostgreSQL in general, but it seems kind of odd, and I could
use some help here. ;)
I have run two queries in table full of invoices where the only different
in the query is the where clause:
SELECT COUNT(DISTINCT l."ORDER_NUM") AS line_orders,
SUM(l."GROSS_PROFIT" + "REPLACEMENT_COST") AS grand_total,
SUM(l."LIST_PRICE" * l."SHIPPED_QUANTITY") AS grand_total_alternative,
SUM(l."GROSS_PROFIT") AS grand_total_profit
FROM cu_invoice_li1_tbl l INNER JOIN cu_invoice_03_tbl h
ON l."ORDER_NUM"=h."ORDER_NUM"
WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.)
OR...
WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.)
The first query returns more rows than the second (because it includes
orders prefixed with I, C, F, etc), whereas the second only returns orders
prefixed with I. It seems that the logic of identifying a single character
as *not* being a single value would be identical or at least similar in
efficiency to the logic of a single character *being* being a single value.
However, the first query runs in about 10-15 seconds, and the second query
ran for over 40 minutes before I cancelled it.
Now, I can easily use a series of "!=" statements to get it down so that
it's only the 'I' orders (which runs even faster, at about 5-6 seconds!),
but can anyone explain this to me? :)
Are "!=" substring evaluations inherently faster by an obscene order of
magnitude, or is something really wrong here?
J.
--
--
<COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY>
<PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT>
<PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT>
<WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER>
--
Instead of asking why a piece of software is using "1970s technology,"
start asking why software is ignoring 30 years of accumulated wisdom.
--
jkakar@expressus.com writes:
I'm glad I did this as I found out I have
to use 'pg_dump -d' to get a useful backup. =)
Why?
The tables I need to modify are referenced by other
tables- will those other tables realise that they should re-establish
foreign key references? If not automatically, will VACUUM ANALYZE do
this for me?
No, and no :-(.
regards, tom lane
Tom,
That does bring up a related question: when are we gonna get DROP
COLUMN capability? Currently my tables are littered with unused columns
because I can't remove them without blowing my referential integrity and
views to heck.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco