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

On Wed, Aug 31, 2011 at 11: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,

Do you know what a precision definition to an INT does in mysql?
Cause it doesn't affect the precision. ***

> [1] How to make Primary Key as a Auto Increment...?

Use the SERIAL or BIGSERIAL pseudo type.

create table x (id bigserial primary key, ...)

> [2] Suppose I want to put 'ENUM' data type then how i can do that ..?

Then just put one. As long as you're on pgsql 8.3 or above.
Considering 8.2 is going out of maintenance soon, 8.3 is the minimum
version you should really be running anyway.

> [3] Please suggest me basic Data type Comparesion between PostgreSQL and
> MySQL .. like :-
>
>          PostgreSQL              MySQL
> ---------------------------------------------
>      characte varying         VARCHAR
>         integer                      INT

Both varchar and int are legal variable types in postgresql. However,
postgresql doesn't support the awkward precision that isn't precision
on ints that mysql does. The postgresql type text is a varchar with
no precision that can hold up to about a gig or so of text. Not that
i recommend putting a gig of text into a single field in a database.

> like that i want all the possible data type comparision.

If it's defined in the SQL standard, postgresql has it, mysql may or
may not and it may or may not behave according to the SQL standard..
The only other things to be aware of are:
MySQL allows unsigned int types, postgresql does not,
MySQL turns the first timestamp into an auto-update timestamp field,
PostgreSQL does not,
*** MySQL allows you to define a "precision" on an int that is in
fact, not a precision. It affects only the display in terms of how
many extra characters are padded onto the beginning of the number when
it's retrieved,
PostgreSQL does autoinc with the SERIAL and BIGSERIAL types.