Postgre provider types
Postgre Driver: psqlOBDC (32). For example: psqlodbc_09_01_0200-1.zip
Postgre Server: PostgreSQL Windows installers (Win x86-32)
SQL Language: PostgreSQL Manuals
Postgre Column Type Information (Text).
When creating or modifying a table using OleDb Admin, you can specify a specific Postgre column type from a list.
Postgre provider types
Additional qualifiers can be added. For example,
city references cities(city)
will check that all any city entered into the new table exists in the (cities) table. The Postgre provider type will be passed directly to the CREATE TABLE command and retained in the .OL grid file.
You can also add default values,
int4 SERIAL
to default to the next value for a sequential type. If you provide a column provider type when creating a table, SQL Offline passes those values on to CREATE TABLE.
CREATE TABLE "categories" ("categoryid" SERIAL NOT NULL, ...)
ALTER SEQUENCE "categories_categoryid_seq" START WITH 9
When creating a table, a column with an ISAUTOINCREMENT flag cause type SERIAL to be used. Additionally, the SEQUENCE for the column will be set above the highest existing value to be inserted as part of the table creation.
By default, Table and Column and index name identifiers will be converted to lower case. Set SqlPreferCaseSettings off (0) in HKEY_CURRENT_USER\Software\Interscape\Offline\1.0\Settings to override this behavior.
Identifiers are converted, by the database server, to lower case if not quoted. If you have both SqlColumnIdBracketSettings on (1) and SqlTableIdBracket's on (1) the identifiers held in the grid must match exactly those in the database.
If you have a single primary key column, you can select the KEYCOLUMN flag, and a primary key will be created as the table is created. If any column type has the ISAUTOINCRIMENT flag set, it will automatically become the primary key. If you are using multiple columns for primary keys, use File/Data Link to define a Primary Key index after creating the table. With Postgre, an index flagged as a primary key will automatically be called PRIMARY, regardless of the name indicated in the Data Link dialog.
Consider loading Customers from Nwind.mdb, as in the Crash Course Tutorial . Replace the connection string with a link to a Postgre server database. For example: Provider=MSDASQL.1;User ID=postgres;Extended Properties="DRIVER={PostgreSQL ANSI};DATABASE=flask;SERVER=localhost .
A Write Data command would create a table as follows:
|
CREATE TABLE "customers" ( |
and create keys and indexes using:
|
ALTER TABLE "customers" ADD PRIMARY KEY ("customerid")
|
If you wanted to specify any columns types other than varchar, enter them into Provider: in the column data dialog.
