Thread: 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:piechcio@isb.com.pl
Maciej Piekielniak <piechcio@isb.com.pl> 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°
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,Maciej mailto:piechcio@isb.com.pl
Maciej Piekielniak <piechcio@isb.com.pl> 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.2on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
(1 row)
i'm working with the native client - psql.
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°
Hello Andreas, Wednesday, February 15, 2006, 8:27:00 PM, you wrote: AK> test=# select version(); AK> version 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,Maciej mailto:piechcio@isb.com.pl
Andreas Kretschmer wrote:
>
> Maciej Piekielniak <piechcio@isb.com.pl> 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
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,Maciej mailto:piechcio@isb.com.pl
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
byrenaming the existing column, creating a new column of the appropriate type, UPDATEing data from the old column to
thenew 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.
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,Maciej mailto:piechcio@isb.com.pl
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 fishingaround in the pg_ system tables. -Owen
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)
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,Maciej mailto:piechcio@isb.com.pl
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