Alter table column syntax add to SQL

Question

I am trying to change a table named company but an error will be displayed

syntax error at or near "(" LINE 2: ADD( company_access_level short NOT NULL, 

My syntax

 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

Answer 1

I just tried this fixed syntax in postgressql and it worked. There is no short type, although you will have to use something else (maybe smallint ?) If your table contains data, this script will fail for the reasons stated 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 

Answer 2

In addition, if there is data in your table, then you cannot add NOT NULL columns (and for some RDBMSs you cannot add NOT NULL columns, even if there is no data in the table).

Either specify the default value, or enable the NULLable column. You can always populate new columns with data and subsequently change NOT NOT columns.

Answer 3

Sorry to open such an old question, but the advice in one of the answers you couldn't add NOT NULL cost me a bit of trouble. You can add a NOT NULL column to the data table, the only limitation is that you must also specify a default value. Tested with Sybase, Postgres and MySQL. So the example above:

 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 

Answer 4

As someone who came here with the same question, I am not sure what to do with these answers. I tried all of them and always got a syntax error "next to" with one or another term. Returning to the official docs , I realized that there was no additional keyword, such as SET or TYPE. Examples:

First it

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, but as soon as I specified the DATE value with the TYPE keyword, the error was resolved and I moved on to another. I had the same experience with the addition of SET (see Examples on the same page of white papers that I already quoted).

Regarding the specific NOT NULL problem (especially regarding my date of dates), I read this answer and it seemed to work - I did not receive the error message -

 zuri=# update lili_code set sunsetdate=NULL; UPDATE 0 

But then I read

On success, the UPDATE command returns the form command tag

 UPDATE count 

Number is the number of rows updated. If count is 0, there are no lines (this is not considered an error).

What is also in the official docs here .

Finally, I turned to PGAdminIII, where I found that NOT NULL is a simple flag. Uncheck the box, the problem is resolved. I am sure there is a way to make this work on the command line with psql, I just did not find it.

I think that some of the options can also be related to the differences between ALTER and UPDATE (see this SO answer and my daring comment), as well as between adding new structures (as in the OP question) and changing data that already exists (like I have). The moral of the story, read the official documentation. Do not scan it. Read. And if you want to know more about NULL and NOT NULL, read this .