Re: [SQL] Alter Table/Indexing
At 02:20 AM 3/25/2009, pgsql-sql-ow...@postgresql.org wrote: To: Zdravko Balorda zdravko.balo...@siix.com cc: pgsql-sql@postgresql.org Subject: Re: Alter Table/Indexing In-reply-to: 49c89fea.8060...@siix.com References: 49c89fea.8060...@siix.com Comments: In-reply-to Zdravko Balorda zdravko.balo...@siix.com message dated Tue, 24 Mar 2009 09:55:06 +0100 Date: Tue, 24 Mar 2009 10:35:31 -0400 Message-ID: 27189.1237905...@sss.pgh.pa.us From: Tom Lane t...@sss.pgh.pa.us X-Archive-Number: 200903/84 X-Sequence-Number: 32327 Zdravko Balorda zdravko.balo...@siix.com writes: I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. ALTER TABLE TYPE already rebuilds the indexes; you won't make the overall process any faster by doing that by hand. regards, tom lane I had a case (a long time ago) where I was on MS SQL in a production environment. We had a number of indices which were system related - meaning they were used infrequently to speed up certain administrative functions. When doing a bulk load we found that if we dropped these indices (but kept the ones that were crucial for production) we could significantly speed up the effective downtime of the system b/c any DDL statement was executed faster. We would then schedule these indices to be re-created at later dates, spreading out the load (b/c the system was in production at that point). I wonder if Postgres functions similarly for such a use case? As Tom says, the total processing time is fixed: you have to upload the data and rebuild all the indices, but if there are non-critical indices, you can go from zero to data loaded faster by dropping them and rebuilding them manually later? Thanks for any insight on that (and I hope my question helps the OP as well - if this seems off topic let me know), Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] alter table on a large db
On 2009-03-19, Zdravko Balorda zdravko.balo...@siix.com wrote: Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. which do you prefer 30 minutes down-time to reload the database or a few hours wait to update it live. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. drop and reload the table? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Alter Table/Indexing
Hi, I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. Does changing type or setting default on an indexed column require sorting? Thanks, Zdravko -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Alter Table/Indexing
Zdravko Balorda zdravko.balo...@siix.com writes: I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. ALTER TABLE TYPE already rebuilds the indexes; you won't make the overall process any faster by doing that by hand. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] alter table on a large db
Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. Regards, Zdravko. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] alter table on a large db
Στις Thursday 19 March 2009 09:57:11 ο/η Zdravko Balorda έγραψε: Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. Which version of postgresql do you use? Also you might consider editing the SQL dump to alter the DDL statements prior to loading it in your db. batch editing sed, awk, perl, could help you on that... Regards, Zdravko. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] alter table on a large db
On Thu, Mar 19, 2009 at 1:57 AM, Zdravko Balorda zdravko.balo...@siix.com wrote: Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. Which alter table statements are you running in particular? Most alter table stuff runs pretty quickly, like adding a column and such. Just wondering. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ALTER TABLE mytable DROP CONSTRAINT IF EXISTS myconstraint ?
On Jan 23, 2008 4:25 AM, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: Does PG have any way of doing $subject without writing a plpgsql-function which does it by querying the catalog manually? No. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] ALTER TABLE mytable DROP CONSTRAINT IF EXISTS myconstraint ?
Does PG have any way of doing $subject without writing a plpgsql-function which does it by querying the catalog manually? I'm looking for an equivalent of DROP TABLE IF EXISTS mytable -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] alter table table add column
Hi, Anybody knows how to add column with reference to BEFORE or AFTER any given column? Let say here's my table structure: Column | Type| Modifiers --+---+--- surname | character varying | lastname | character varying | address | character varying | And, I want to add the field name age with type integer after lastname OR before the address field. How to I do that? I would really appreciate your response. Thanks in advance. == Ronald Rojas Systems Administrator Linux Registered User #427229 == Arnold's Laws of Documentation: (1) If it should exist, it doesn't. (2) If it does exist, it's out of date. (3) Only documentation for useless programs transcends the first two laws.
[SQL] Alter Table
When i alter a table and add a new column it always adds it to the end of the table. Is there any way to tell it to add the new column at the 5th position or to add the new column after a surtain column. Thanks Shavonne Wijesinghe http://www.studioform.it
Re: [SQL] Alter Table
am Thu, dem 15.03.2007, um 14:35:41 +0100 mailte Shavonne Marietta Wijesinghe folgendes: When i alter a table and add a new column it always adds it to the end of the table. Is there any way to tell it to add the new column at the 5th position or to add the new column after a surtain column. No, but you can (and should) define the column-order in the select-statement. Other solutions: - define a VIEW - recreate the table Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alter Table
On Thu, 2007-03-15 at 08:35, Shavonne Marietta Wijesinghe wrote: When i alter a table and add a new column it always adds it to the end of the table. Is there any way to tell it to add the new column at the 5th position or to add the new column after a surtain column. No, but the good news is you can just recreate the table once it's stabilized in form with something like: create table new_table as select field1, field4, field2, field3 from old_table; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] alter table
Hello ,
How can i modify few fields with alter?
ALTER TABLE fv_wystawione
ALTER id_fv SET DEFAULT nextval('id_fv_seq'::text),
ALTER imie SET DEFAULT '';
Syntax error in last line.
--
Best regards,
Maciej mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] alter table
Maciej Piekielniak [EMAIL PROTECTED] schrieb:
Hello ,
How can i modify few fields with alter?
ALTER TABLE fv_wystawione
ALTER id_fv SET DEFAULT nextval('id_fv_seq'::text),
ALTER imie SET DEFAULT '';
test=# create table xyz (id int not null);
CREATE TABLE
test=# create sequence xyz_seq;
CREATE SEQUENCE
test=# alter table xyz alter column id set default nextval('xyz_seq');
ALTER TABLE
or:
test=# drop TABLE xyz;
DROP TABLE
test=# create table xyz (id int not null, foo varchar);
CREATE TABLE
test=# alter table xyz alter column id set default nextval('xyz_seq'), alter
column foo set default '';
ALTER TABLE
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] alter table
Hello Andreas,
Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
AK test=# alter table xyz alter column id set default nextval('xyz_seq'),
alter column foo set default '';
PGAdmin-SQL:
alter table xyz alter column id set default nextval('xyz_seq'), alter column
foo set default '';
ERROR: syntax error at or near , at character 63
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] alter table
Hello Andreas, Wednesday, February 15, 2006, 8:27:00 PM, you wrote: AK test=# select version(); AKversion AK -- AK PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6) AK (1 row) AK i'm working with the native client - psql. AK HTH, Andreas PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) I must work on 7.4... -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
Andreas Kretschmer wrote:
Maciej Piekielniak [EMAIL PROTECTED] schrieb:
Hello Andreas,
Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
AK test=# alter table xyz alter column id set default
nextval('xyz_seq'), alter column foo set default '';
PGAdmin-SQL:
alter table xyz alter column id set default
nextval('xyz_seq'), alter column foo set default '';
ERROR: syntax error at or near , at character 63
Hmm.
test=# select version();
version
--
PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc
(GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
(1 row)
Note that prior to 8.0 PostgreSQL does not support multiple ALTER actions in a
single query. To get an equivalent effect, wrap separate ALTER TABLE queries
in a transaction:
BEGIN;
alter table xyz alter column id set default nextval('xyz_seq');
alter table xyz alter column foo set default '';
COMMIT;
Also, are you sure you want '' as a column default, and not ALTER COLUMN foo
DROP DEFAULT?
-Owen
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] alter table
Hello Owen,
Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
OJ Note that prior to 8.0 PostgreSQL does not support multiple ALTER actions
in a single query. To get an equivalent effect, wrap separate ALTER TABLE
queries in a transaction:
OJ BEGIN;
OJ alter table xyz alter column id set default nextval('xyz_seq');
OJ alter table xyz alter column foo set default '';
OJ COMMIT;
OJ Also, are you sure you want '' as a column default, and not ALTER COLUMN
foo DROP DEFAULT?
OJ -Owen
OK. THX. Second question:
First, maybe set many fields with the same action - ex. set default?
Ex. on mysql
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
MODIFY specific_name char(64) DEFAULT '' NOT NULL,
MODIFY sql_data_access
enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA'
) DEFAULT 'CONTAINS_SQL' NOT NULL
Second, can i modify more than 1 option with alter table on one field?:
ex (mysql):
ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] alter table
Maciej Piekielniak wrote:
Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
OJ Note that prior to 8.0 PostgreSQL does not support
multiple ALTER actions in a single query. To get an
equivalent effect, wrap separate ALTER TABLE queries in a transaction:
OJ BEGIN;
OJ alter table xyz alter column id set default nextval('xyz_seq');
OJ alter table xyz alter column foo set default '';
OJ COMMIT;
OJ Also, are you sure you want '' as a column default, and
not ALTER COLUMN foo DROP DEFAULT?
OJ -Owen
OK. THX. Second question:
First, maybe set many fields with the same action - ex. set default?
Ex. on mysql
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
MODIFY specific_name char(64) DEFAULT '' NOT NULL,
MODIFY sql_data_access
enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA'
) DEFAULT 'CONTAINS_SQL' NOT NULL
Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE
statements:
BEGIN;
ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
... and so on ...
COMMIT;
Note that ALTER TABLE under postgresql cannot change a column's type (including
precision or length). You can fake it by renaming the existing column,
creating a new column of the appropriate type, UPDATEing data from the old
column to the new column, [setting the new column's constraints,] and finally
removing the old column, but it's a long-winded process.
Second, can i modify more than 1 option with alter table on
one field?:
ex (mysql):
ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;
Not under 7.4.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] alter table
Hello Owen, Wednesday, February 15, 2006, 8:56:05 PM, you wrote: ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL; OJ Not under 7.4. Hmm, maybe postgres can copy constraints and properties in create table .. as select? CREATE TABLE fv_wystawione ( abonament) AS SELECT a.nazwa from abonamenty a; This command only copy data type. -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
Maciej Piekielniak wrote: Hello Owen, Wednesday, February 15, 2006, 8:56:05 PM, you wrote: ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL; OJ Not under 7.4. Hmm, maybe postgres can copy constraints and properties in create table .. as select? What is it you're actually trying to accomplish? There's got to be a better way, but without understanding what you're doing (rather than how) it's hard to give you advice. CREATE TABLE AS and SELECT INTO only reproduce data, not metadata. AFAIK duplicating a table's constraints involves fishing around in the pg_ system tables. -Owen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
On Wed, 15 Feb 2006, Owen Jacobson wrote:
Maciej Piekielniak wrote:
Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
OJ Note that prior to 8.0 PostgreSQL does not support
multiple ALTER actions in a single query. To get an
equivalent effect, wrap separate ALTER TABLE queries in a transaction:
OJ BEGIN;
OJ alter table xyz alter column id set default nextval('xyz_seq');
OJ alter table xyz alter column foo set default '';
OJ COMMIT;
OJ Also, are you sure you want '' as a column default, and
not ALTER COLUMN foo DROP DEFAULT?
OJ -Owen
OK. THX. Second question:
First, maybe set many fields with the same action - ex. set default?
Ex. on mysql
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
MODIFY specific_name char(64) DEFAULT '' NOT NULL,
MODIFY sql_data_access
enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA'
) DEFAULT 'CONTAINS_SQL' NOT NULL
Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE
statements:
BEGIN;
ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
... and so on ...
COMMIT;
Note that ALTER TABLE under postgresql cannot change a column's type
(including precision or length).
Not in 7.4, but I believe 8.1 allows that (ALTER TABLE ALTER COLUMN TYPE
with semi-optional USING)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] alter table
Hello Stephan, Wednesday, February 15, 2006, 9:03:26 PM, you wrote: SS Not in 7.4, but I believe 8.1 allows that (ALTER TABLE ALTER COLUMN TYPE SS with semi-optional USING) Thx for all. -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
On Feb 16, 2006, at 3:11 , Maciej Piekielniak wrote:
How can i modify few fields with alter?
I think you need to alter columns one at a time. If you need them to
go into effect at the same time, you can wrap the multiple ALTER
TABLE statements in a transaction. For example,
begin;
ALTER TABLE fv_wystawione ALTER id_fv SET DEFAULT nextval
('id_fv_seq'::text);
ALTER TABLE fv_wystawione ALTER imie SET DEFAULT '';
commit;
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Alter table
Ok another very newbie question. How can i change the data type a column can accept? at the moment it will only take character(7) i want to change it to varchar(30), but i cant figure how, ideas? Many thanks Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/ 7.3 supports the drop constraint. The only exception begin if you upgraded your database. It keeps the existing trigger like constraints if youve moved from 7.1 or 7.2. But these triggers can be dropped as well. HTHChad P.S. Great signature! :-)
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
I think you can do some constraints in 7.2, but 7.3 will allow dropping them all in that fashion. On Wed, 2003-07-30 at 11:52, Elielson Fontanezi wrote: Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !__! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst ---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0 ---()--( ) \ () / \_/\_/ signature.asc Description: This is a digitally signed message part
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
if the constraint are named $1 $2 etc you will need to quote them eg ALTER TABLE xyz DROP CONSTRAINT $1 ; in some version you may require ALTER TABLE xyz DROP CONSTRAINT $1 RESTRICT; What is ur version btw? try to post the table structure also. regds mallah. Elielson Fontanezi wrote: Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) \\\!/ 55 11 5080 9283 !__! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst ---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0 ---()--( ) \ () / \_/\_/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] ALTER TABLE x DROP CONSTRAINT fkey
Pardon stupidiy, what is the right syntax for dropping a foreign key? Struggling here! TIA --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Alter table
Uz.ytkownik Rachel.Vaudron napisa?: Hi, I wonder if it is possible to remove a field of a table ? I haven't found anything about this into the reference manual. Can I do something like that ?: ALTER TABLE table DROP COLUMN column; alter table xxx rename to temp; create table xxx as select field1, field2, ...without some field... from temp; drop table temp; Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Alter table
Thanks a lot, but it's already what i am doing! It's very a pity that there is no way do to this more quickly!!! alter table xxx rename to temp; create table xxx as select field1, field2, ...without some field... from temp; drop table temp; Tomasz Myrta Rachel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Alter table
Hi, I wonder if it is possible to remove a field of a table ? I haven't found anything about this into the reference manual. Can I do something like that ?: ALTER TABLE table DROP COLUMN column; Thanks Rachel ** [EMAIL PROTECTED] Laboratoire de prehistoire du Lazaret 33 bis bd Franck Pilatte 06300 Nice http://rachel.familinux.org Windows a bug's life ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] alter table question
How do I alter a table to set a column to be not null? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] alter table add column implementation undesirable?
Forest Wilkinson [EMAIL PROTECTED] writes: A coworker told me that the postgres implementation of ALTER TABLE ADD COLUMN creates an inefficient database. Dunno where he got that idea. There are some problems lurking in ADD COLUMN when used on a table with inheritance children --- the new column is added to the children too, as it should be, but in an unexpected column position, which causes trouble for pg_dump (a dump and reload will do the wrong thing). Perhaps what you heard is a garbled report of that issue. regards, tom lane
[SQL] alter table add column implementation undesirable?
A coworker told me that the postgres implementation of ALTER TABLE ADD COLUMN creates an inefficient database. He said it results in a table whose new column is stored someplace other than the rest of the columns. (A hidden auxiliary table?) Is this true in postgres 6.5.3? 7.x? Was it ever true? Forest Wilkinson
[SQL] ALTER TABLE ADD COLUMN syntax question.
I used the command below to alter a table and it took with no problem, but the REFERENCES appears to have been ignored since I can put invalid numbers into last_post_id. Are there plans to add the ability to specify column constrainsts with ALTER TABLE ADD COLUMN in the future? For now, I suppose I can use ALTER TABLE ADD CONSTRAINT to make a table constraint for last_post_id. I hope it will not make things too slow. Are table constraints slower than column constraints? ALTER TABLE topic ADD COLUMN last_post_id INTEGER REFERENCES post ON DELETE SET NULL; -- Robert
