How to insert sequence in non-automated field

Question

I need to insert records in a table with data from another continuing the sequence of a column that already exists in it, but the column I want to continue the sequence is not auto-incremented and the table has no primary key.

Situation:

Table suppliers:

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

Table contacts:

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

I would like to be able to make an Insert more or less like this in the table contacts:

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 being this (repeating the code):

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

How to solve this problem?

Answer 1

Try it like this:

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

I put the coalesce, in case the table contatos is empty, gives no problems, and I used the row_number as increment. Using +1 as increment will only work in one insert, for the value of Max(cod) will be the same on all lines returned by select so the value returned is repeated.

I put in Sqlfiddle: http://sqlfiddle.com/#! 17/45bc5/4

Answer 2

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;

I put in the Github for future reference.

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

Of course I wouldn’t rule out turning cod of contatos in a serial column, even if only to do this operation.

Can you make a Procedure or code in another language that makes column by column. It would be too simple code, just create a variable and increment it.

If I think of anything else I put here. It’s possible I have some trick with SELECT simple and pure that can be increased in the hand. I know that you can make a JOIN, but I find it very complicated and Gambi too.