Send Feedback
Skip to content

pgwire Examples

This section demonstrates how to connect SQL-based tools to a KDB-X database using pgwire as a proxy.

Integration with Tableau

  1. Install the PostgreSQL driver: Tableau Drivers
  2. In Tableau Desktop:
  3. Select PostgreSQL from the data source list.
  4. Enter the following connection properties:
Property Example
Server 127.0.0.1
Port 5434
Database databasefile
Username qadmin
Password any non-empty
Require SSL Unticked
  1. Click Sign In.

Integration with Power BI

  1. Open Power BI Desktop.
  2. Select PostgreSQL database from the connector list.
  3. Configure the connection:
Property Example
Server 127.0.0.1:5434
Database any (not required)
Mode Import or DirectQuery
  1. When prompted for authentication, enter:
Property Example
Username kdb user (for example, admin)
Password matching password

Note

If you're unsure of credentials, see setting usernames in KDB-X.

Integration via psql (CLI)

For lightweight testing, use the PostgreSQL CLI client:

psql -h 127.0.0.1 -p 5434 -U admin

Once connected, you can issue basic queries like:

SELECT * FROM trade WHERE sym = 'AAPL' LIMIT 10;

If you encounter errors like “table does not exist,” verify that the q process has loaded the relevant table and that it conforms to ANSI SQL rules (for example, flat, atomic columns).

How schema appears in SQL clients

pgwire exposes tables loaded into q using a simplified mapping. For example, a q table like:

quote:([] sym:`AAPL`GOOG; bid:101.2 99.5; ask:101.5 100.0)

appears in SQL clients as:

quote
------
sym | bid  | ask
AAPL| 101.2|101.5
GOOG| 99.5 |100.0

pgwire emulates basic PostgreSQL metadata views such as:

  • pg_tables – Lists available tables
  • pg_type – Lists visible types

This allows GUI clients to auto-populate fields and build drag-and-drop queries.

Logging failed queries

To log failed SQL queries in q:

.z.pg:{$[$[0=type x;".s.spg"~x 0;0b];$[10h=type r:@[value;.sql.last:0N!x;::];[.sql.err,:enlist`query`error!(x;r);r];r];value x]}

This function writes errors to .sql.err. You can query the error log later with:

select from .sql.err

Note

To log all queries (not just failures), wrap .s.spg and write entries to an audit table.

Sample query flow: Tableau → pgwire → q

Here's an example query trace:

1. Tableau builds query: SELECT sym, price FROM trade WHERE sym = 'AAPL'
2. pgwire receives query and logs input
3. pgwire calls .s.spg with raw SQL
4. q evaluates and returns data as rowset
5. pgwire encodes result using Postgres wire protocol
6. Tableau receives and displays result

This pattern is the same for all SQL clients using pgwire.

Next steps: