Cannot insert into field with primary key constraint in PostgreSQL

Question

I have a "raw" table that looks like this (among other many fields):

 team_id |        team_name        
---------+-------------------------
       1 | Team1
       1 | Team1
       2 | Team2
       2 | Team2

I want to extract the team names and their id codes and create another table for them, so I created:

    CREATE TABLE teams (
        team_id integer NOT NULL,
        team_name varchar(50) NOT NULL,
        CONSTRAINT team_pkey PRIMARY KEY (team_id)
);

And I am planning to copy the data from the old table to the recently created one like this:

INSERT INTO teams(team_id,team_name)                                        
SELECT team_id,team_name FROM rawtable 
GROUP BY team_id, team_name;

At first I wasn't adding the GROUP BY part, and I was getting a message:

ERROR:  duplicate key value violates unique constraint "team_pkey"

I added the GROUP BY so it doesn't try to insert more than one row for the same team, but the problem still persist and I keep getting the same message.

I don't understand what is causing it. It looks like I am inserting single non duplicate rows into the table. What's the best way to fix this?

Are you sure, so it is how you describe. Your data, your SQL is working for me without problems. — Pavel Stehule
– Pavel Stehule, Commented May 30, 2015 at 17:10

user330315 · Accepted Answer · 2015-05-30 17:15:16Z

1

If two different teams with the same id are in raw_table e.g. (1, 'foo') and (1, 'bar') the group by will still return both, because those two are different.

If you just want to pick one of the rows for duplicate values of team_id then you should use something like this:

insert into teams (team_id,team_name)  
select distinct on (team_id) team_id, team_name
from rawtable
order by team_id;

The Postgres specific distinct on operator will make sure that only distinct values for team_id are returned.

answered May 30, 2015 at 17:15

user330315

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

1 Comment

telex-wap Over a year ago

Thank you, actually that was the problem I had. I didn't need to change my statement to find the bad row because I accidentally saw it, but I will mark your answer as accepted because of the useful example I can use for the future.

Zohar Peled · Accepted Answer · 2015-05-30 17:11:49Z

1

My best guess is that you have the same team_id for more then one team_name at least somewhere in your table. Try to add `Having count(*)=1 to your select statement

answered May 30, 2015 at 17:11

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

3 Comments

user330315 Over a year ago

having count(*) = 1 won't help if two different team_name have the same team_id because they are still considered different by a group by team_id, team_name

telex-wap Over a year ago

Actually that was it! I didn't copy the original raw table here because it was really long, but it seems that the id column had not been checked for duplicates, so there was one faulty row. I didn't even change the select as you suggest, because I just accidentally saw the offending row....

Zohar Peled Over a year ago

Oops, correct. Using max as joachim wrote is a better suggestion.

Joachim Isaksson · Accepted Answer · 2015-05-30 17:20:04Z

1

Since the team_id is unique in the destination table, two separate team names with the same id will create duplicates, one row for each name.

A simple fix is to group by team_id so that you only get a single row per id, and pick one of the names the team has (here we somewhat arbitrarily use MAX to get the last in alphabetical order)

INSERT INTO teams(team_id,team_name)                                        
 SELECT team_id, MAX(team_name) FROM rawtable 
 GROUP BY team_id

answered May 30, 2015 at 17:20

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Comments

potatopeelings · Accepted Answer · 2015-05-30 17:11:07Z

0

One of your Team1 or Team2 probably has some extra spaces or nonprintable characters. This would cause your group by to return multiple rows for Team_ID 1 or 2 causing the problem.

answered May 30, 2015 at 17:11

potatopeelings

41.2k8 gold badges97 silver badges121 bronze badges

2 Comments

Zohar Peled Over a year ago

This is sample data. For only 2 teams he would have inserted them manually faster then writing the question...

potatopeelings Over a year ago

Er... so was Team1 and Team2 in my example. The point was there could be extra spaces or nonprintable character causing his group by to return multiple rows.

tec · Accepted Answer · 2015-05-30 17:22:12Z

0

Try to use distinct in your query :

insert into teams (team_id,team_name) select distinct on (team_id) team_id, team_name from order by team_id;

edited May 30, 2015 at 17:22

answered May 30, 2015 at 17:08

tec

4110 bronze badges

1 Comment

Pavel Stehule Over a year ago

SELECT DISTINCT a,b .. and SELECT a,b GROUP BY a, b are equal.

Collectives™ on Stack Overflow

Cannot insert into field with primary key constraint in PostgreSQL

5 Answers 5

1 Comment

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related