SHOW statements in Doltgres

May 13, 2025

3 min read

Last month we announced the Beta release of Doltgres, the world's first and only version-controlled postgres-compatible SQL database. We're still hard at work delivering incremental improvements.

Today we're going to talk about Doltgres's support for SHOW statements that are not part of standard SQL or the Postgres dialect. They're a handy way to inspect your schema from any SQL client. Let's jump in.

SHOW DATABASES

The SHOW DATABASES statement does just what you think: give you a list of your databases on the current server.

postgres=> show databases;
       Database
----------------------
 information_schema
 postgres
 sql_alchemy_big_demo
(3 rows)

postgres=> create database new_database;
CREATE DATABASE
postgres=> show databases;
       Database
----------------------
 information_schema
 new_database
 postgres
 sql_alchemy_big_demo
(4 rows)

This is similar to the psql command \l, except you can use it in any client, not just psql.

SHOW TABLES

SHOW TABLES lists the tables in the current database.

postgres=> show tables;
 Tables_in_postgres
--------------------
(0 rows)

postgres=> create table t1 (a int primary key, b text);
CREATE TABLE
postgres=> create table t2 (c int primary key, d text);
CREATE TABLE
postgres=> show tables;
 Tables_in_postgres
--------------------
 t1
 t2
(2 rows)

This is similar to the psql command \d, except you can use it in any client, not just psql.

SHOW SCHEMAS

SHOW SCHEMAS lists the schemas in the current database.

postgres=> show schemas;
    schema_name
--------------------
 dolt
 pg_catalog
 public
 information_schema
(4 rows)

postgres=> create schema new_schema;
CREATE SCHEMA
postgres=> show schemas;
    schema_name
--------------------
 dolt
 new_schema
 pg_catalog
 public
 information_schema
(5 rows)

This is similar to the psql command \dn, except you can use it in any client, not just psql.

SHOW SEQUENCES

SHOW SEQUENCES lists all the sequences in the current database.

postgres=> show sequences;
 sequence_schema | sequence_name
-----------------+---------------
(0 rows)

postgres=> create table t3 (a serial primary key, b text);
CREATE TABLE
postgres=> show sequences;
 sequence_schema | sequence_name
-----------------+---------------
 public          | t3_a_seq
(1 row)

This is similar to the psql command \ds, except you can use it in any client, not just psql.

SHOW INDEXES

SHOW INDEXES lists all the indexes in the current database.

postgres=> show indexes from t3;
   Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed   | Null | Index_type | Comment | Index_comment | Visible | Expression
  -------+------------+----------+--------------+-------------+-----------+-------------+----------+--------  +------+------------+---------+---------------+---------+------------
   t3    |          0 | PRIMARY  |            1 | a           |           |           0 |          |          |      | BTREE      |         |               | YES     |
  (1 row)

This is similar to the psql command \di, except you can use it in any client, not just psql.

SHOW CREATE TABLE

SHOW CREATE TABLE returns the CREATE TABLE statement that will create the table named with its current schema.

postgres=> show create table t3;
 Table |                         Create Table
-------+--------------------------------------------------------------
 t3    | CREATE TABLE "t3" (                                         +
       |   "a" integer NOT NULL DEFAULT (nextval('public.t3_a_seq')),+
       |   "b" text,                                                 +
       |   PRIMARY KEY ("a")                                         +
       | )
(1 row)

Note that there are some schema elements in Postgres tables that can't be included in a single CREATE TABLE statement, notably creating non-primary indexes. These will be added in a future release.

There's no psql equivalent to this statement.

What's the point of these?

Well, as we've said several times now: you can use these statements in any client or workbench, not just psql. If you've ever peeked under the hood, the psql client issues very complex queries to return its schema information. For example, here's the query it uses for \d, to list tables.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind 
    WHEN 'r' THEN 'table' 
    WHEN 'v' THEN 'view' 
    WHEN 'm' THEN 'materialized view' 
    WHEN 'i' THEN 'index' 
    WHEN 'S' THEN 'sequence' 
    WHEN 't' THEN 'TOAST table' 
    WHEN 'f' THEN 'foreign table' 
    WHEN 'p' THEN 'partitioned table' 
    WHEN 'I' THEN 'partitioned index' 
  END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Good luck remembering all that! We think SHOW TABLES and SHOW SEQUENCES are a lot easier, which is why we implemented them.

It's also worth calling out that these statements are all lifted from, or inspired by, MySQL. MySQL is dying, but in many ways it has a more modern interface than Postgres. Cockroach, another Postgres-compatible database, came to the same conclusion and included these statements in their Postgres parser, which Doltgres's parser is forked from.

Conclusion

Work continues on Doltgres. We implemented these SHOW statements because they're so helpful in diagnosing issues that our customers run into. But there's still lots of work to do and features to implement. If you have a feature you want us to hit first, please file an issue to let us know! We love hearing from our customers.

Questions about using the beta release? Find a bug you want fixed? Come by our Discord to talk to our engineering team and meet other Doltgres users.

Blog