How to Solve Error Message ERROR: syntax error at or near “auto_increment” when create table in PostgreSQL Database Server

By Posted on

Introduction

Another article where the main focus is just to show how to solve error message. The error message appears upon creating a new table. But the one which is triggering the error message is in the usage of the ‘auto_increment’ in the query as exist below : 

db_apps=# create table users(user_id int auto_increment, email_id varchar(55), password varchar(55), first_name varchar(55), last_name varchar(55), primary key(user_id));
ERROR: syntax error at or near "auto_increment"
LINE 1: create table users(user_id int auto_increment, email_id varc...
^
db_app=#

Solution

So, after getting the description for all the error message above in the previous part. The solution for adding an auto increment feature is for set the sequence to the table. Just create the table as is without having to add the ‘auto_increment’ reserved keyword for adding the auto increment function. Actually, the solution is actually exist in the previous article. Not a direct solution for solving the problem in order to add the auto_increment feature but the focus is to create the table successfully. That article for creating the table has the title of ‘How to Solve Error Message Cannot Create user Table in PostgreSQL Database Server’ exist in this link. The actual solution after creating the table is in the following steps :

  1. The following is the command for creating the table as follows : 

    db_apps=# create table users(user_id int primary key, email_id varchar(55), password varchar(55), first_name varchar(55), last_name varchar(55));
CREATE TABLE
db_apps=#

  2. Create a new sequence in the PostgreSQL command console. It is actually already available in another article with the title of ‘How to Alter Primary Key Column as an Auto Increment Column in PostgreSQL Database Server’ in this link. Just type the following syntax pattern : 

    product=# create sequence user_id_seq;
CREATE SEQUENCE
product=#

  3. Finally, continuing on the previous step, for creating the sequence, just alter the primary key column. It is an additional step in order to modify that primary key column to have an auto increment feature. Just execute the following command : 

    product=# alter table category id set default nextval('user_id_seq');
ALTER TABLE
product=#

Leave a Reply