SHOW keyword

This keyword provides table, column, and partition information including metadata. The SHOW keyword is useful for checking the designated timestamp setting column, the partition attachment settings, and partition storage size on disk.

Syntax

Flow chart showing the syntax of the SHOW keyword

Description

  • SHOW returns all the tables.
  • SHOW COLUMNS returns all the columns and their metadata for the selected table.
  • SHOW PARTITIONS returns the partition information for the selected table.
  • SHOW PARAMETERS shows configuration keys and their matching env_var_name, their values and the source of the value
  • SHOW SERVER_VERSION displays PostgreSQL compatibility version
  • SHOW USER shows user secret (enterprise-only)
  • SHOW USERS shows all users (enterprise-only)
  • SHOW GROUPS shows all groups the user belongs or all groups in the system (enterprise-only)
  • SHOW SERVICE ACCOUNTS displays all service accounts or those assigned to the user/group (enterprise-only)
  • SHOW SERVICE ACCOUNT displays details of a service account (enterprise-only)
  • SHOW PERMISSIONS displays permissions of user, group or service account (enterprise-only)
  • SHOW SERVER_CONF shows the content of QuestDB's server.conf configuration file. (enterprise-only)

Examples

SHOW TABLES

SHOW TABLES;
table
weather
my_table
...

SHOW COLUMNS

SHOW COLUMNS FROM my_table;
columntypeindexedindexBlockCapacitysymbolCachedsymbolCapacitydesignated
symbSYMBOLtrue1048576false256false
priceDOUBLEfalse0false0false
tsTIMESTAMPfalse0false0true
sSTRINGfalse0false0false

SHOW PARTITIONS

SHOW PARTITIONS FROM my_table;
indexpartitionBynameminTimestampmaxTimestampnumRowsdiskSizediskSizeHumanreadOnlyactiveattacheddetachedattachable
0WEEK2022-W522023-01-01 00:36:00.02023-01-01 23:24:00.0399830496.0 KiBfalsefalsetruefalsefalse
1WEEK2023-W012023-01-02 00:00:00.02023-01-08 23:24:00.02809830496.0 KiBfalsefalsetruefalsefalse
2WEEK2023-W022023-01-09 00:00:00.02023-01-15 23:24:00.02809830496.0 KiBfalsefalsetruefalsefalse
3WEEK2023-W032023-01-16 00:00:00.02023-01-18 12:00:00.01018390246480.0 MiBfalsetruetruefalsefalse

SHOW PARAMETERS

SHOW PARAMETERS;

The output demonstrates:

  • property_path: the configuration key
  • env_var_name: the matching env var for the key
  • value: the current value of the key
  • value_source: how the value is set (default, conf or env)
property_pathenv_var_namevaluevalue_source
http.min.net.connection.rcvbufQDB_HTTP_MIN_NET_CONNECTION_RCVBUF1024default
http.health.check.authentication.requiredQDB_HTTP_HEALTH_CHECK_AUTHENTICATION_REQUIREDtruedefault
pg.select.cache.enabledQDB_PG_SELECT_CACHE_ENABLEDtrueconf
cairo.sql.sort.key.max.pagesQDB_CAIRO_SQL_SORT_KEY_MAX_PAGES2147483647env

You can optionally chain SHOW PARAMETERS with other clauses:

-- This query will return all parameters where the value contains 'C:'
SHOW PARAMETERS WHERE value ILIKE '%C:%';

-- This query will return all parameters where the property_path is not 'cairo.root' or 'cairo.sql.backup.root', ordered by the first column
SHOW PARAMETERS WHERE property_path NOT IN ('cairo.root', 'cairo.sql.backup.root') ORDER BY 1;

-- This query will return all parameters where the value_source is 'env'
SHOW PARAMETERS WHERE value_source = 'env';

SHOW USER

SHOW USER; --as john

or

SHOW USER john;
auth_typeenabled
Passwordfalse
JWK Tokenfalse
REST Tokenfalse

SHOW USERS

SHOW USERS;
name
admin
john

SHOW GROUPS

SHOW GROUPS;

or

SHOW GROUPS john;
name
management

SHOW SERVICE ACCOUNT


SHOW SERVICE ACCOUNT;

or


SHOW SERVICE ACCOUNT ilp_ingestion;

auth_typeenabled
Passwordfalse
JWK Tokenfalse
REST Tokenfalse

SHOW SERVICE ACCOUNTS


SHOW SERVICE ACCOUNTS;

| name |

|-------------|

| management |

| svc1_admin |


SHOW SERVICE ACCOUNTS john;

| name |

|-------------|

| svc1_admin |


SHOW SERVICE ACCOUNTS admin_group;

| name |

|-------------|

| svc1_admin |

SHOW PERMISSIONS FOR CURRENT USER

SHOW PERMISSIONS;
permissiontable_namecolumn_namegrant_optionorigin
SELECTtG

SHOW PERMISSIONS user

SHOW PERMISSIONS admin;
permissiontable_namecolumn_namegrant_optionorigin
SELECTtG
INSERTordersfG
UPDATEorder_itmequantityfG

SHOW PERMISSIONS

For a group

SHOW PERMISSIONS admin_group;
permissiontable_namecolumn_namegrant_optionorigin
INSERTordersfG

For a service account


SHOW PERMISSIONS ilp_ingestion;

| permission | table_name | column_name | grant_option | origin |

|------------|------------|-------------|--------------|--------|

| SELECT | | | t | G |

| INSERT | | | f | G |

| UPDATE | | | f | G |

SHOW SERVER_VERSION

Shows PostgreSQL compatibility version.

SHOW SERVER_VERSION;
server_version
12.3 (questdb)

SHOW SERVER_CONF

SHOW SERVER_CONF;
namevalue
config.validation.stricttrue
query.timeout.sec60

See also

The following functions allow querying tables with filters and using the results as part of a function: