|
Home > Archive > PostgreSQL SQL > February 2006 > alter table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Maciej Piekielniak 2006-02-15, 1:24 pm |
| 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
---------------------------(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
| |
| Andreas Kretschmer 2006-02-15, 1:24 pm |
| 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°
---------------------------(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
| |
| Maciej Piekielniak 2006-02-15, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Andreas Kretschmer 2006-02-15, 8:24 pm |
| 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)
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°
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Maciej Piekielniak 2006-02-15, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Owen Jacobson 2006-02-15, 8:24 pm |
| Andreas Kretschmer wrote:
>
> Maciej Piekielniak <piechcio@isb.com.pl> schrieb:
>
> nextval('xyz_seq'), alter column foo set default '';
> nextval('xyz_seq'), alter column foo set default '';
>
> 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
| |
| Maciej Piekielniak 2006-02-15, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Owen Jacobson 2006-02-15, 8:24 pm |
| 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, [s
etting 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
| |
| Maciej Piekielniak 2006-02-15, 8:24 pm |
| Hello Owen,
Wednesday, February 15, 2006, 8:56:05 PM, you wrote:[color=darkred
]
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Owen Jacobson 2006-02-15, 8:24 pm |
| Maciej Piekielniak wrote:
>
> Hello Owen,
>
> Wednesday, February 15, 2006, 8:56:05 PM, you wrote:
>
> 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
| |
| Stephan Szabo 2006-02-15, 8:24 pm |
| On Wed, 15 Feb 2006, Owen Jacobson wrote:
> Maciej Piekielniak wrote:
>
> 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 majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Maciej Piekielniak 2006-02-15, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Michael Glaesemann 2006-02-15, 8:24 pm |
|
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
|
|
|
|
|