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

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)

Examples

SHOW TABLES

SHOW TABLES;

table
weather
my_table
...

SHOW COLUMNS

SHOW COLUMNS FROM my_table;

column	type	indexed	indexBlockCapacity	symbolCached	symbolCapacity	designated
symb	SYMBOL	true	1048576	false	256	false
price	DOUBLE	false	0	false	0	false
ts	TIMESTAMP	false	0	false	0	true
s	STRING	false	0	false	0	false

SHOW PARTITIONS

SHOW PARTITIONS FROM my_table;

index	partitionBy	name	minTimestamp	maxTimestamp	numRows	diskSize	diskSizeHuman	readOnly	active	attached	detached	attachable
0	WEEK	2022-W52	2023-01-01 00:36:00.0	2023-01-01 23:24:00.0	39	98304	96.0 KiB	false	false	true	false	false
1	WEEK	2023-W01	2023-01-02 00:00:00.0	2023-01-08 23:24:00.0	280	98304	96.0 KiB	false	false	true	false	false
2	WEEK	2023-W02	2023-01-09 00:00:00.0	2023-01-15 23:24:00.0	280	98304	96.0 KiB	false	false	true	false	false
3	WEEK	2023-W03	2023-01-16 00:00:00.0	2023-01-18 12:00:00.0	101	83902464	80.0 MiB	false	true	true	false	false

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_path	env_var_name	value	value_source
http.min.net.connection.rcvbuf	QDB_HTTP_MIN_NET_CONNECTION_RCVBUF	1024	default
http.health.check.authentication.required	QDB_HTTP_HEALTH_CHECK_AUTHENTICATION_REQUIRED	true	default
pg.select.cache.enabled	QDB_PG_SELECT_CACHE_ENABLED	true	conf
cairo.sql.sort.key.max.pages	QDB_CAIRO_SQL_SORT_KEY_MAX_PAGES	2147483647	env

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_type	enabled
Password	false
JWK Token	false
REST Token	false

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_type	enabled
Password	false
JWK Token	false
REST Token	false

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;

permission	table_name	column_name	grant_option	origin
SELECT			t	G

SHOW PERMISSIONS user

SHOW PERMISSIONS admin;

permission	table_name	column_name	grant_option	origin
SELECT			t	G
INSERT	orders		f	G
UPDATE	order_itme	quantity	f	G

SHOW PERMISSIONS

For a group

SHOW PERMISSIONS admin_group;

permission	table_name	column_name	grant_option	origin
INSERT	orders		f	G

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)

See also

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

• table_columns()
• tables()
• table_partitions()