0

I would like to add a column qc_isotope to an existing psql table sensitivity. The value of qc_isotope must not be NULL and should either equal 'TC' or 'TL' with a default value of 'TC' given to all existing rows in sensitivity. I am fairly new to Postgresql and am unsure how to do this.

Here is my attempt which is unsuccessful

ALTER TABLE sensitivity 
ADD COLUMN qc_isotope VARCHAR(2) CHECK 'NOT NULL' DEFAULT 'TC';
Improve this question

2 Answers 2

Reset to default
3

try this:

ALTER TABLE sensitivity 
   ADD qc_isotope VARCHAR(2) DEFAULT 'TC' 
       CHECK (qc_isotope IN ('TC', 'TL')) NOT NULL;

The full list of syntax options etc for check constraints is described here. There is also further information on alter table add column statements here.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

You forgot the NOT NULL constraint
@ChrisProsser - It does not seem to like the ENABLE keyword
@moadeep Apologies, I have corrected the answer above. I am also just checking whether the not null constraint is required in addition.
@a_horse_with_no_name You are quote right, I had assumed the in would handle this, but it does not. I am corrective above, thanks for pointing this out.
I edited the answer to make it compatible with Postgres (I incorrectly changed the question's tag to oracle so this is on me). I also replaced the links to the Oracle manual with links to the Postgres manual.
|
2

A different appoach: create a DOMAIN and use that as a data type for qc_isotope. This will come in handy if the data type uccurs in more than one place: the constraint won't have to be repeated.

CREATE DOMAIN QC_ISO VARCHAR(2) CHECK (value IN ('TC', 'TL' ))
        ;

CREATE  TABLE sensitivity
        ( id SERIAL NOT NULL PRIMARY KEY
        );

ALTER TABLE sensitivity
        ADD COLUMN qc_isotope QC_ISO NOT NULL DEFAULT 'TC'
        ;

INSERT INTO sensitivity(qc_isotope) VALUES ('AA') ;
INSERT INTO sensitivity(qc_isotope) VALUES ('TC') ;

SELECT * FROM sensitivity;

Result:

CREATE DOMAIN
NOTICE:  CREATE TABLE will create implicit sequence "sensitivity_id_seq" for serial column "sensitivity.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sensitivity_pkey" for table "sensitivity"
CREATE TABLE
ALTER TABLE
ERROR:  value for domain qc_iso violates check constraint "qc_iso_check"
INSERT 0 1
 id | qc_isotope 
----+------------
  2 | TC
(1 row)

UPDATE: it does appear that DOMAINs can be changed once they are used (this works on PG-9.1):

ALTER DOMAIN QC_ISO
        DROP CONSTRAINT QC_ISO_check -- I don't think the name is important
        ;

ALTER DOMAIN QC_ISO
        ADD CONSTRAINT QC_ISO_check CHECK (value IN ('TC', 'TL', 'AA' ))
        ;
INSERT INTO sensitivity(qc_isotope) VALUES ('AA') ;
INSERT INTO sensitivity(qc_isotope) VALUES ('BB') ;


SELECT * FROM sensitivity;

New result:

ALTER DOMAIN
ALTER DOMAIN
ERROR:  value for domain qc_iso violates check constraint "qc_iso_check"
INSERT 0 1
 id | qc_isotope 
----+------------
  2 | TC
  4 | AA
(2 rows)
Improve this answer

3 Comments

As much as I love the domain concept, the fact that the definition cannot be changed once it is used in a table makes it a bit cumbersome to use.
You are right. I was referring to the definition of the datatype which you cannot change e.g. from varchar(20) to varchar(50) if you decided later that you need more characters. A possible workaround is an unbounded type with a check constraint though.
The UDT datatypes are painful. Unfortunately, they are needed e.g. for GIS. Upgrading will cause headaches: lots of dynamic SQL and poking around in the catalogs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.