ERROR: null value in column " " of relation " " violates not-null constraint DETAIL: Failing row contains (21, null, null, null, ...)

FlavioR 0
Viewed 23

I am new on PostgresQL, and I am experiencing the error in the title.

I have table_a with an empty column, which I want to fill up with data from a column in table_b, upon a match.

table_a:

id | name | town  | town_id  |

1  | name1 | city1 |  -      | 
2  | name2 | city1 |  -      |
3  | name3 | city2 |  -      |
4  | name4 | city2 |  -      |
5  | name5 | city3 |  -      |

table_b

id | town_name 

1  | city1 
2  | city2 
3  | city3

I want INSERT the table_b.id INTO table_a.town_id ON town = town_name

INSERT INTO
    table_a(town_id) 
SELECT 
    table_b.id
FROM
    table_b
JOIN
    table_a
    ON town = town_name;

Problem is that I get the error in the title, in a row which is not actually existing (e.g.: table_a has 20 rows, but error is on row 21...). What is happening there? It seems it is correctly inserting the data, but then it does not stop at the end of the table, so the not null constraints is triggered.

sql-insert postgresql
1 Answers

FIXED:

As suggested by @Frank Heikens I had to use UPDATE

UPDATE table_a
SET town_id = table_b.id
FROM table_b
WHERE town = town_name;
FlavioR0
Related