1

i'm trying to alter my table but it doesn't work, although according to 1 hour of google this should be the way to do it... the error seems to be with the brackets. I've tried them all () {} []...

ALTER TABLE all_in_one DROP (age, occupation, salary);
ALTER TABLE all_in_one ADD COLUMN sex char(7);
ALTER TABLE all_in_one ALTER COLUMN sex char(7) SET DEFAULT 'male';

SQL error:

FEHLER:  Syntaxfehler bei „(“
LINE 2: ALTER TABLE all_in_one DROP (age, occupation, salary);
                                    ^

i also have a problem with the data type. Seems char(7) just won't work.

I'm using phpPgAdmin.

EDIT: i used "ALTER TABLE all_in_one DROP COLUMN age, DROP COLUMN occupation, DROP COLUMN salary;", but is this the only way to do that? Or is there no way i could use brackets in phppgadmin?(if i need to delete 10-100 or more columns, writing DROP COLUMN seems very tedious)

Next problem would be defining the data type.

ALTER TABLE all_in_one ADD COLUMN sex char(7);

doesn't work.

EDIT:

ALTER TABLE all_in_one DROP COLUMN age, DROP COLUMN occupation, DROP COLUMN salary;
ALTER TABLE all_in_one ADD COLUMN sex character(7);
ALTER TABLE all_in_one ALTER COLUMN sex SET DEFAULT 'male';

this works. although the documentation says i could use char, i couldn't. but with character it works.

Peter
  • 13
  • 1
  • 4
  • Which version of Postgres you re using? See if [this](http://stackoverflow.com/questions/9783422/postgres-array-for-loop) helps regarding the multiple columns to drop. Also it may be easier to just create a new table with only the columns you need there, insert the data you need in the new and then delete the old table/rename the new. – Salem Jul 13 '14 at 13:20
  • i am using PostgreSQL 9.3.4 – Peter Jul 13 '14 at 13:25
  • How often do you need to do this - I'm guessing once. What's the big deal in reducing the amount of typing for a one-time operation? Just type it and move on. – Bohemian Jul 13 '14 at 13:26
  • i moved on. droping columns one by one is my new thing now, but i still have the problem with TYPE. ADD COLUMN sex TYPE char(7) causes a problem. ADD COLUMN sex char(7) causes a problem. even the variations with and without "" (7), etc. – Peter Jul 13 '14 at 13:29
  • @Peter TYPE is only needed in ALTER statement.See my edit. – Salem Jul 13 '14 at 13:42
  • ok thx all, got it now, moving on. – Peter Jul 13 '14 at 13:56

1 Answers1

0

I think the right syntax is 

ALTER TABLE all_in_one DROP COLUMN age, DROP COLUMN occupation, DROP COLUMN salary;

and for alter table:

ALTER TABLE all_in_one ADD COLUMN sex char(7);
ALTER TABLE all_in_one ALTER COLUMN sex TYPE char(7);
ALTER TABLE all_in_one ALTER COLUMN sex SET DEFAULT 'male';
Salem
  • 12,808
  • 4
  • 34
  • 54