Обсуждение: Want some basic compare of data type on PostgreSQL and MySQL
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.
Thanks,
Bhavesh Patel
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Want-some-basic-compare-of-data-type-on-PostgreSQL-and-MySQL-tp4757149p4757149.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Wed, Aug 31, 2011 at 11:15 PM, bhavesh1385 <bhavesh.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 > 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.
On Wed, Aug 31, 2011 at 10:15 PM, bhavesh1385 <bhavesh.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
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:
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:
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:
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
);
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
);
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:
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.
On 2011-09-01, bhavesh1385 <bhavesh.bece.13@gmail.com> wrote: > Hello All, > > I Want some basic compare of data type on PostgreSQL and MySQL. > > [1] How to make Primary Key as a Auto Increment...? you can't, use the pseudo-type serial (or bigserial) instead which does something similar, but subtly different. > [2] Suppose I want to put 'ENUM' data type then how i can do that ..? I think you have to create an enum type first. > [3] Please suggest me basic Data type Comparesion between PostgreSQL and > MySQL .. like :- > > PostgreSQL MySQL > --------------------------------------------- > characte varying VARCHAR > integer INT postgres understands varchar and int as well as aliases fior the expected types > like that i want all the possible data type comparision. assuming you understand mysql types already: http://www.postgresql.org/docs/9.0/interactive/datatype.html -- ⚂⚃ 100% natural
On Thursday 1. September 2011 11.16.23 Scott Marlowe wrote: > 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. Printed out as plain text on paper with 4,000 characters per page, it would produce about 500 volumes of 500 pages each. That would take up 20 running metres of shelf space. In order to avoid global deforestation, please restrain your urge to print out those big text fields. regards, Leif
On Thu, Sep 1, 2011 at 7:32 AM, Leif Biberg Kristensen <leif@solumslekt.org> wrote: > On Thursday 1. September 2011 11.16.23 Scott Marlowe wrote: >> 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. > > Printed out as plain text on paper with 4,000 characters per page, it would > produce about 500 volumes of 500 pages each. That would take up 20 running > metres of shelf space. > > In order to avoid global deforestation, please restrain your urge to print out > those big text fields. I'm sure with an appropriately small font we could cut that right down, pardon the pun.