w3resource

PostgreSQL Create Table: Create a table to set default value, a minimum value and a NULL as default value for specific columns


9. Write a SQL statement to create a table named jobs, including job_id, job_title, min_salary and max_salary, and make sure that, the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns.

Sample Solution:

Code:

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL UNIQUE, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT NULL
);

Output:

postgres=# CREATE TABLE IF NOT EXISTS jobs (
postgres(# JOB_ID varchar(10) NOT NULL UNIQUE,
postgres(# JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
postgres(# MIN_SALARY decimal(6,0) DEFAULT 8000,
postgres(# MAX_SALARY decimal(6,0) DEFAULT NULL
postgres(# );
CREATE TABLE

Here is the 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 default ' '::character varying
 min_salary | numeric(6,0)          | default 8000
 max_salary | numeric(6,0)          | default NULL::numeric
Indexes:
    "jobs_job_id_key" UNIQUE CONSTRAINT, btree (job_id)

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

Previous: Write a SQL statement to create a table named countries, including country_id, country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.
Next: Write a SQL statement to create a table named countries, including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.