This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

PostgreSql pivot table reports

PostgreSql or protocol-compatible database (like Amazon Redshift) can be used as a data source with SQL-compatible database connector.

There are no any limitations on the dataset size; your PostgreSql should be able to execute aggregate queries fast enough (in seconds; 2 minutes max). In case of large datasets you may pre-aggregate the data with materialized view, apply some filters on indexed columns, or use TimescaleDB extension for real-time aggrations.

PostgreSql connection setup

Connection String should be a valid connection string for NpgSql driver; for example:

Host=hostName;Port=5432;Database=db;User ID=user;Password=password;
Host Specifies the host name of the machine on which the PostgreSql is running.
Do not use "localhost" or LAN server name; use only public IP address or server's domain name.
Port The TCP port of the PostgreSQL server.
Database The PostgreSQL database to connect to.
User ID The username to connect with.
Password The password to connect with.
SSL Mode Specify SSL Mode=Require to force SSL or if your PostgreSql allows only SSL connections.
Trust Server Certificate Specify Trust Server Certificate=True to allow self-signed SSL server certificates.
Server Compatibility Mode Specify Redshift if your configure a connection to Amazon Redshift.
Specify NoTypeLoading if your configure a connection to QuestDB.

QuestDB notes

SeekTable can access QuestDB with Postgres wire protocol that is by default accessible via 8812 port. To configure a QuestDB-based cube choose PostgreSql as a database connector and specify this connection string:

Host=QUEST_DB_IP;Port=8812;Database=qdb;User ID=admin;Password=quest;ServerCompatibilityMode=NoTypeLoading;

To access QuestDB on "localhost" you can easily deploy a self-hosted SeekTable version (docker).

Known limitations:


Server certificate was not accepted or The remote certificate is invalid according to the validation procedure
Add Trust Server Certificate=True to the connection string to disable SSL certificate validation (needed if your server uses self-signed certificate).
ERROR: operator does not exist
This error usually occurs with flat-table reports that have a filter for some column and data type of this column is not comparable with the specified value (PostgreSql uses strict typing). To fix this you can cast the column to the datatype needed for the comparison. Typical cases:
  • if error message contains "operator does not exist: text > numeric" (comparison may be different) and your filter is something like some_column < 5 this means that some_column has TEXT datatype. To fix this go to the cube configuration form, find the dimension with Name "some_column" and add a Parameter to define SQL expression with a cast: some_column::NUMERIC.
  • if error message contains "operator does not exist: date = text" and your filter is something like some_column="2019-05-15" this means that column's data type is Date (or DateTime) and it cannot be compared with a TEXT value. To fix this go to the cube configuration form, find the dimension with Name=some_column and add a Parameter to define SQL expression with a cast; for example: some_column::date, some_column::timestamp.
Out of the range of DateTime (year must be between 1 and 9999)
This happens when "Select Query" returns a timestamp column with value(s) that cannot be represented by .NET DateTime object (usually because of year part). In some cases this may be fixed by adding ConvertInfinityDateTime=True to the connection string; if this doesn't help you need to exclude unpresentable timestamps from the query results (either by WHERE filtering or by timestamp normalization with an SQL expression).
ERROR: The field 'some_column' has a type currently unknown to Npgsql
This error occurs when "Select Query" returns a column that has a data type that is unknown to Npgsql driver. For example, this error may occur if you have a 'citext' column.
To fix this it is enough to cast this field to a known data type like ::TEXT (custom SQL expression for the dimension may be specified in "Parameters").