PostgreSQL & PHP Tutorials - Introduction to Datatypes - Data Type Differences
PostgreSQL »
Introduction to Database Datatypes
PostgreSQL » Starting Out » Introduction to Database Datatypes
PostgreSQL » Starting Out » Introduction to Database Datatypes
Posted By Chris Smith
Posted on 07 Feb 2006, 09:31 PM
Each database system will have slightly different options available.
MySQL lets you specify whether a number (integer or float) column will be 'signed' or 'unsigned' - a signed column will let you store negative and positive numbers, an unsigned column will only let you store positive numbers. PostgreSQL doesn't let you make this distinction.
Some of the data types may have limitations built into them. A 'small integer' (smallint) column will only store values between -32,768 and +32,767 (the 'integer' data type goes from -2,147,483,648 to 2,147,483,647), so be aware of any data type limitations before you start.
MySQL has a special field type called 'enum' (or enumeration) which only allows certain data to be entered into a field, for example 0/false/no, or 1/true/yes (we could have used this for the 'order delivered' field). Most database systems will require you to set up a 'constraint' to emulate this particular behaviour, but that's getting a bit beyond this article's scope.
PostgreSQL has a built in data type for isbn's, IP addresses, all sorts of geometric data and much more. It also allows you to create your own data types if necessary.
If you need to work with multiple databases, check each databases documentation before you start to make sure each one can handle the different data types. Some types such as 'enum' might appear, or you may need to work around differences such as 'datetime' showing differently depending on which system you are using.
The default types (varchar, int, smallint, bigint, text, char) will not significantly change between databases.
A lot of thought and research needs to go into designing a database, and we still haven't created a database or any tables yet! Choosing the wrong datatype can make your database slow (and speed problems can be hard to track down!), so it's best to do a little research, write down some ideas and go from there.
Written for Interspire
MySQL lets you specify whether a number (integer or float) column will be 'signed' or 'unsigned' - a signed column will let you store negative and positive numbers, an unsigned column will only let you store positive numbers. PostgreSQL doesn't let you make this distinction.
Some of the data types may have limitations built into them. A 'small integer' (smallint) column will only store values between -32,768 and +32,767 (the 'integer' data type goes from -2,147,483,648 to 2,147,483,647), so be aware of any data type limitations before you start.
MySQL has a special field type called 'enum' (or enumeration) which only allows certain data to be entered into a field, for example 0/false/no, or 1/true/yes (we could have used this for the 'order delivered' field). Most database systems will require you to set up a 'constraint' to emulate this particular behaviour, but that's getting a bit beyond this article's scope.
PostgreSQL has a built in data type for isbn's, IP addresses, all sorts of geometric data and much more. It also allows you to create your own data types if necessary.
If you need to work with multiple databases, check each databases documentation before you start to make sure each one can handle the different data types. Some types such as 'enum' might appear, or you may need to work around differences such as 'datetime' showing differently depending on which system you are using.
The default types (varchar, int, smallint, bigint, text, char) will not significantly change between databases.
A lot of thought and research needs to go into designing a database, and we still haven't created a database or any tables yet! Choosing the wrong datatype can make your database slow (and speed problems can be hard to track down!), so it's best to do a little research, write down some ideas and go from there.
Written for Interspire
Avg Rating: 5
Vote Count: 1
Vote Count: 1