Using PostgreSQL Database on Localhost with Toad Data Point

Introduction

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:

• Setting the Environment
• Installing PostgreSQL ODBC Driver
• Using Toad Data Point with PostgreSQL on Localhost
• Running PostgreSQL Database on Localhost
• Configuring PostgreSQL ODBC Data Source
• Connecting to PostgreSQL on Localhost with Toad Data Point

Setting the Environment

We need to download the following software (Windows OS used):

–Toad Data Point

–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/.

Installing PostgreSQL ODBC Driver

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

Using Toad Data Point with PostgreSQL on Localhost

In this section we shall use PostgreSQL on localhost with Toad Data Point. First, we need to install PostgreSQL database on a local machine.

Running PostgreSQL Database on Localhost

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

Configuring PostgreSQL ODBC Data Source

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

Connecting to PostgreSQL on Localhost with Toad Data Point

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

Conclusion

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.