Oct 30, 2017 10:17:03 AM by Deepak Vohra
PostgreSQL database is one of the leading open source databases, with unique features such as support for advanced datatypes including multidimensional arrays and user0defined types, common table expressions (CTEs), full outer joins, VALUES list, parallel queries, and materialized views.
PostgreSQL database may be run on several platforms, both local and cloud. In this two-article series we shall discuss using PostgreSQL database on localhost, AWS RDS, and Docker Engine with Toad Data Point. Toad Data Point is a database IDE that simplifies data source access, development and management. This article has the following sections:
We need to download the following software (Windows OS used):
-PostgreSQL Database (postgresql-9.6.3-3-windows-x64.exe)
-PostgreSQL ODBC Driver msi (psqlodbc_09_06_0410-x64.zip)
An AWS account is required, which may be created at https://aws.amazon.com/resources/create-account/.
To install PostgreSQL ODBC Driver extract the psqlodbc_09_06_0410-x64.zip file and double-click on the Windows installer file psqlodbc_x64.msi. The Setup wizard gets started, as shown in Figure 1. Click on Next.
Figure 1. PostgreSQL ODBC Driver Setup Wizard
Accept the End user Agreement and click on Next. In Custom Setup the ODBC Driver should be selected to be installed by default as shown in Figure 2. Click on Next.
Figure 2. Selecting Components to Install
Click on Install as shown in Figure 3.
Figure 3. Install
When the installation completes click on Finish as shown in Figure 4.
Figure 4. Completing Installation of PostgreSQL ODBC Driver
In this section we shall use PostgreSQL on localhost with Toad Data Point. First, we need to install PostgreSQL database on a local machine.
To install PostgreSQL on a local machine double-click on the postgresql-9.6.3-3-windows-x64.exe application. PostgreSQL Setup wizard gets started, as shown in Figure 5. Click on Next.
Figure 5. PostgreSQL Setup Wizard
Specify Password for super user postgres and click on Next as shown in Figure 6.
Figure 6. Specifying Password for Postgres User
In Port keep the default port 5432 and click on Next as shown in Figure 7.
Figure 7. Setting Port
In Advanced Options keep the Default locale and click on Next as shown in Figure 8.
Figure 8. Setting Locale
In Ready to Install click on Next to start installation as shown in Figure 9.
Figure 9. Ready to Install
The PostgreSQL database installation gets started, as shown in Figure 10.
Figure 10. PostgreSQL Database getting installed
When the installation completes click on Finish as shown in Figure 11.
Figure 11. Completing installation of PostgreSQL Database
From the PostgreSQL installation bin directory run the following command to start the psql client interface (CLI).
psql -U postgres
Specify the Password at the prompt to start the psql CLI, which displays the postgres=# command prompt, as shown in Figure 12.
Figure 12. The psql CLI
List the Schemas with the dn command as shown in Figure 13.
Figure 13. Listing Schemas
Before being able to connect with PostgreSQL with Toad Data Point we need to configure a Data Source for PostgreSQL database. Select Control Panel>Administrative Tools>Data Sources (ODBC) as shown in Figure 14.
Figure 14. Selecting Data Sources (ODBC)
The ODBC Data Source Administrator gets started, as shown in Figure 15. Click on Add.
Figure 15. ODBC Data Source Administrator>Add
In Create New Data Source select the PostgreSQL Unicode (x64) driver as shown in Figure 16 and click on Finish.
Figure 16. Selecting the PostgreSQL Unicode Driver
The PostgreSQL Unicode ODBC Driver Setup wizard gets started as shown in Figure 17.
Figure 17. PostgreSQL Unicode ODBC Driver Setup wizard
The Data Source name is filled in by default. Specify Database as public, Server as localhost, User Name as postgres, Port as 5432, and the Password as shown in Figure 18. Click on Test.
Figure 18. Configuring and Testing Data Source Connection
The Connection Test dialog should indicate that the connection is successful as shown in Figure 19.
Figure 19. Connection Successful
Click on Save in the Data Source Setup wizard as shown in Figure 20.
Figure 20. Save
A new Data Source gets added in the ODBC Data Source Administrator, as shown in Figure 21. Click on OK.
Figure 21. Data Source added for PostgreSQL Database
In this section we shall connect with PostgreSQL Database with Toad Data Point, create a database table, and query the database table. Select Connect>New Connection in Toad Data Point as shown in Figure 22.
Figure 22. Connect>New Connection
Select PostgreSQL in the data sources list as shown in Figure 23.
Figure 23. Selecting PostgreSQL Data Source
In the Create New Connection wizard select the General tab and select the Use data source name checkbox as shown in Figure 24. Select the Data source name as the data source configured in the preceding section. Specify User as the super user postgres. Specify Password for the super user postgres. Click on Connect.
Figure 24. Create New Connection>Connect
A connection to PostgreSQL database gets added to the Navigation Manager, as shown in Figure 25. The AutoCommit ON message in the lower panel indicates that the transactions auto-commit is set to on.
Figure 25. Connection to PostgreSQL Added
Next, we shall run a SQL script to create a database table in PostgreSQL database. Click on SQL Editor link as shown in Figure 26.
Figure 26. SQL Editor
Run a SQL script in the SQL Editor to create a database table. Any SQL script may be run. A sample script is listed:
CREATE TABLE wlslog(time_stamp VARCHAR(255) PRIMARY KEY,category VARCHAR(255),type VARCHAR(255),
servername VARCHAR(255), code VARCHAR(255),msg VARCHAR(255));
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:16-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:17-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STARTING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:18-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to ADMIN');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:19-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RESUMING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:20-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000361','Started WebLogic AdminServer');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:21-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to RUNNING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg) VALUES('Apr-8-2014-7:06:22-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000360','Server started in RUNNING mode');
Copy and paste the sample script in the SQL Editor and click on Execute scripts as shown in Figure 27.
Figure 27. Running SQL Script
As the Messages in Figure 28 indicate, the SQL statements run and create a database table and add data to the table. The database table gets listed in Object Explorer>Tables, also shown in Figure 28.
Figure 28. Database Table added in PostgreSQL Database
Next, we shall query the database table added; for which the same SQL Editor may be used or a new SQL Editor started. To start a new SQL Editor right-click on the connection in Navigation Manager and select New SQL Editor as shown in Figure 29.
Figure 29. New SQL Editor
In the SQL Editor add a SQL statement to query the database table. The SQL Editor provides auto-suggest features to build a SQL statement. Add “SELECT * FROM” and the SQL Editor lists the tables to select from, as shown in Figure 30. Select the wlslog table.
Figure 30. Adding a SQL Statement to Query
To run the SQL statement click on Execute scripts as shown in Figure 31.
Figure 31. Running SQL Query
The result set for the query gets listed as shown in Figure 32.
Figure 32. Result Set for Query
In this article we discussed using PostgreSQL database running on a local machine. In part two of this series, we shall discuss using PostgreSQL database on AWS RDS and Docker with Toad Data Point.
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.