Find Replica Identity for a Postgres table

Question

Is there a way to see what kind of replica identity a Postgres table has, whether using pgAdmin or through a query?

Equiponderate

Answer 1

You can query the pg_class system catalog:

SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid = 'mytablename'::regclass;

Submergible

It's also useful to know all tables with no primary key if you use default logical replica identity - dba.stackexchange.com/questions/29932/… – Felder

If you set the replica identity of a table to an index you could find the relevant index with SELECT indexrelid::regclass FROM pg_index WHERE indrelid='mytablename'::regclass AND indisreplident; – Roxanneroxburgh

Answer 2

Find replica identify for multiple tables. The query will list the tablename as its replica identity status.

SELECT oid::regclass,CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid in ('public.testtable1'::regclass,'public.testtable1'::regclass);

Crowded

Answer 3

Improving the best answer by Laurenz Albe, this query list for all tables in a schema:

SELECT 
table_name, 
oid,
CASE relreplident
    WHEN 'd' THEN 'default'
    WHEN 'n' THEN 'nothing'
    WHEN 'f' THEN 'full'
    WHEN 'i' THEN 'index'
END AS replica_identity
FROM pg_class JOIN information_schema.tables t
ON oid = CONCAT('"',table_name,'"')::regclass
WHERE table_schema = '<your_schema>' 

Why am I using concat? Just due upper case table names.

Johnnajohnnie