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.
One thought on “How to Create a Table in PostgreSQL with an auto-increment”