How to insert sequence in non-self-incrementing field

Question

How to insert sequence in non-self-incrementing field

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

I need to insert records into one table with data from another by continuing the sequence of a column that already exists in it, but the column I want to continue the sequence is not self-incrementing and the table does not have a primary key.

Situation:

Suppliers table:

cod | nome
--------------------
3   | Aloha
12  | Castor
21  | Tesla

Contacts table:

cod | nome
--------------------
1   | Canada
2   | Asteca
3   | Limiar

I would like to be able to insert something like this in the contacts table:

INSERT INTO contatos (cod, nome)
SELECT (SELECT MAX(cod) FROM contatos)+1, nome FROM fornecedores;

Expecting this result:

cod | nome
--------------------
1   | Canada
2   | Asteca
3   | Limiar
4   | Aloha
5   | Castor
6   | Tesla

But the result is this (repeating the code):

cod | nome
--------------------
1   | Canada
2   | Asteca
3   | Limiar
4   | Aloha
4   | Castor
4   | Tesla

How do I solve this problem?

sql postgresql

asked by anonymous 21.11.2017 / 14:12

2 answers

1

If you can ensure that the vendor table has sequential code starting at 1 you can do:

INSERT INTO contatos (cod, nome)
SELECT (SELECT MAX(cod) FROM contatos) + cod, nome FROM fornecedores;

If you can not do this then you would need to see the criteria to find a suitable solution.

Of course I would not rule out turning cod of contatos into a serial column, even just to do this operation.

You can do a procedure or a code in another language that does column by column. It would be too simple a code, just create a variable and increment it.

If I think of anything else I'll post it here. It is possible that you have some trick with SELECT simple and pure that gives to increase in the hand. I know I can JOIN , but I find it very complicated and gambi .

21.11.2017 / 14:26

How to get value from a database column using C # Why does bitmap indexing work well for low cardinality domains?

score 3 · Accepted Answer

Try this:

INSERT INTO contatos (cod, nome)
SELECT 
    (coalesce((SELECT 
         MAX(cod) 
     FROM contatos),0)+ row_number() OVER ()), nome FROM fornecedores;

I put the coalesce, so in case the contatos table is empty, do not give problems, and I used row_number as increment. Using +1 As an increment will only work on a insert , since the value of Max(cod) will be the same on all rows returned by select so the returned value is repeated.

I put it in SQLFiddle: link