List all the postgres clients/drivers that are tested

[mpsq]

[jaugsburger]

CLI

PSQL v12

Support for SELECT, INSERT, CREATE, DROP, TRUNCATE

Database UI Clients

SQuirreL SQL with Postgres JDBC driver

Driver version 42.2.12

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works with parameters	Works with parameters
INSERT	Works with parameters	Works with parameters

Note: There are several metadata queries that are sent by client/driver that are not supported. This means that auto-completion and table/column name highlighting will not work as expected in the UI.

For reference purposes, this is the query that we don't currently support from SQuirreL

Metadata queries

SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'

SELECT nspname AS TABLE_SCHEM, NULL AS TABLE_CATALOG FROM pg_catalog.pg_namespace  WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_'  OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_'  OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_'))  ORDER BY TABLE_SCHEM

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'p' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'p' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS,  '' as TYPE_CAT, '' as TYPE_SCHEM, '' as TYPE_NAME, '' AS SELF_REFERENCING_COL_NAME, '' AS REF_GENERATION  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND (false  OR ( c.relkind IN ('r','p') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )  OR ( c.relkind = 'r' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') )  OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'p' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'p' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS,  '' as TYPE_CAT, '' as TYPE_SCHEM, '' as TYPE_NAME, '' AS SELF_REFERENCING_COL_NAME, '' AS REF_GENERATION  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE E'%' AND (false  OR ( c.relkind IN ('r','p') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' )  OR ( c.relkind = 'r' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') )  OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, NULL, NULL, NULL, d.description AS REMARKS, 2 AS PROCEDURE_TYPE,  p.proname || '_' || p.oid AS SPECIFIC_NAME  FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p  LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid)  LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc')  LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog')  WHERE p.pronamespace=n.oid and pg_function_is_visible(p.oid) ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, p.oid::text 

select null as type_cat, n.nspname as type_schem, t.typname as type_name,  null as class_name, CASE WHEN t.typtype='c' then 2002 else 2001 end as data_type, pg_catalog.obj_description(t.oid, 'pg_type')  as remarks, CASE WHEN t.typtype = 'd' then  (select CASE when typname = E'date' then 91 when typname = E'time[]' then 2003 when typname = E'_name' then 2003 when typname = E'numeric[]' then 2003 when typname = E'timestamptz[]' then 2003 when typname = E'refcursor' then 2012 when typname = E'timetz[]' then 2003 when typname = E'money[]' then 2003 when typname = E'bit' then -7 when typname = E'uuid' then 1111 when typname = E'_money' then 2003 when typname = E'int2' then 5 when typname = E'name[]' then 2003 when typname = E'int4' then 4 when typname = E'_varchar' then 2003 when typname = E'uuid[]' then 2003 when typname = E'xml' then 2009 when typname = E'int8' then -5 when typname = E'text' then 12 when typname = E'_numeric' then 2003 when typname = E'_timestamp' then 2003 when typname = E'json[]' then 2003 when typname = E'_bool' then 2003 when typname = E'date[]' then 2003 when typname = E'varchar[]' then 2003 when typname = E'_oid' then 2003 when typname = E'_json' then 2003 when typname = E'int2[]' then 2003 when typname = E'int4[]' then 2003 when typname = E'bpchar' then 1 when typname = E'name' then 12 when typname = E'bit[]' then 2003 when typname = E'int8[]' then 2003 when typname = E'timestamptz' then 93 when typname = E'refcursor[]' then 2003 when typname = E'_bpchar' then 2003 when typname = E'bpchar[]' then 2003 when typname = E'bytea' then -2 when typname = E'bool' then -7 when typname = E'bool[]' then 2003 when typname = E'_refcursor' then 2003 when typname = E'numeric' then 2 when typname = E'oid' then -5 when typname = E'point' then 1111 when typname = E'_timestamptz' then 2003 when typname = E'_float4' then 2003 when typname = E'bytea[]' then 2003 when typname = E'json' then 1111 when typname = E'timestamp[]' then 2003 when typname = E'_char' then 2003 when typname = E'_time' then 2003 when typname = E'_float8' then 2003 when typname = E'timestamp' then 93 when typname = E'_bytea' then 2003 when typname = E'oid[]' then 2003 when typname = E'_bit' then 2003 when typname = E'_int8' then 2003 when typname = E'_date' then 2003 when typname = E'varchar' then 12 when typname = E'_int4' then 2003 when typname = E'float8' then 8 when typname = E'_point' then 2003 when typname = E'_uuid' then 2003 when typname = E'float4' then 7 when typname = E'point[]' then 2003 when typname = E'_int2' then 2003 when typname = E'_timetz' then 2003 when typname = E'char[]' then 2003 when typname = E'float8[]' then 2003 when typname = E'money' then 8 when typname = E'_xml' then 2003 when typname = E'float4[]' then 2003 when typname = E'text[]' then 2003 when typname = E'xml[]' then 2003 when typname = E'char' then 1 when typname = E'time' then 92 when typname = E'_text' then 2003 when typname = E'timetz' then 92 else 1111 end from pg_type where oid=t.typbasetype) else null end as base_type from pg_catalog.pg_type t, pg_catalog.pg_namespace n where t.typnamespace = n.oid and n.nspname != 'pg_catalog' and n.nspname != 'pg_toast' and t.typtype IN ('c','d')  order by data_type, type_schem, type_name

SELECT setting FROM pg_catalog.pg_settings WHERE name='max_index_keys

PG Admin

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works with parameters	Works with parameters
INSERT	Works with parameters	Works with parameters

Note: There are several metadata queries that are sent by client/driver that are not supported. This means that auto-completion and table/column name highlighting will not work as expected in the UI.

I'm yet to capture these

DataGrip

Postgres JDBC driver version 42.2.5*

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works with parameters	Works with parameters
INSERT	Works with parameters	Works with parameters

Note: There are several metadata queries that are sent by client/driver that are not supported. This means that auto-completion and table/column name highlighting will not work as expected in the UI.

For reference purposes, these are the queries that we don't currently support from DataGrip

Metadata query

select current_database() as a, current_schemas(false) as b

When auto-commit is off, these 2 queries fail to parse

SHOW TRANSACTION ISOLATION LEVEL
SAVEPOINT JDBC_SAVEPOINT_0

Visualization tools

Grafana via Posgres plugin

Support SELECT

Note 1: Support any other query (INSERT/TRUNCATE/DROP) - Care needs to be taken by administrator of Grafana and QuestDB server instances as Grafana does not check whether the queries are read or write and will send them to QuestDB regardless. It is advisable that the admin sets the QuestDB server to read-only mode in the server.conf security settings.

Note 2: Works using simple query mode

Note 3: There are 2 metadata queries that are sent by plugin that are not supported. This means that auto-completion and table/column name highlighting will not work as expected in the UI.

Queries:

SELECT quote_ident(table_name) FROM information_schema.tables WHERE table_schema IN (
  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s
) ORDER BY table_name

SELECT quote_ident(column_name) FROM information_schema.columns WHERE 
table_schema IN (
  SELECT
    CASE WHEN trim(s[i]) = '"$user"' THEN user ELSE trim(s[i]) END
  FROM
    generate_series(
      array_lower(string_to_array(current_setting('search_path'),','),1),
      array_upper(string_to_array(current_setting('search_path'),','),1)
    ) as i,
    string_to_array(current_setting('search_path'),',') s
) AND table_name = 'wewe' AND data_type IN ('bigint','integer','double precision','real') AND column_name <> 'time' ORDER BY column_name

Languages

Java

JDBC driver version 42.2.12

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works with parameters	Works with parameters
INSERT	Works with parameters	Works with parameters

GO

lib/pq

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works but parameters not supported	UNKNOWN
INSERT	Works but parameters not supported	UNKNOWN

Nodejs

pg lib

	AUTO-COMMIT=OFF	AUTO-COMMIT=ON
SELECT	Works with parameters	UNKNOWN
INSERT	Works with parameters	UNKNOWN

[mpsq]

Awesome, thanks Joan!

[mpsq]

REMOVED