Problem with PostgreSQL query

[pablogosse]

Hi all. I'm having a problem with a query in PostgreSQL that I'm hoping someone can help with.

I've got a table, 'jb_jobs', with a column 'education_levels' that is currently set to accept null values.

I want to alter the table and set 'education_level' to not null, so I'm using the following:

ALTER TABLE jb_jobs ALTER COLUMN "education_levels" SET NOT NULL;

which returns the following error:

PostgreSQL said: ERROR: parser: parse error at or near "NOT"
Your query:
ALTER TABLE jb_jobs ALTER COLUMN "education_levels" SET NOT NULL

Can anyone see what the problem is? I've looked high and low and I can't see anything, and I actually got the syntax for this straight from the PostgreSQL documentation.

Thanks in advance,
Pablo

[ultraslacker]

Ahh, the joy of modifying postgres tables.

You can use a table constraint -

ALTER TABLE jb_jobs ADD CONSTRAINT jb_jobs_education_levels CHECK(education_levels IS NOT NULL);

It is more efficient to enforce the constraint generated in the table definition, so you may want to just recreate the table.

You will be able to use your ALTER syntax in version 7.3, which will avoid all this trouble.

[gigowiz]

You can't make that alteration. You can only set or unset the default value.

See

http://www.ca.postgresql.org/docs/aw_pgsql_book/node203.html

Hank