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

SHOW { TABLES
     | COLUMNS FROM tableName
     | PARTITIONS FROM tableName
     | CREATE TABLE tableName
     | CREATE VIEW viewName
     | USER [userName]
     | USERS
     | GROUPS [userName]
     | SERVICE ACCOUNT [accountName]
     | SERVICE ACCOUNTS [userName]
     | PERMISSIONS [entityName]
     | SERVER_VERSION
     | PARAMETERS };

Description

• SHOW TABLES 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 CREATE TABLE returns a DDL query that allows you to recreate the table.
• SHOW CREATE VIEW returns a DDL query that allows you to recreate a view.
• SHOW USER shows user secret (enterprise-only)
• SHOW GROUPS shows all groups the user belongs or all groups in the system (enterprise-only)
• SHOW USERS shows all users (enterprise-only)
• SHOW SERVICE ACCOUNT displays details of a service account (enterprise-only)
• SHOW SERVICE ACCOUNTS displays all service accounts or those assigned to the user/group (enterprise-only)
• SHOW PERMISSIONS displays permissions of user, group or service account (enterprise-only)
• SHOW SERVER_VERSION displays PostgreSQL compatibility version
• SHOW PARAMETERS shows configuration keys and their matching env_var_name, their values and the source of the value

Examples

SHOW TABLES

show tablesDemo this query

SHOW TABLES;

table_name
ethblocks_json
trades
weather
AAPL_orderbook
trips

SHOW COLUMNS

show columnsDemo this query

SHOW COLUMNS FROM trades;

column	type	indexed	indexBlockCapacity	symbolCached	symbolCapacity	symbolTableSize	designated	upsertKey
symbol	SYMBOL	false	0	true	256	42	false	false
side	SYMBOL	false	0	true	256	2	false	false
price	DOUBLE	false	0	false	0	0	false	false
amount	DOUBLE	false	0	false	0	0	false	false
timestamp	TIMESTAMP	false	0	false	0	0	true	false

SHOW CREATE TABLE

retrieving table ddlDemo this query

SHOW CREATE TABLE trades;

ddl
CREATE TABLE trades (symbol SYMBOL CAPACITY 256 CACHE, side SYMBOL CAPACITY 256 CACHE, price DOUBLE, amount DOUBLE, timestamp TIMESTAMP) timestamp(timestamp) PARTITION BY DAY WAL WITH maxUncommittedRows=500000, o3MaxLag=600000000us;

This is printed with formatting, so when pasted into a text editor that support formatting characters, you will see:

CREATE TABLE trades (
	symbol SYMBOL CAPACITY 256 CACHE,
	side SYMBOL CAPACITY 256 CACHE,
	price DOUBLE,
	amount DOUBLE,
	timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
WITH maxUncommittedRows=500000, o3MaxLag=600000000us;

Per-column Parquet encoding

When columns have per-column Parquet encoding or compression overrides, they appear in the SHOW CREATE TABLE output:

CREATE TABLE sensors (
	ts TIMESTAMP,
	temperature DOUBLE PARQUET(rle_dictionary, zstd(3)),
	humidity FLOAT PARQUET(rle_dictionary),
	device_id VARCHAR PARQUET(default, lz4_raw),
	status INT
) timestamp(ts) PARTITION BY DAY BYPASS WAL;

Storage policy clause

When a storage policy is attached to a table (Enterprise only), the policy renders as a STORAGE POLICY(...) clause in the SHOW CREATE TABLE output:

SHOW CREATE TABLE sensor_data;

CREATE TABLE 'sensor_data' (
    ts TIMESTAMP,
    value DOUBLE
) timestamp(ts) PARTITION BY DAY
STORAGE POLICY(TO PARQUET 3 DAYS, DROP NATIVE 10 DAYS, DROP LOCAL 1 MONTH) WAL;

Stages that are not configured on the policy are omitted from the clause. A disabled policy (ALTER TABLE ... DISABLE STORAGE POLICY) still renders — the disabled state is not part of the DDL. See ALTER TABLE SET STORAGE POLICY.

Enterprise variant

QuestDB Enterprise will include an additional OWNED BY clause populated with the current user.

For example,

CREATE TABLE trades (
	symbol SYMBOL CAPACITY 256 CACHE,
	side SYMBOL CAPACITY 256 CACHE,
	price DOUBLE,
	amount DOUBLE,
	timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
WITH maxUncommittedRows=500000, o3MaxLag=600000000us
OWNED BY 'admin';

This clause assigns permissions for the table to that user.

If permissions should be assigned to a different user, please modify this clause appropriately.

SHOW CREATE VIEW

retrieving view ddl

SHOW CREATE VIEW my_view;

ddl
CREATE VIEW 'my_view' AS (SELECT ts, symbol, price FROM trades);

This returns the CREATE VIEW statement that would recreate the view, including any DECLARE parameters if the view is parameterized.

SHOW PARTITIONS

SHOW PARTITIONS FROM my_table;

index	partitionBy	name	minTimestamp	maxTimestamp	numRows	diskSize	diskSizeHuman	readOnly	active	attached	detached	attachable	hasParquetGenerated	isParquet	parquetFileSize
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	false	false	-1
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	false	false	-1
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	false	false	-1
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	false	false	-1

See table_partitions() for the full column list, including hasParquetGenerated, isParquet, and parquetFileSize.

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)
• sensitive: if it is a sensitive value (passwords)
• reloadable: if the value can be reloaded without a server restart

property_path	env_var_name	value	value_source	sensitive	reloadable
http.min.net.connection.limit	QDB_HTTP_MIN_NET_CONNECTION_LIMIT	64	default	false	false
line.http.enabled	QDB_LINE_HTTP_ENABLED	true	default	false	false
cairo.parquet.export.row.group.size	QDB_CAIRO_PARQUET_EXPORT_ROW_GROUP_SIZE	100000	default	false	false
http.security.interrupt.on.closed.connection	QDB_HTTP_SECURITY_INTERRUPT_ON_CLOSED_CONNECTION	true	conf	false	false
pg.readonly.user.enabled	QDB_PG_READONLY_USER_ENABLED	true	conf	false	true
pg.readonly.password	QDB_PG_READONLY_PASSWORD	****	default	true	true
http.password	QDB_HTTP_PASSWORD	****	default	true	false

You can optionally chain SHOW PARAMETERS with other clauses:

-- This query will return all parameters where the value contains 'tmp', ignoring upper/lower case
(SHOW PARAMETERS) WHERE value ILIKE '%tmp%';

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

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

-- Show all the parameters that have been modified from their defaults, via conf file or env variable
(SHOW PARAMETERS) WHERE  value_source <> 'default';

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 and views with filters and using the results as part of a function:

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