public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ed Sabol <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error
Date: Wed, 18 Feb 2026 20:47:27 -0500
Message-ID: <[email protected]> (raw)

Using PostgreSQL 15.16, I have some SQL for creating a materialized view that's inside a larger transaction which randomly emits the following error:

ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(largetable, 20488) already exists.

The error doesn't happen 100% of the times I try to execute this transaction. Maybe 30% of the time? Also, I've had it happen on multiple separate PostgreSQL servers.

I'm not entirely sure how to interpret these ERROR and DETAIL messages. Can someone explain it?

I've tried the following queries to try to figure out what typnamespace = 20488 refers to:

SELECT nspname FROM pg_namespace WHERE oid = 20488;
SELECT count(*) FROM pg_catalog.pg_type WHERE typname = 'targetable' AND typnamespace = 20488;

Neither query provides any useful information. The latter always returns zero immediately immediately prior to the SQL error.

Here's the (slightly sanitized) SQL which causes the error:

CREATE MATERIALIZED VIEW someschema.largetable AS
(
   SELECT 
      row_number() OVER () AS __row,
      sub.*,
      public.somefunc1(afield) AS s_result1,
      public.somefunc2(anotherfield) AS s_result2,
      public.somefunc3(somefield1, somefield2) AS s_result3
   FROM (
      SELECT * FROM someschema.largetable_upd_1
      UNION ALL SELECT * FROM someschema.largetable_upd_2
      UNION ALL SELECT * FROM someschema.largetable_upd_3
      UNION ALL SELECT * FROM someschema.largetable_upd_4
      UNION ALL SELECT * FROM someschema.largetable_upd_5
      UNION ALL SELECT * FROM someschema.largetable_upd_6
      UNION ALL SELECT * FROM someschema.largetable_upd_8
      UNION ALL SELECT * FROM someschema.largetable_upd_9
      UNION ALL SELECT * FROM someschema.largetable_upd_10
   ) sub
);

public.somefunc1, public.somefunc2, and public.somefunc3 return three types defined in PgSphere. I have the latest PgSphere installed.
Any ideas as to how to eliminate the error?

My first thought was to change the asterisks to explicit lists of columns.

Any assistance would be appreciated!

Thanks,
Ed

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Duplicate key value violates unique constraint "pg_type_typname_nsp_index" error
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox