Yeah, that error is annoying as hell. You're just trying to create a type or a table and Postgres throws ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" with SQLSTATE 23505. It happens out of nowhere, and the message doesn't tell you which key is duplicated. Let's cut to the chase.
The immediate fix (most common case)
Nine times out of ten, this isn't a real duplicate. It's a stale sequence in the system catalog. Postgres uses a sequence, pg_type_oid_seq, to hand out OIDs for new types. If that sequence's value is somehow behind the actual maximum OID in pg_type, the next insert tries to use an OID that already exists. That triggers the unique constraint on (typname, typnamespace) because the OID is part of the catalog row, and the conflict shows up on that index.
Here's the fix—run this as a superuser:
-- Check the current max OID in pg_type
SELECT max(oid) FROM pg_type;
-- Fix the sequence to be ahead of that max
SELECT setval('pg_type_oid_seq', (SELECT max(oid) FROM pg_type));
This forces the sequence to start from the highest existing OID plus one. But there's a subtlety: setval with two arguments sets the last value, so the next nextval returns that value plus one. If you want to be extra safe, you can add 1:
SELECT setval('pg_type_oid_seq', (SELECT max(oid) + 1 FROM pg_type), false);
The third parameter false tells Postgres that the value you passed is not the last value, so it will use it as the next value directly. Either works, but I prefer the second form because it leaves no ambiguity.
Why this works
What's actually happening here is that pg_type_oid_seq isn't guaranteed to stay in sync with the actual OIDs in the table. Normally it does, but certain operations can break that. For example, if you restored a dump with pg_restore that was taken before some types existed, or if you manually inserted rows into pg_type (which you shouldn't, but people do), the sequence gets out of sync. When you then create a new type, the sequence returns an OID that's already taken, and the insert fails with 23505 on the unique index that enforces (typname, typnamespace). The reason the error points to that index instead of the primary key is that the OID is the primary key, but the unique constraint on (typname, typnamespace) is checked first in some code paths. Either way, the root cause is the same: a duplicate OID attempt.
So by resetting the sequence to a value higher than any existing OID, you eliminate the collision. That's the real fix.
Less common variations
That sequence fix covers most cases, but not all. Here are other scenarios where you might see this error, and how to handle them.
1. Race condition: concurrent CREATE TYPE
If you have multiple sessions running CREATE TYPE or CREATE TABLE (which also creates a type) at the same time, and they both try to use the same OID from the sequence, you can hit this error. This is rare because the sequence is supposed to be atomic, but under parallel restore or heavy automation, it can happen. The fix here is to retry the failed statement. It's a transient error, not a permanent state. If you're using a script, wrap it in a retry loop. If it happens consistently, then it's not a race—it's the sequence issue above.
2. Dropped type that still exists in the catalog
Sometimes you drop a type, but a dependency or a failed transaction leaves a ghost entry in pg_type. The sequence might have moved past that OID, but the stale row still occupies the name/namespace combination. So even after fixing the sequence, you get a duplicate on (typname, typnamespace) because the name is already there. You'll see this if you try to create a type with the same name as a dropped one, and the error persists after the sequence fix.
To check for ghosts, query:
SELECT oid, typname, typnamespace, typrelid
FROM pg_type
WHERE typname = 'your_type_name';
If you find a row that shouldn't exist (for example, typrelid = 0 and typisdefined = false), you can delete it manually—but be careful. Deleting from system catalogs directly is risky. Only do it if you're sure the type is not referenced anywhere. Better yet, try to recreate the type with a different name to see if the error goes away, which confirms it's a name conflict.
3. After pg_upgrade or dump/restore
If you recently upgraded from an older Postgres version using pg_upgrade, or restored a dump from a different cluster, the sequence might be behind. The fix is the same as step 1, but you might also need to run ANALYZE on the system catalogs afterward to update statistics. I've seen cases where the planner got confused by stale stats, but the error itself is still the sequence.
4. Extension creation conflicts
Some extensions (like PostGIS or pg_trgm) create types at install time. If you're installing an extension and hit this error, it's almost always the sequence issue. The extension's script uses CREATE TYPE internally, and it fails the same way. Reset the sequence and retry the extension installation.
Prevention
The best way to avoid this is to never mess with the system catalogs manually. I know that's obvious, but a lot of people get into this by trying to clean up a dropped type and doing DELETE FROM pg_type WHERE .... Don't. Use DROP TYPE properly.
Also, be careful with pg_dump and pg_restore. If you're restoring into a database that already has objects, use --clean and --if-exists to drop existing objects first. Otherwise, you can end up with partial states that mess up the sequence.
Regularly check your sequence health with a query like:
SELECT last_value, is_called FROM pg_sequences WHERE schemaname = 'pg_catalog' AND sequencename = 'pg_type_oid_seq';
If last_value is less than max(oid) in pg_type, you're asking for trouble.
So, in short: run the setval command, test your operation again, and if it still fails, look for ghost rows. That's the whole game. Most people never get past the sequence fix, and it works.