PostgreSQL Insert Record: Insert rows into the table to ensure that the value of the key column will be unique and auto incremented

9. 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.

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, -- Defines the COUNTRY_NAME column as a varchar type with a maximum length of 40 characters, which cannot be NULL
    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.
• REGION_ID is defined as an integer type column and cannot contain NULL values.
• By using SERIAL data type for COUNTRY_ID, PostgreSQL automatically creates a sequence and sets it as the default value for the column, ensuring that each inserted row gets a unique identifier.

Now insert one record into the table:

INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('India',185);

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

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

Now insert another record into the table :

INSERT INTO countries(COUNTRY_NAME,REGION_ID) VALUES('Japan',102);

Now see the value of the key field incremented automatically :

postgres=# SELECT * FROM countries;
 country_id | country_name | region_id
------------+--------------+-----------
          1 | India        |       185
          2 | Japan        |       102
(2 rows)

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

Previous: Write a SQL statement to insert a record into the table countries to ensure that, at country_id and the region_id combination will be entered once in the table.
Next: 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.