w3resource

PostgreSQL Create Table: Create a table to restrict the upper limit for a column


6. Write a SQL statement to create a table named jobs, including job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000.

Sample Solution:

Code:

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL , 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0), 
MAX_SALARY decimal(6,0) 
CHECK(MAX_SALARY<=25000)
);

Output:

postgres=# CREATE TABLE IF NOT EXISTS jobs (
postgres(# JOB_ID varchar(10) NOT NULL ,
postgres(# JOB_TITLE varchar(35) NOT NULL,
postgres(# MIN_SALARY decimal(6,0),
postgres(# MAX_SALARY decimal(6,0)
postgres(# CHECK(MAX_SALARY<=25000)
postgres(# );
CREATE TABLE

Here is command to see the structure of the created table :

postgres=# \d jobs;
             Table "public.jobs"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 job_id     | character varying(10) | not null
 job_title  | character varying(35) | not null
 min_salary | numeric(6,0)          |
 max_salary | numeric(6,0)          |
Check constraints:
    "jobs_max_salary_check" CHECK (max_salary <= 25000::numeric)

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to create a table countries, set a constraint NULL.
Next: Write a SQL statement to create a table named countries, including country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.