I'm trying to alter the table named company, but it will display the error
syntax error at or near "("
LINE 2: ADD( company_access_level short NOT NULL,
My syntax is
ALTER TABLE company
ADD company_access_level short NOT NULL,
workgroup_level short NOT NULL,
Company Logon URL character varying NOT NULL,
Company Logoff URL character varying NOT NULL
Thanks
I just tried this fixed syntax in postgressql and it worked. There is no short datatype though so you'll have to use something else (maybe smallint?) If your table contains data this script will fail for the reasons in John's answer.
ALTER TABLE company
ADD company_access_level int NOT NULL,
ADD workgroup_level int NOT NULL,
ADD "Company Logon URL" character varying NOT NULL,
ADD "Company Logoff URL" character varying NOT NULL
Also, if your table has data in it then you can't add NOT NULL columns (and for some RDBMSs you can't add NOT NULL columns even when there is no data present in the table).
Either provide a default value or allow the column to be NULLable. You can always populate the new columns with data and modify the columns to be NOT NULL afterwards.
Sorry for opening such an old question but the advice in one of the answers that you could not add a NOT NULL cost me quite a bit of trouble. You CAN add a NOT NULL column to a table with data, the only restriction is that you must also provide a default value. Tested with Sybase, Postgres and MySQL. So the example above becomes:
ALTER TABLE company
ADD company_access_level int default 'not set' NOT NULL ,
ADD workgroup_level int default 0 NOT NULL,
ADD "Company Logon URL" character varying default 'not set' NOT NULL,
ADD "Company Logoff URL" character varying default 'not set' NOT NULL
As someone who came here with the same question, I'm not sure what to make of these answers. I tried them all, and always got a syntax error 'near' one term or another. After going back to the official docs, I realized that what was missing was an additional keyword, such as SET or TYPE. Examples:
First this
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate DATE NULL;
ERROR: syntax error at or near "DATE"
LINE 2: ALTER COLUMN sunsetdate DATE NULL;
Then this:
zuri=# ALTER TABLE newarts
ALTER COLUMN sunsetdate TYPE DATE NULL;
ERROR: syntax error at or near "NULL"
LINE 2: ALTER COLUMN sunsetdate TYPE DATE NULL;
Yes, there is still an error there but once I specified the meaning of DATE with the keyword TYPE the error was resolved and I moved on to another one. I had the same experience with the addition of SET (see the examples on the same page of the official docs I already cited).
As to the specific issue of NOT NULL, (especially as it relates to my issue of dates) I read this answer and it seemed to work - I didn't get an error message -
zuri=# update lili_code set sunsetdate=NULL; UPDATE 0
But then I read
On successful completion, an UPDATE command returns a command tag of the form
UPDATE count
The count is the number of rows updated. If count is 0, no rows matched the condition (this is not considered an error).
Which is also in the official docs, here.
Finally, I turned to PGAdminIII, where I found that NOT NULL is a simple check box. Uncheck it, problem solved. I'm sure there is a way to make this work at the command line with psql, I just haven't found it.
I think some of the variation may also be due to the differences between ALTER and UPDATE (see this SO answer and my cheeky comment) as well as between ADDing new structures (as in the OP's question) and modifying data that is already there (as in mine). The moral of the story, read the official documentation. Don't scan it. Read it. And if you want to know more about NULL and NOT NULL, read this.
