SQL numeric types
Both the Postgresl and mysql systems support the SQL 92 numeric data types these are NUMERIC, DECIMAL, INTEGER, and SMALLINT as well as the inexact REAL, and DOUBLE PRECISION types.
Both databases have BIGINT type and their behaviours are identical. Mysql also supports the following additional types TINYINT and MEDIUMINT which are not supported on Postgresql
| Comparision of numeric data types | |||
| Type | Bytes | Range | Availability |
|---|---|---|---|
| TINYINT | 1 | -128 to +127 | my |
| SMALLINT | 2 | -32768 to +32767 | my, pg |
| MEDIUMINT | 3 | -8388608 to 8388607 | my |
| INT | 4 | -2147483648 to +2147483647 | my, pg |
| SERIAL | 4 | 1 to 2147483647 | pg |
| BIGINT | 8 | -9223372036854775808 to +9223372036854775807 | my, pg |
| BIGSERIAL | 8 | 1 to 9223372036854775807 | pg |
BIGINT makes my head spin.
Something that we associate with numeric data types are columns whose values increase automatically as you add new records to the table. Unfortunately the databases implement them differently. In mysql these columns are labeled as being AUTO_INCREMENT which is not a databtype but an attribute. In posgres the approach is to set the column type as SERIAL or BIGSERIAL
Notice that SERIAL and BIGSERIAL fields cannot include negative numbers or Zero, yet you do not get the full range of values that associate with unsigned numbers.
| Generating Unique Identifiers | |
| mySQL | pgSQL |
|---|---|
CREATE TABLE tablename (
idColumn INT NOT NULL AUTO_INCREMENT ); |
CREATE TABLE tablename (
idColumng SERIAL );
OR
CREATE SEQUENCE tablename_colname_seq; idColumn integer DEFAULT
nextval('tablename_idColumn_seq') NOT NULL
); |
The next problem is how to retrieve the generated value for an auto increment or serial field. Unfortunately in postgresql retrieving this information is not as easy as in mysql. this link (http://www.raditha.com/blog/archives/000488.html) explains how it's done.
