w3resource

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:

CREATE TABLE countries ( 
COUNTRY_ID SERIAL PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID integer NOT NULL
);

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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming