PostgreSQL Insert Record: Insert rows to ensure that a default value will be inserted in a specific column if no values assigned for that

10. Write a SQL statement to insert records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned to that column.

Sample Solution:

Code:

Here is the code to create a sample table countries:

-- This SQL statement creates a new table named 'countries' with specified columns and constraints.

CREATE TABLE countries ( 
    COUNTRY_ID SERIAL PRIMARY KEY,        -- Defines the COUNTRY_ID column as an auto-incrementing serial integer primary key
    COUNTRY_NAME varchar(40) NOT NULL DEFAULT 'N/A', -- Defines the COUNTRY_NAME column as a varchar type with a maximum length of 40 characters, which cannot be NULL, and has a default value of 'N/A'
    REGION_ID integer NOT NULL            -- Defines the REGION_ID column as an integer type, which cannot be NULL
);

Explanation:

• The CREATE TABLE statement is used to create a new table in the database.
• countries is the name of the new table being created.
• COUNTRY_ID is defined with the SERIAL data type, which automatically generates unique integer values for each row inserted into the table. It is also defined as the primary key of the table using the PRIMARY KEY constraint.
• COUNTRY_NAME is defined as a varchar type column with a maximum length of 40 characters and cannot contain NULL values. Additionally, it has a default value of 'N/A', which means if no value is provided during insertion, 'N/A' will be inserted by default.
• REGION_ID is defined as an integer type column and cannot contain NULL values.
• Using DEFAULT 'N/A' ensures that if a value for 'COUNTRY_NAME' is not provided during insertion, it will default to 'N/A'. This helps in cases where the country name is not available or not applicable.

Now insert one record into the table:

INSERT INTO countries VALUES(501,'India',102);

Here is the command to see the list of inserting rows :

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       102
(1 row)

Now insert another two records into the table :

INSERT INTO countries(region_id) VALUES(109);
INSERT INTO countries(country_name,region_id) VALUES('Australia',121);

Now see the value of the key field incremented automatically:

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
        501 | India        |       102
          1 | N/A          |       109
          2 | Australia    |       121
(3 rows)

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

Previous: Write a SQL statement to insert rows into the table countries in which the value of country_id column will be unique and auto incremented.
Next: Write a SQL statement to insert rows into the job_history table in which one column job_id is containing those values which exist in job_id column of jobs table.