Connect to PostgreSQL

PostgreSQL is an open source relational database management system developed by a worldwide team of volunteers.

The PostgreSQL connector allows you to access data from PostgreSQL based databases within Data Studio.

In this article:

• How to connect to PostgreSQL
• Notes
• Limits
• Related resources

How to connect to PostgreSQL

This connector uses the PostgreSQL JDBC driver to connect a Data Studio data source to a single PostgreSQL database table.

Supported versions

We've tested Data Studio on the following PostgreSQL versions:

• 9.6
• 10
• 11
• 12

If you experience issues using a supported version, please let us know in the Data Studio Help Community. You can add and vote on feature requests here.

To connect

1. Sign in to Data Studio.
2. In the top left, click  then select Data Source.
3. Select the PostgreSQL connector
4. Configure access to your database using one of the connection options (see below)
5. Click AUTHENTICATE
   1. You will see a list of tables in that database
6. Select a table
7. Click CONNECT.

You can now access all of the columns in the table as fields in your reports.

Connection options

BASIC

To connect with a hostname or IP address, you'll need to provide the following information:

• Hostname or IP address
• Port (Optional)
• Database
• Username
• Password

JDBC URL

To connect via a JDBC URL, provide the following connection information:

• JDBC URL
  Example

  
  jdbc:postgresql://<hostname or IP address>[:<port>]/<database>
  • Username
  • Password

If you use the JDBC hostname option, you can make sure it's accessible with public DNS lookup tools such as Whois.

Enable SSL (optional)

Data Studio supports secure (encrypted) connections to the server using the TLS (Transport Layer Security) protocol. TLS is also referred to as SSL (Secure Sockets Layer). To enable a secure connection, check Enable SSL, then provide your SSL configuration files.

Enable client authentication (optional)

When this box is checked, you can upload a client private key and a client certificate if required to access your database.

Learn more about PostgreSQL secure connections.

Custom Query

Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Data Studio uses this custom SQL as an inner select statement for each generated query to the database.

Notes

Supported data types

The PostgreSQL connector currently supports the following data types:

• Numeric
• Character
• Boolean
• Date/Time (except Intervals)

When Data Studio encounters a column of a non-supported type, it will not create a field for that column.

Learn more about PostgreSQL data types.

Firewall access

To allow Data Studio to connect to and query databases that are behind a firewall, you will need to open access to specific IP addresses.  

Alert: The IP addresses currently used by Data Studio to connect to your databases will change in early 2023. To ensure that your reports continue to work properly after the change, you must open the IP addresses listed below.

Show the list of IP addresses

142.251.74.0/23 
2001:4860:4807::/48 (Optional, for platforms that support IPv6)
74.125.0.0/16

Limits of the PostgreSQL connector

• You can query a maximum of 150K rows using this connector.
• Column headers (field names) must use ASCII characters only. Non-ASCII characters aren't supported.

Related resources

Create a data source