I had a programmer today, ask me about how to check to see if a table is an unlogged table. He had already found the basic query on Stack Overflow with a google search, and asked me if it was OK to use. The basic query is actually incomplete due to not including the schema name.
The information that is needed is held in two table: pg_catalog.pg_class and pg_catalog.pg_namespace. using these two tables we can find out if the table is a permanent table, unlogged table, or temporary table and this works with PostgreSQL 9.1+.
Lets create three tables, so we can see what type of results we get.
CREATE TABLE IF NOT EXISTS public.test1 (id SERIAL); CREATE UNLOGGED TABLE IF NOT EXISTS public.test2 (id SERIAL); CREATE TEMP TABLE IF NOT EXISTS public.test3 (id SERIAL);
Now lets write our query. We join the pg_class to pg_namespace so that we can get the schema name and filter by the schema, so that we don't get all the system tables in our results.
SELECT n.nspname AS schema, c.relname AS table, c.relpersistence AS table_type FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' OR n.nspname LIKE 'pg_temp_%';
┌─────────────┬──────────────┬────────────┐ │ schema │ table │ table_type │ ╞═════════════╪══════════════╪════════════╡ │ public │ test1_id_seq │ p │ │ public │ test1 │ p │ │ public │ test2_id_seq │ p │ │ public │ test2 │ u │ │ pg_temp_228 │ test3_id_seq │ t │ │ pg_temp_228 │ test3 │ t │ └─────────────┴──────────────┴────────────┘ (6 rows)
As you can see we have our three types of tables: p = permanent table, u = unlogged table, and t = temporary table. If there becomes more values in the future, you will be able to find them in the docs for pg_class The results also returned the sequences attached to the tables. If we had indexes, toast tables, etc, they would have also been returned. We actually need to just return our exact table. We can do this by adjusting our query filter to look up our exact table.
SELECT n.nspname AS schema, c.relname AS table, c.relpersistence AS table_type FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relpersistence = 'u' AND n.nspname = 'public' AND c.relname = 'test2';
┌────────┬───────┬────────────┐ │ schema │ table │ table_type │ ╞════════╪═══════╪════════════╡ │ public │ test2 │ u │ └────────┴───────┴────────────┘ (1 row)
This always gives us back a single record if the table is an unlogged table or no results if the table is a permanent table, temporary table, or does not exist.
If you wish to simplify your program that looks at the results, you can update the query to return a true/false.
SELECT COUNT(*)::integer::boolean AS unlogged FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relpersistence = 'u' AND n.nspname = 'public' AND c.relname = 'test2';
┌──────────┐ │ unlogged │ ╞══════════╡ │ True │ └──────────┘ (1 row)
This always gives us back a single record. True if the table is an unlogged table or False if the table is a permanent table, temporary table, or does not exist. We can test these additional test cases:
SELECT COUNT(*)::integer::boolean AS unlogged FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relpersistence = 'u' AND n.nspname = 'public' AND c.relname = 'test1';
┌──────────┐ │ unlogged │ ╞══════════╡ │ False │ └──────────┘ (1 row)
SELECT COUNT(*)::integer::boolean AS unlogged FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relpersistence = 'u' AND n.nspname = 'public' AND c.relname = 'test3';
┌──────────┐ │ unlogged │ ╞══════════╡ │ False │ └──────────┘ (1 row)
SELECT COUNT(*)::integer::boolean AS unlogged FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relpersistence = 'u' AND n.nspname = 'public' AND c.relname = 'test4';
┌──────────┐ │ unlogged │ ╞══════════╡ │ False │ └──────────┘ (1 row)
You might ask how does this boolean work? The COUNT return a bigint, so we cast that to an integer. This is OK to do as we are going to have either 0 (p, t or does not exist) or 1 (u) rows, so the value will never exceed the max value for an integer. We can now cast the integer to a boolean, 0 (p, t or does not exist) = False, 1 (u) = True.
COUNT(*)::integer::boolean