Re: Want some basic compare of data type on PostgreSQL and MySQL

On Wed, Aug 31, 2011 at 10:15 PM, bhavesh1385 <bhavesh(dot)bece(dot)13(at)gmail(dot)com>wrote:

> Hello All,
>
> I Want some basic compare of data type on PostgreSQL and MySQL.
>
> I am Try to using create the database on PostgreSQL with the same query on
> MySQL then it will create a problem...
>
> I have make changes according to the Datatype of PostgreSQL and below are
> the syntax for the query.
>
> Into MySQL the Syntax Look like :-
> ---------------------------------
> CREATE TABLE IF NOT EXISTS `tbl_admin` (
> `iadmin_id` int(11) NOT NULL AUTO_INCREMENT,
> `vemail` varchar(255) NOT NULL,
> `vname` varchar(255) NOT NULL,
> `irole_id` int(11) NOT NULL,
> `vpassword` varchar(255) NOT NULL,
> `estatus` enum('Active','InActive') NOT NULL,
> PRIMARY KEY (`iadmin_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
>
>
>
> So i want to execute the same query into PostgreSQL and i try the same
> query
> but error occur..
>
> and i correct the syntax of the query according to PostgreSQL as following
> ------------------
> CREATE TABLE tbl_admin1 (
> iadmin_id integer NOT NULL,
> vemail character varying,
> vname character varying,
> irole_id integer,
> vpassword character varying,
> PRIMARY KEY (iadmin_id)
> )
>
> ---------------
> NOTE :- Still I have some below confusion into following
>
> [1] How to make Primary Key as a Auto Increment...?
> [2] Suppose I want to put 'ENUM' data type then how i can do that ..?
> [3] Please suggest me basic Data type Comparesion between PostgreSQL and
> MySQL .. like :-
>
> PostgreSQL MySQL
> ---------------------------------------------
> characte varying VARCHAR
> integer INT
>
> like that i want all the possible data type comparision.
>

First, the documentation for postgresql is very comprehensive and available
here: http://www.postgresql.org/docs/9.0/interactive/index.html

More specifically, the documentation for data types is here:
http://www.postgresql.org/docs/9.0/interactive/datatype.html

To create an auto incrementing column, use type 'serial' for 4 byte signed
values and 'bigserial' for 8 byte values. Postgresql will create a sequence
and use that sequence to generate a value on insert if no value is provided.
There is documentation for the serial shorthand here:
http://www.postgresql.org/docs/9.0/interactive/datatype-numeric.html#DATATYPE-SERIAL

varchar and int are part of the sql standard and can be used as-is in
postgresql. You can't specify the length of the string representation of an
integer via int(11) but you can specify constraints for max and min values,
if required. Use bigint for 8 byte integers. Also, be sure to read the
description of the varchar datatype so that you understand the differences
in performance compared to mysql, especially in regard to using varchar
instead of text types.

There is documentation for enum types here:
http://www.postgresql.org/docs/9.0/interactive/datatype-enum.html

Given the available values for your estatus enum, you may want to just use
the built in boolean datatype and rename the column to 'active'

CREATE TYPE status_enum AS ENUM ('Active', 'InActive');

CREATE TABLE tbl_admin (
iadmin_id serial NOT NULL PRIMARY KEY,
vemail varchar(255) NOT NULL,
vname varchar(255) NOT NULL,
irole_id bigint NOT NULL CHECK (irole_id >= -99999999999 AND irole_id <=
99999999999),
vpassword varchar(255) NOT NULL,
estatus status_enum NOT NULL
);

However, I imagine that irole_id is actually a foreign key to another table,
in which case, you don't really want the check constraint in this table, but
you do want an explicit foreign key. Note that I changed the type to bigint
in order to accomodate your original int(11) datatype, since 99999999999 is
too large for a 4 byte value. To declare the irole_id column as a foreign
key to the tbl_role table, using an integer type, do the following:

CREATE TABLE tbl_admin (
iadmin_id serial NOT NULL PRIMARY KEY,
vemail varchar(255) NOT NULL,
vname varchar(255) NOT NULL,
irole_id integer *references tbl_role(irole_id)*,
vpassword varchar(255) NOT NULL,
estatus status_enum NOT NULL
);

Documentation of foreign keys is at:
http://www.postgresql.org/docs/8.3/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

In future, please make an effort to discover the answers to such basic
questions in the documentation for yourself. No one wants to spend a lot of
time answering questions that have already been well documented elsewhere.