Connector for PostgreSQL – Features and settings

Page content

Connector features

The following functionality is available when you access data with the connector for PostgreSQL.


Feature	Supported?
Load methods	Import (in-memory) External (in-database) On-demand
Custom queries	Yes
Stored procedures	Yes
Custom connection properties	No
Single sign-on with identity provider	No
Authoring in web client	No
Included in Spotfire distribution on Linux	Yes

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for PostgreSQL.


Option	Description
Server	The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon. Example with port number: MyDatabaseServer:1234
Authentication method	The authentication method to use when logging into the database. Choose between Windows authentication and Database authentication. Windows authentication When using Windows authentication, e.g., Kerberos, the access token of the logged in user will be used. Users that have been given the appropriate access rights will be able to connect and read data. Domain credentials are not stored in the analysis file. Username and password With username and password the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog. If credentials are found in the analysis file they will be used to automatically authenticate against the database. If no credentials or credentials profiles are found in the analysis file all who open the file will be prompted for database credentials. Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail.
Use SSL	Determines if Secure Sockets Layer (SSL) is enabled for connections to the database.
SSL mode	[Only available when you select Use SSL.] Determines the SSL mode to use in connections to the database. The following options are available: allow SSL encryption is used only if the server requires it. prefer SSL should be used if the server supports it. require SSL must always be used. verify-ca [To use this option, a certificate file is required.] SSL must be used, and the server's CA certificate must be verified. verify-full [To use this option, a certificate file is required.] SSL must be used, the server's CA certificate must be verified, and server host name must match the certificate's host name. See the official PostgreSQL documentation for more information about how to use SSL mode.
Embed certificate file	[Only available when you select Use SSL.] The SSL certificate file to use for verifying the database server, when you select SSL mode verify-ca or verify-full. To embed a certificate, click Browse and select a certificate file on your computer. The certificate file is embedded and stored in the Spotfire data source. Tip: Because the certificate file is embedded, other users do not have to have the certificate file on their computers. What certificate file should I use? Note: If you are working against a public Spotfire Server (not on-premises), you can only use certificate files with public keys. The certificate file must match the CA certificate on the database server. It is recommended to only use certificate files with public keys. It can be a security risk to use certificate files with private keys, because the certificate file is embedded in the Spotfire data source, and potentially also in analysis files. To be able to embed certificate files with private keys, you must be part of a user group with the preference setting `AllowEmbeddingCertificatesWithPrivateKeys` enabled for the PostgreSQL connector. As an administrator, you can edit preference settings for users and groups in the Administration Manager. Tip: For efficient reuse and maintainability, it is recommended that you use shared connection data sources in the library. Note: If you do not embed a certificate file, the PostgreSQL ODBC driver attempts to use a certificate file in a default directory. If you use this method, you must place the certificate file in this directory on all computers that run Automation Services or web player services. For more information about how the driver handles certificate files by default, see the official documentation from PostgreSQL.
View	Open the Certificate dialog in Windows, where you can view information about the embedded certificate file.
Remove	Remove the embedded certificate file.
Database	Select the database of interest from the drop-down list. Alternatively, type the name of the desired database.
Forbid genetic optimizer	Determines whether to use the setting Forbid genetic optimizer, in the PostgreSQL ODBC driver, in connections to the database.
Parse statements	Determines whether to use the setting Parse statements, in the PostgreSQL ODBC driver, in connections to the database.
Server side prepare	Determines whether to use the setting Server side prepare, in the PostgreSQL ODBC driver, in connections to the database.
Connection cache size	The number of rows in the tuple cache. The default value is 100 rows.
SysTable prefixes	Prefixes of table names in the database that should be treated as system tables. Separate values with a semicolon. Default value is `dd_`.
Connection timeout (s)	The maximum time, in seconds, allowed for a connection to the database to be established. The default value is 120 seconds.
Command timeout (s)	The maximum time, in seconds, allowed for a command to be executed. The default value is 1800 seconds.

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the PostgreSQL connector supports.


Database data type	Spotfire data type
BYTEA	Binary
GEOMETRY	Binary Note: This is not supported in Custom Queries.
BOOL	Boolean
MONEY	Currency
DATE	Date
TIMESTAMP	DateTime
TIMESTAMPTZ	DateTime
BIT	Integer Note: This is not supported in Custom Queries.
INT2	Integer
INT4	Integer
SMALLSERIAL	Integer
SERIAL	Integer
INT8	LongInteger
BIGSERIAL	LongInteger
FLOAT8	Real
FLOAT4	SingleReal
BPCHAR	String
VARCHAR	String
TEXT	String
INTERVAL	String Note: This is not supported in Custom Queries. It might not be possible to access INTERVAL data from some versions of Brytlyt.
CIDR	String
INET	String
MACADDR	String
XML	String
TIME	Time
TIMETZ	Time Note: This is not supported in Custom Queries.
NUMERIC(p,s)	When p <= 9 and s = 0: Integer/Int When 9 < p <= 18 and s = 0: LongInteger/Long When p <= 15: Real/Double Else: Currency/Decimal

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the PostgreSQL connector supports.


Function type	Functions supported
Date and Time	`Millennium, Year, Quarter, Month, Week, Day, DayOfMonth, DayOfYear, DayOfWeek, Hour, Minute, Second, Millisecond`
Conversion	`SN`
Math	`Degrees, Abs, Mod, Pi, Radians, Random, Scale, ACos, ACosd, ASin, ASind, Cos, Cosd, Cot, Cotd, Sin, Sind, Tan, Tand, Cbrt, Ceiling, Div, Exp, Floor, Ln, Log, Power, Round, Sign, Sqrt, ATan, ATand, ATan2, ATan2d`
Operators	`+, -, *, /, %, &`
Statistical	`Sum, Avg, Min, Max, First, Last, Count, UniqueCount, Corr, Covar_pop, Regr_avgx, Regr_avgy, Regr_count, Regr_intercept, Regr_r2, Regr_slope, Regr_sxx, Regr_sxy, Regr_syy, Stddev, Stddev_pop, Stddev_samp, Variance, Var_pop, Var_samp`
Text	`Concat, Initcap, LeftStr, Length, Lower, LTrim, Md5, RightStr, RTrim, Repeat, Replace, Reverse, Strpos, Trim, Upper, Bit_length, Char_length, Octet_length, Btrim, Chr, To_ascii, To_hex, Substr, Translate, Convert, Convert_From, Convert_To`

Binning is also supported by PostgreSQL.