Search This Blog

Thursday 24 April 2014

Creating a Database in Oracle and in PostgreSQL

As a part of my college project, I needed to create a database in oracle. It went fine. A I had a PostgreSQL set up at home, I decided to run the scripts on the PostgreSQL setup. Some interesting observations...
On the Oracle server, the college admin created a user for me. Once you create a user in oracle, a schema is automatically created for the user. So all my tables, views etc were added to the said schema.( See the oracle link and also this explanation) So number of schemas = number of users.
 Unlike a MySQL or Postgres instance where we can create several databases, Oracle has a single database.
On this single Oracle database, my college admin create a user for me:
create user xyz identified by xyzpassword;
The next step would be to grant appropriate privileges to allow the user to connect:
grant connect to xyz;
It is only now that I can start doing stuff inside my schema.
In case of Postgres however things are different:
A PostgreSQL database cluster contains one or more named databases. Users and 
groups of users are shared across the entire cluster, but no other data is 
shared across databases. 
Any given client connection to the server can access only the data in a single 
database, the one specified in the connection request.
A database contains one or more named schemas, which in turn contain tables. 
Schemas also contain other kinds of named objects, including data types, 
functions, and operators. The same object name can be used in different 
schemas without conflict; for example, both schema1 and myschema can contain 
tables named mytable. Unlike databases, schemas are not rigidly separated: 
a user can access objects in any of the schemas in the database he is connected to, 
if he has privileges to do so.
As seen here there is no direct mapping of user and schema.
So while in Oracle I did not have to worry about any schema issues, with PostgreSQL my first statements were:
CREATE SCHEMA "Project"
  AUTHORIZATION postgres;
SET search_path = "Project";
Next were differences in the DDL: A table in Oracle:
CREATE TABLE DEPARTMENT( 
  ID varchar2(10), 
  Name varchar2(20) UNIQUE NOT NULL, 
  CONSTRAINT DEPARTMENT_PK PRIMARY KEY (ID) 
  );
would look like below in PostgreSQL:
CREATE TABLE DEPARTMENT( 
  ID character varying(10), 
  Name character varying(20) UNIQUE NOT NULL, 
  CONSTRAINT DEPARTMENT_PK PRIMARY KEY (ID) 
  );
As seen the varchar2 in Oracle is equivalent to character varying in Postgres.The names "character varying(n)" and "character(n)" are actually a part of the SQL specification.
Similarly there was another difference observed in terms of numeric datatypes:
CREATE TABLE STUDENT_DEGREE(
  StudentPID varchar2(9),
  "Year" NUMBER(4,0),
While Oracle has defined several types like INTEGER,SHORTINTEGER and LONGINTEGER. These are all represented by Oracle as a NUMBER datatype behind the scenes. So an integer would actually correspond to number(38,0). This is not the case with Postgres:
The types smallint, integer, and bigint store whole numbers, that is, numbers 
without fractional components, of various ranges. Attempts to store values 
outside of the allowed range will result in an error.
The type integer is the usual choice, as it offers the best balance between range,
 storage size, and performance. The smallint type is generally only used if disk 
space is at a premium. The bigint type should only be used if the integer range is 
not sufficient, because the latter is definitely faster.
There is no number datatype in Postgres. Instead they have the equivalent numeric datatype:
The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. 
It is especially recommended for storing monetary amounts and other quantities where exactness is required. 
However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point 
types described in the next section.
As this indicates the integer datatype and numeric datatypes are not same.
CREATE TABLE STUDENT_DEGREE(
  StudentPID character varying(9),
  "Year" smallint,
I also encountered the difference with dates and times. Oracle does not include a datatype that can store only date or one that can hold only time. This would be semantically wrong:
CREATE TABLE LAB_EVENT(
  ID varchar2(10),
  LabID varchar2(4) NOT NULL,
  EventDate date NOT NULL,
  EventTime date NOT NULL,
I wanted only date in EventDate column, no time component. I wanted the EventTime to hold just time. Unfortunately there is no wayto do this with Oracle.I found a good discussion on the same on the askTom website and accordingly changed my schema:
CREATE TABLE LAB_EVENT(
  ID varchar2(10),
  LabID varchar2(4) NOT NULL,
  EventDateTime date NOT NULL,
With Postgres I didn't have this problem:
CREATE TABLE LAB_EVENT(
  ID character varying(10),
  LabID character varying(4) NOT NULL,
  EventDate date NOT NULL,
  EventTime time NOT NULL,
There are obviously several more differences in the datatypes and their behavior among the two implementations. These are those that I observed in my project DDL setup. For a more exhaustive list check this link.

No comments:

Post a Comment