Hi Greg,
I've recently had a number of emails about data types and type mapping in
the pgAdmin Migration Wizard, so I've posted this to the interfaces list in
the hope it may be of use to others.
Let me explain how the Migration Wizard works - it may make things clearer.
There are 2 MS DAO interfaces, 1 to the source database (this is used as a
full blown connection as we don't know what the source database is - in
other words, we're relying on DAO to describe the database). The other
interface is the PostgreSQL one which is initiated at logon. All we use this
for is executing queries. Everything we learn about PostgreSQL we extract
from the system tables.
This leads us to the type map. On one side you have all the ODBC/DAO data
types (the db... ones). These are fixed and cannot be added to or removed.
On the other side are the PostgreSQL types, the ones in the dropdown lists.
The short delay when you click on the Type Map button is pgAdmin retrieving
the data types that your system can handle - this ensures that you can use
any custom types you have created, or any new types included in future
releases of PostgreSQL.
When the Migration runs, pgAdmin looks at the source table, figures out the
data type for each column and then looks up in the type map what to
re-create that column as. The default mappings are hard coded into pgAdmin
and then written to the registry.
The upshot of all this is that in your case, I have had a quick look, and
the Access 'number' type is being reported by DAO as a dbByte (anyone have
any idea why?), so I would suggest remapping dbByte to an int or float type.
Hope this helps,
Dave.
--
Dave Page, Network & Systems Manager, The Vale Housing Association Ltd.
dpage@vale-housing.co.uk
http://www.vale-housing.co.uk (Work)
http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
Beer can be a permanent solution - but only if you have enough of it!
> -----Original Message-----
> From: Greg LaPolla [mailto:GLapolla@Linksys.com]
> Sent: 13 April 1999 17:33
> To: Dave Page
> Subject: RE: Bug Reports
>
>
> Sorry to bug you again, but I found another glitch, Access
> has a field type
> 'Number' there is no selection for that in the type map, and
> I get a problem
> creating an index here is the excerpts from the log:
>
>
> conn=147783952, query='CREATE TABLE "Menus" ( "MenuCat" "bytea",
> "MenuSubCat" "bytea", "MenuItem" "bytea", "MenuText" "text",
> "MenuMacro"
> "text", "Parameter" "text" );'
> conn=147783952, query='COMMIT'
>
>
> all the data is migrated in and the commit is issued, then
> here is what
> happens:
>
> conn=147783952, query='BEGIN'
> conn=147783952, query='CREATE UNIQUE INDEX
> "Menus_PrimaryKey_idx" ON "Menus"
> USING btree ("MenuCat", "MenuSubCat", "MenuItem");'
> ERROR from backend during send_query: 'ERROR: Can't find a
> default operator
> class for type 17.'
> conn=147783952, query='COMMIT'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=17,
> errmsg='Error creating
> the table'
>
> ------------------------------------------------------------
> hdbc=147783952, stmt=147856152, result=0
> manual_result=0, prepare=0, internal=0
> bindings=0, bindings_allocated=0
> parameters=0, parameters_allocated=0
> statement_type=4, statement='CREATE UNIQUE INDEX
> "Menus_PrimaryKey_idx" ON "Menus" USING btree ("MenuCat",
> "MenuSubCat",
> "MenuItem");'
> stmt_with_params='CREATE UNIQUE INDEX
> "Menus_PrimaryKey_idx" ON "Menus" USING btree ("MenuCat",
> "MenuSubCat",
> "MenuItem");'
> data_at_exec=-1, current_exec_param=-1, put_data=0
> currTuple=-1, current_col=-1, lobj_fd=-1
> maxRows=0, rowset_size=1, keyset_size=0,
> cursor_type=0,
> scroll_concurrency=1
> cursor_name='SQL_CUR08D01B18'
>
>
> the three fields being indexed are getting assigned bytea,
> the access type
> is numeric, any Ideas on this ?
>
>
> I see postgres 5.0 beta has support for the numeric type.
>
>
