Moving from Oracle to PosgreSQL

Post #137,788 by tuberculosis 1/26/04 10:31:25 AM 8/21/07 12:40:40 PM Reply	Moving from Oracle to PosgreSQL I'm moving an Oracle db to Postgres, primarily because the database isn't going to get large enough to warrant the high price of Oracle and our little startup is strapped for cash. I'm hoping I can just change the type names mostly. The mapping I've come up with so far is: VARCHAR2 to varchar NUMBER(9) (used for primary keys it seems) to int8 LONG RAW (used to store binary files in the db) to bytea DATE to timestamp BLOB to bytea NUMBER (no precision) to numeric NUMBER (1-8) to int4 See any problems? Access is via JDBC and they use tablespaces as namespaces - ie there is a copy of the schema in variously named tablespaces and access is always via TableSpace.TableName. What is the equivalent construct in Postgres? Is it schema? Thanks for any help. "I believe that many of the systems we build today in Java would be better built in Smalltalk and Gemstone." -- Martin Fowler, JAOO 2003 Edited by tuberculosis Aug. 21, 2007, 12:40:40 PM EDT Expand All History
Post #137,792 by Yendor 1/26/04 10:41:33 AM Reply	WRT timestamps... ...Decide before you do the migration whether you want "timestamp with time zone" or "timestamp without time zone". I've never had to do that kind of migration before, so I've no comments on your datatype mappings, sorry. -YendorMike [link\|http://www.hope-ride.org/\|http://www.hope-ride.org/]
Post #137,801 by admin 1/26/04 11:08:30 AM Reply	Look at the "text" datatype instead of varchar. There's no space penalty or performance penalty, and they can be arbitrarily large. I'd echo Mike's concerns about with/without time zone as well on the timestamps. You can always put "with timezone" on the selects, though, if you need to. Re: Schemas: The hierarchy is server->database->schema->table. A connection for a particular database can only access that database. So if you want to segregate tables within a database, use a schema. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."

Welcome to IWETHEY!