Creating a PostgreSQL Dataset

Creating a database that uses PostgreSQL is just as easy as SQL.  Here I'll step through the process and show screenshots of the various steps.  Although I'm using a locally installed PostgreSQL database, the process for a remote one is the same.

2017-12-04_21-40-10.png

Identifying the dataset requires the entry of the name and ID.  Then I selected the PostgreSQL dataset type and clicked next.

2017-12-04_21-40-50.png

Next I clicked the KwikSelect icon

2017-12-05_8-41-11.png

Then I picked the appropriate driver.  Note that if you select the ANSI driver you will still be forced to enable unicode characters (you cannot uncheck the option later in the wizard).  Therefore you should pick the unicode driver.

2017-12-05_8-40-44.png

If you don't see any drivers then you need to run the application stack builder and install the appropriate drivers.  As you can see below, I've already installed the drivers I'll need.

2017-12-05_8-42-09.png

The connection string is not much different from SQL Server.  Check with your database administrator to verify that your driver is correct.

2017-12-04_21-46-01.png

Clicking OK will then enter the connection string into the dialog.  You can change the string manually if you need to for some reason.  Though you cannot change the password as it's encrypted here.

Encrypted password

Encrypted password

At the last step in the configuration process I made sure to uncheck the GIS columns option.  I don't currently have PostGIS setup and configured.

2017-12-04_22-15-28.png

The creation of the database finished without error.

2017-12-04_22-17-22.png

Now I can inspect the schema that's been created.  The exact same number of tables exist in PostgreSQL as within SQL.  

2017-12-05_8-36-53.png

From within the client you can see the configuration in the setup information dialog.

2017-12-05_8-49-58.png

The schema manager feature in the Enterprise Studio behaves the same, with all the same options.  To verify that I manually removed some indexes.  Running check should highlight this.

2017-12-05_8-54-56.png

As I hoped the feature works and tells me I'm missing some indexes.

2017-12-05_8-56-44.png

You can also migrate from SQL to PostgreSQL by using the export feature in the Enterprise Studio.

2017-12-05_9-11-27.png

However, take care to plan ahead.  Any dataset created with the GIS options will require the PostGIS be installed locally.  The header of the export will include a message indicating such.

2017-12-05_9-13-18.png