Using primary keys in postgrSQL

Question

I currently have a table:

userID | color | quantity
-------------------------

where userID is the primary key. My problem is when I try to insert to the DB (that already has one item from the same ID) I get the error: pq: duplicate key value violates unique constraint I am using Go with lib/pq package to insert. I am unsure whether I have the wrong idea of what to use a PK for, or if I don't understand what kind of table I need to make

Answer 1

Primary key is a key that uniquely identifies each single row in the table and therefore needs to be unique. If you need more rows with same userID in your table then userID cannot be a primary key.

When you specify column (or group of columns) as a primary key PostgreSQL will put uniqueness constraint on it so it cannot happen that two rows in table have same contents of that column - that's why you see constraint violation error.

You can solve this problem by adding another ID column that will have unique value for each row (e.g. autoincremented sequence) and making it primary key instead of userID.

Here is a detailed tutorial on primary key in Postgres to give you a better understanding of primary key usage.