SQL numeric types

Navigation

Online convertor (http://www.raditha.com/mysql/mysql2pgsql.php)
Making up the numbers
Basic Administration
Strange characters
Links (http://www.raditha.com/mysql/links.php)
Date and Time

Migration (http://www.raditha.com/postgres/)

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;
CREATE TABLE tablename (

   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.