How to Create a Table in PostgreSQL with an auto-increment

Posted on

This article focus on how to create a table in PostgreSQL Database Server. Moreover, it is a table with an auto-increment feature. In order to create it, below are the steps to take :

1.  Access the PostgreSQL Database Server by any means. In this article, accessing through the PostgreSQL command console. Just type the following command pattern :

psql -Uusername
user@hostname:~$ psql -Umyuser
psql (10.5)
Type "help" for help.

dbname=#

2. After successfully access the PostgreSQL command console, execute the following query pattern :

create table tablename(column_name column_type)

The following is an example of executing a query according to the above pattern :

dbname=# create table tablename (table_id serial primary key, name varchar(100));
CREATE TABLE
dbname=# 

3. Don’t forget to check the table if the output from the above query fits the requirement. The following is the command execution :

dbname=# \d+ table
                                                               Table "public.table"
   Column    |          Type          | Collation | Nullable |                    Default                    | Storage  | Stats target | Description 
-------------+------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 table_id    | integer                |           | not null | nextval('software_software_id_seq'::regclass) | plain    |              | 
 name        | character varying(100) |           |          |                                               | extended |              | 
Indexes:
    "software_pkey" PRIMARY KEY, btree (software_id)

dbname=# 

4. Just try and execute a specific query to test whether the auto-increment is working or not. Before that, check the content of the table. Initially, there will be no row of record at all. The following is the query command :

select * from table

The following is the execution of the above query :

dbname=# select * from table;
 table_id    | name 
-------------+------
(0 rows)

dbname=# 

5. Insert a new record to the table. It is to prove without hinting the auto-increment column in the query, the execution is still success.

dbname=# insert into tablename(name) values('content');
INSERT 0 1
dbname=#

6. Check the record if it exist in the table. Just execute the following query :

dbname=# select * from tablename;
 table_id    |   name   
-------------+----------
           1 | content
(1 row)

dbname=# 

7. Apparently, by the output of the above query, it is a success. Without having to specify the value of the ‘table_id’, the value is defined by the previous query.

Leave a Reply