sql query Insert Into with subquery

Question

I am trying to add some values in a table with a Select procedure for 1 value like this.

INSERT INTO product(productname, productprice, productcategorynumber) 
VALUES ('12', '12', (SELECT productcategorynumber 
                     FROM product_category 
                     WHERE productcategoryname = 'DRINKS'));

I Receive the following error:

duplicate key value violates unique constraint

thanx for help

Your syntax should work fine. The error is about the values you're trying to insert. Perhaps the productname is unqiue, and there already is a row with productname = '12' — Andomar
– Andomar, Commented Jun 23, 2013 at 16:31

Denis de Bernardy · Accepted Answer · 2013-06-23 17:00:45Z

1

No need for values:

INSERT INTO product(productname, productprice, productcategorynumber) 
SELECT '12', '12', productcategorynumber 
FROM product_category 
WHERE productcategoryname = 'DRINKS';

I'm presuming these are not the actually values you want, but it'll hopefully put you on the right track. (Is there a unique constraint on productname? If so, you need to adjust the query accordingly.)

answered Jun 23, 2013 at 17:00

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

grepit · Accepted Answer · 2013-06-23 16:57:53Z

0

There is a constraint on one of the fields :(productname, productprice, productcategorynumber). Check your table to see which field has a unique constraint on it and then you need insert distinct value for that field.

Here is a statement which will list the constraints for you in PostgreSQL:

SELECT  constraint_name, constraint_type FROM information_schema.table_constraints
WHERE table_name = 'product'

you should also be able use the below command to see the constraints for this product table

\d product

edited Jun 23, 2013 at 16:57

answered Jun 23, 2013 at 16:33

grepit

22.6k6 gold badges110 silver badges82 bronze badges

Collectives™ on Stack Overflow

sql query Insert Into with subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related