Input value equal to ID?

Asked

Viewed 28 times

-1

Hello, I have a table in postgres that has a column called link, serves to connect data with the same origin of several tables. This link is equal to the ID of the table in question 'tasks'. How do I make sure that when entering the data, the link value enters equal to the auto_increments ID (primary key)?

sql postgresql

  • I believe that this post has its answer https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

  • You can use in a block the RETURNING link clause of the first INSERT and use this value returned in the other Inserts. https://www.postgresql.org/docs/current/sql-insert.html . Another possibility is to use the currval(sequence) function to obtain the value returned by the last nextval of this sequence.

2 answers

1


Assuming your data model is something like:

CREATE SEQUENCE seq_id_cliente;
CREATE SEQUENCE seq_id_venda;

CREATE TABLE tb_cliente
(
  id BIGINT DEFAULT nextval('seq_id_cliente') PRIMARY KEY,
  nome TEXT
);

CREATE TABLE tb_venda
(
  id BIGINT DEFAULT nextval('seq_id_venda') PRIMARY KEY,
  id_cliente BIGINT REFERENCES tb_cliente(id),
  produto TEXT,
  valor NUMERIC(9,2),
  datahora TIMESTAMP DEFAULT now()
);

You can use the function currval() to retrieve the primary key identifier from the parent table to record it as the foreign key identifier in the daughter tables, see only:

-- CADASTRANDO CLIENTE (TABELA PAI)
INSERT INTO tb_cliente (nome) VALUES ('FULANO DE TAL');

-- CADASTRANDO VENDAS DO CLIENTE (TABELAS FILHAS)
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'LAPIS', 1.50);
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'CANETA', 3.35 );
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'CADERNO', 7.25);
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'BORRACHA', 0.50);

-- CADASTRANDO CLIENTE (TABELA PAI)
INSERT INTO tb_cliente (nome) VALUES ('BELTRANO DA SILVA');

-- CADASTRANDO VENDAS DO CLIENTE (TABELAS FILHAS)
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'APONTADOR', 3.30);
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'LAPISEIRA', 3.35);
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'REGUA', 7.25);
INSERT INTO tb_venda (id_cliente, produto, valor) VALUES (currval('seq_id_cliente'), 'GIZ DE CERA', 0.50);

Consultation:

SELECT
  v.id AS id_venda,
  v.id_cliente AS id_cliente,
  c.nome AS nome_cliente,
  v.produto AS nome_produto,
  v.valor AS valor_venda,
  v.datahora AS datahora_venda
FROM
  tb_venda AS v
JOIN
  tb_cliente AS c ON (v.id_cliente = c.id)
ORDER BY
  id_cliente,
  id_venda;

Exit:

| id_venda | id_cliente |      nome_cliente | nome_produto | valor_venda |              datahora_venda |
|----------|------------|-------------------|--------------|-------------|-----------------------------|
|        1 |          1 |     FULANO DE TAL |        LAPIS |         1.5 | 2020-06-03T21:14:23.033329Z |
|        2 |          1 |     FULANO DE TAL |       CANETA |        3.35 | 2020-06-03T21:14:23.033329Z |
|        3 |          1 |     FULANO DE TAL |      CADERNO |        7.25 | 2020-06-03T21:14:23.033329Z |
|        4 |          1 |     FULANO DE TAL |     BORRACHA |         0.5 | 2020-06-03T21:14:23.033329Z |
|        5 |          2 | BELTRANO DA SILVA |    APONTADOR |         3.3 | 2020-06-03T21:14:23.033329Z |
|        6 |          2 | BELTRANO DA SILVA |    LAPISEIRA |        3.35 | 2020-06-03T21:14:23.033329Z |
|        7 |          2 | BELTRANO DA SILVA |        REGUA |        7.25 | 2020-06-03T21:14:23.033329Z |
|        8 |          2 | BELTRANO DA SILVA |  GIZ DE CERA |         0.5 | 2020-06-03T21:14:23.033329Z |

See working on Sqlfiddle

1

For those who come from Mysql the auto_increment In Postgresql the operation is a little different, because it uses sequences (sequences) to control the "auto increment".

The Postgresql auto increment feature is not just an option in the column but a series of factors that together determine the effect of adding one (or n to its primary key), they being: Sequence (sequence) ? A Postgresql resource used to generate sequential numbers Nextval ? It is a Postgresql function used to get the next value of a Quence. Default value (default value)? A resource provided in a column to determine a default value that the field will assume if nothing is reported in the Insert command.

This all comes together to creating a Serial field. Example: ALTER TABLE example ADD COLUMN id Serial;

Or else: CREATE TABLE example ( serial id NOT NULL )

In this case, the id column will be inserted and will use the 3 resources above and we can observe the return message: NOTICE: ALTER TABLE will create implicit Sequence "exemplo_id_seq" for serial column "example.id" Query successfully executed without results in 396 ms. source of knowledge: https://www.oficinadanet.com.br/artigo/postgresql/como_criar_um_campo_auto-increment_no_postgresql

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.