How to create postgresql tables using automatic primary key id generation?

Question

I want you to forgive me for this posting, maybe it’s someone’s question, but come on...

I know that the programmer has to master some things, and these things are considered as triads, for example; Every programmer has to know advanced English not to be dependent on courses but on official documentation of a certain technology, Every programmer has to know Object Orientation and every programmer has to know the basics of SQL, so... I don’t know all these things but I’m learning moderately the subjects.

I had the habit of creating my applications in Mysql, but I’m with a client who wants to do the system anyway in Postgresql, with it has no talk, but today I was without a job and has emerged these quick jobs to do, so I’m hugging and I really need your help.

Currently I need to implement the security system of an application, and I need to create some tables with SQL statement, the project I’m doing it already does a lot for me, and creates some tables automatically, I’m using Spring Boot, however the security part is necessary to create the tables through SQL statement, I know to do without any problem in Mysql database, but in the same way that I know to do in Mysql do not know to perform the creation of the database in Posgresql, I’m encountering syntax errors, and unfortunately I don’t have a test database to create the system, I know this is a huge mistake, but I will try to solve this problem later.

I tried to research Free courses on how to create tables in SQL, but there is little information on the Internet regarding this, of course there is a lot of information in English, but I’m not able to understand why I’m still learning English.

The tables are just these below, I’m trying to create these tables in Postgresql, but it seems that to create an auto key increment has to create it separately, I made some attempts, but I’m afraid that damage the database settings on the Heroku server.

CREATE TABLE usuario (
    codigo BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    senha VARCHAR(120) NOT NULL,
    ativo BOOLEAN DEFAULT true,
    data_nascimento DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE grupo (
    codigo BIGINT(20) PRIMARY KEY,
    nome VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE permissao (
    codigo BIGINT(20) PRIMARY KEY,
    nome VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE usuario_grupo (
    codigo_usuario BIGINT(20) NOT NULL,
    codigo_grupo BIGINT(20) NOT NULL,
    PRIMARY KEY (codigo_usuario, codigo_grupo),
    FOREIGN KEY (codigo_usuario) REFERENCES usuario(codigo),
    FOREIGN KEY (codigo_grupo) REFERENCES grupo(codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE grupo_permissao (
    codigo_grupo BIGINT(20) NOT NULL,
    codigo_permissao BIGINT(20) NOT NULL,
    PRIMARY KEY (codigo_grupo, codigo_permissao),
    FOREIGN KEY (codigo_grupo) REFERENCES grupo(codigo),
    FOREIGN KEY (codigo_permissao) REFERENCES permissao(codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Could someone kindly tell me what these tables would look like if they were made in Posgresql ?

When trying to execute the query of the creation of user_group gave the following error;

ERROR:  syntax error at or near "("
LINE 2:     codigo_usuario BIGINT(20) NOT NULL,
                                 ^
********** Error **********

ERROR: syntax error at or near "("
SQL state: 42601
Character: 55

Answer 1

To define a column as the auto increment mysql use the type called serial or bigserial this will create a Quence that will atulizing the number to each entered record.

The DDL of the table should look like this. Postgresql does not work with Engines like Mysql so you can omit its definition.

CREATE TABLE usuario (
    codigo BIGSERIAL,
    nome VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    senha VARCHAR(120) NOT NULL,
    ativo BOOLEAN DEFAULT true,
    data_nascimento DATE
    PRIMARY KEY(codigo)
);

Postgresql documentation

Answer 2

In Postgresql the AUTO_INCREMENT is at all times made by means of SEQUENCES. When using the numeric type BIGSERIAL, an object of SEQUENCE is created implicitly by the server.

Serial and bigserial data types are not true types, but merely a suitable rating for defining identifying columns unique (similar to the AUTO_INCREMENTO property existing in some other databases).

Particularly, I am not a fan of these "conveniences" and I prefer to have control of what is happening.

I suggest creating the object of SEQUENCE manually and I recommend avoiding the use of such numerical types such as SERIAL and BIGERIAL:

--
-- PRIMEIRO, CRIA-SE A SEQUENCE MANUALMENTE
--
CREATE SEQUENCE public.sq_pk_usuario START 1;

-- 
-- CRIACAO DA TABELA COM VALOR DEFAULT 
--
CREATE TABLE public.usuario
(
    codigo BIGINT NOT NULL DEFAULT nextval('public.sq_pk_usuario'),
    nome VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    senha VARCHAR(120) NOT NULL,
    ativo BOOLEAN DEFAULT true,
    data_nascimento DATE,

    CONSTRAINT pk_usuario PRIMARY KEY (codigo)
);