Can I write an insert query using data from two joined tables?

Question

I have a request SELECT:

SELECT id_default_value, id_type FROM ntg_attribute, ntg_module_attribute 
WHERE ntg_attribute.id_module_attribute = ntg_module_attribute.id;

This returns 2 columns, id_default_valueand id_type. I would like to use this data as the basis for a query INSERTin another table ntg_default_value, using id_default_valueas a key and id_typeas an inserted value.

Almost everything here, but not quite:

INSERT INTO ntg_default_value (id, id_type) 
SELECT id_default_value, id_type FROM ntg_attribute, ntg_module_attribute 
WHERE ntg_attribute.id_module_attribute = ntg_module_attribute.id;

This gives me:

ERROR:  duplicate key value violates unique constraint "pk_ntg_default_value"

What I'm actually trying to do is perhaps? If so, how do I build a query?

(PostgreSQL 8.4.6)

Answer 1

The constraint name "pk_ntg_default_value" probably means that you are violating the primary key constraint on the ntg_default_value table.

. , id id_type, , GROUP BY , , id_devault_value id_type. :

INSERT INTO ntg_default_value (id, id_type)
SELECT id_default_value, id_type 
FROM ntg_attribute, ntg_module_attribute 
WHERE ntg_attribute.id_module_attribute = 
      ntg_module_attribute.id 
GROUP BY id_default_value, id_type