To use a PostgreSQL database with the SQL Query tool we recommend creating the connection in Data Sync and saving it to the connection library. This prevents the need to re-authenticate and supply the connection details each time you want to use the SQL Query Tool.
To connect to PostgreSQL we recommend using our connector available from our GitHub site. Data Sync has the ability to install this from the File Menu. Simply got to File > Install Data Connector, and then select PostgreSQL from the dropdown and click OK.
This will install the connector and Data Sync will need closing and re-opening for the changes to take effect.
If you get an error saying it could not install the connector because it is "Unable to access folder", then this is because the folder is locked by Ouvvi. Please stop your Ouvvi services and try again.
You can then access the connector by clicking onto Connect Data Source and then going to SQL Database > PostgreSql. The numbers contained in the brackets are the driver version the connector is targeting. If you are after targeting a newer driver please contact us via firstname.lastname@example.org.
Enter in your connection string into the text box or you can build it out using the connection properties.
An example of the connection string you might use is:
For our connection we use the connection string:
Then click Connect & Create Library Connection to save it to your connection library.
This will first open a new window where you need to expand the tree to select a table to connect to, click OK once you have selected the table.
Then enter in a name for your connection and an optional description. Once it has been saved, you can refresh the connection library and under Databases you will now be able to see your PostgreSql database connection and expand it to select a table to connect to.
You can then access the SQL Query Tool by right clicking onto the connection (in the Connection Library Window) and selecting Open SQL Query Tool. This will open the SQL Query Tool with the connection to your PostrgeSQL Database and you can start writing a SQL Query as you need to.
At a minimum you will need to enter the following into the connection properties window:
The host name or IP address of the PostgreSQL Server to connect to.
The PostgreSQL database you are connecting to.
The TCP port of the PostgreSQL server.
THe username to connect to the PostgreSQL server. This is not required if you are using integrated security.
The password to connect to the PostgreSQL server. This is not required if you are using integrated security.