Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1C125B5DBFC for ; Thu, 1 Sep 2011 06:46:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 14220-08 for ; Thu, 1 Sep 2011 09:46:46 +0000 (UTC) X-Greylist: delayed 00:08:00.656701 by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 858D5B5DBF6 for ; Thu, 1 Sep 2011 06:46:45 -0300 (ADT) Received: by bke11 with SMTP id 11so1596372bke.19 for ; Thu, 01 Sep 2011 02:46:44 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.7.211 with SMTP id e19mr762142bke.371.1314869923575; Thu, 01 Sep 2011 02:38:43 -0700 (PDT) Received: by 10.204.97.203 with HTTP; Thu, 1 Sep 2011 02:38:43 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: <1314854108076-4757149.post@n5.nabble.com> References: <1314854108076-4757149.post@n5.nabble.com> Date: Thu, 1 Sep 2011 02:38:43 -0700 Message-ID: Subject: Re: Want some basic compare of data type on PostgreSQL and MySQL From: Samuel Gendler To: bhavesh1385 Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174c3748b877ca04abde0274 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/4 X-Sequence-Number: 35804 --0015174c3748b877ca04abde0274 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Aug 31, 2011 at 10:15 PM, bhavesh1385 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. --0015174c3748b877ca04abde0274 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Aug 31, 2011 at 10:15 PM, bhaves= h1385 <bh= avesh.bece.13@gmail.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<= br> 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` (
=A0`iadmin_id` int(11) NOT NULL AUTO_INCREMENT,
=A0`vemail` varchar(255) NOT NULL,
=A0`vname` varchar(255) NOT NULL,
=A0`irole_id` int(11) NOT NULL,
=A0`vpassword` varchar(255) NOT NULL,
=A0`estatus` enum('Active','InActive') NOT NULL,
=A0PRIMARY KEY (`iadmin_id`)
) ENGINE=3DInnoDB =A0DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D1 ;



So i want to execute the same query into PostgreSQL and i try the same quer= y
but error occur..

and i correct the syntax of the query according to PostgreSQL as following<= br> ------------------
CREATE TABLE tbl_admin1 (
=A0iadmin_id integer NOT NULL,
=A0vemail character varying,
=A0vname character varying,
=A0irole_id integer,
=A0vpassword character varying,
=A0PRIMARY 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 :-

=A0 =A0 =A0 =A0 =A0PostgreSQL =A0 =A0 =A0 =A0 =A0 =A0 =A0MySQL
---------------------------------------------
=A0 =A0 =A0characte varying =A0 =A0 =A0 =A0 VARCHAR
=A0 =A0 =A0 =A0 integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0INT

like that i want all the possible data type comparision.


First, the documentation for postgresql is= very comprehensive and available here:=A0http://www.postgresql.org/docs/9.0/int= eractive/index.html

More specifically, the documentation for data types is = here:

To create an auto incrementing column, use type 'se= rial' for 4 byte signed values and 'bigserial' for 8 byte value= s. =A0Postgresql will create a sequence and use that sequence to generate a= value on insert if no value is provided. =A0There is documentation for the= serial shorthand here:

varchar a= nd int are part of the sql standard and can be used as-is in postgresql. = =A0You can't specify the length of the string representation of an inte= ger via int(11) but you can specify constraints for max and min values, if = required. =A0Use bigint for 8 byte integers. =A0Also, be sure to read the d= escription of the varchar datatype so that you understand the differences i= n performance compared to mysql, especially in regard to using varchar inst= ead of text types.

There is documentation for enum types here:
<= a href=3D"http://www.postgresql.org/docs/9.0/interactive/datatype-enum.html= ">http://www.postgresql.org/docs/9.0/interactive/datatype-enum.html

Given the available values for your estatus enum, you m= ay 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 (
=A0iadmin_id serial NOT NUL= L PRIMARY KEY,
=A0vemail varchar(255) NOT NULL,
=A0vname varchar(255)= NOT NULL,
=A0irole_id bigint NOT NULL CHECK (irole_id >=3D -99999999= 999 AND irole_id <=3D 99999999999),
=A0vpassword varchar(255) NOT NULL,
=A0estatus status_enum NOT NULL
)= ;

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

CREATE TABLE tbl_admin (
=A0iadmin_id serial NO= T NULL PRIMARY KEY,
=A0vemail varchar(255) NOT NULL,
=A0vname varchar= (255) NOT NULL,
=A0irole_id integer references tbl_role(irole_id)= ,
=A0vpassword varchar(255) NOT NULL,
=A0estatus status_enum NOT NULL
)= ;

Documentation of foreign keys is at:
=

In future, please make an effort to discover the answer= s to such basic questions in the documentation for yourself. =A0No one want= s to spend a lot of time answering questions that have already been well do= cumented elsewhere.
--0015174c3748b877ca04abde0274--