How to create tables in postgreSQL using automatic generation of primary key id?

Question

How to create tables in postgreSQL using automatic generation of primary key id?

Navigation

#1 by (4 votes)
#2 by (2 votes)

1

I want you to forgive me for this post, maybe it's somebody's doubt, but come on ...

I know that the programmer has to master some things, and these things are considered as trívias, for example; Every programmer has to know advanced English not to stay depending on courses but yes 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 do not know of all these things more I am learning moderately the subjects.

I had the habit of creating my MySQL applications, but I have a client who wants to do the system in PostgreSQL anyway, he does not have a conversation, but today I'm out of a job and there are some 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 , but the security part is necessary to create the tables through SQL statement, I know how to do without any problem in MySQL database, but in the same way I know how to do in Mysql I do not know how to create the database in PosgreSQL, I am I'm having trouble with syntax errors, and unfortunately I do not have a test database to create the system, I know this is a huge mistake, but I'm going to try to solve this problem further.

I've tried searching Free courses on how to create tables in SQL, but there is very little information on the internet in this regard, of course there is a lot of information in English, but I can not understand why I'm still learning English. The tables are just the ones below, I'm trying to create these tables in PostgreSQL, but it seems that to create an auto increment key I have to create it separately, I've made a few attempts, but I'm afraid that damaging the database settings on the server Heroku.

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 anyone generously tell me how these tables would look if they were made in PosgreSQL?

When you try to run Query from the user_group creation, you 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

postgresql

asked by anonymous 21.06.2017 / 19:32

2 answers

2

In PostgreSQL the AUTO_INCREMENT is always done through SEQUENCES . When using numeric type BIGSERIAL , a SEQUENCE object is implicitly created by the server.

Serial and bigserial data types are not true types, but merely a convenient notation to define identifier columns (similar to the existing AUTO_INCREMENT property in some other databases).

Particularly, I'm not a fan of these "conveniences" and I'd rather have control of what's going on.

I suggest creating the object of SEQUENCE manually and I recommend avoiding using these numeric 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)
);

21.06.2017 / 20:58

Create function remove () [closed] Reading data from a Json with php

score 4 · Accepted Answer

To define a column as auto increment of MySQL use the type called serial or bigserial this will create a sequence that will overlap the number with each inserted record.

The DDL of the table should look this way. PostgreSQL does not work with engines like MySQL so it can skip setting it.

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