Datatype Summary

এর দ্বারা পোস্ট করা assad
Thursday, March 22, 2012


sl#
 datatype in ORACLE datatype in POSTGRESQL data type in MYSQL data type MSSQL
1
 varchar2(size [byte | char]) bigint tinyint[(m)] bigint
2
 nvarchar2(size) bigserial smallint[(m)] int
3
 number [ (p [, s]) ] bit mediumint[(m)] smallint
4
 float [(p)] bit varying(n) int[(m)] tinyint
5
 long boolean bigint[(m)] bit
6
 date box float(p) decimal(precision, scale)
7
 binary_float bytea float[(m,d)] numeric
8
 binary_double character varying(n) double[(m,d)] money
9
 timestamp [(fractional_seconds_precision)] character(n) decimal[(m,[d])]
stored as string		 smallmoney
10
 timestamp [(fractional_seconds)] with time zone cidr bit[(m)] float(n)
11
 timestamp [(fractional_seconds)] with local time zone circle char[(m)] real
12
 interval year [(year_precision)] to month date varchar(m) datetime
13
 interval day [(day_precision)] to second [(fractional_seconds)] double precision tinytext2 smalldatetime
14
 raw(size) inet text2 char
15
 long raw integer mediumtext2 varchar
16
 rowid interval(p) longtext2 text
17
 urowid [(size)] line binary[(m)] nchar
18
 char [(size [byte | char])] lseg varbinary(m) nvarchar
19
 nchar[(size)] macaddr tinyblob ntext
20
 clob money blob binary
21
 nclob numeric [ (p, s) ] mediumblob varbinary
22
 blob path longblob image
23
 bfile point enum2
("a1","a2",...)		 cursor
24

polygon set2
("a1","a2",...)		 sql_variant
25

real date table
26

smallint datetime timestamp
27

serial time uniqueidentifier
28

text timestamp

29

time [ (p) ] [ without time zone ] year

30

time [ (p) ] with time zone



31

timestamp [ (p) ] [ without time zone ]














Oracle Datatype Summary
Code
Datatype
Description
1
VARCHAR2(size [BYTE | CHAR])
Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
2
NVARCHAR2(size)
Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
3
NUMBER[(precision [, scale]])
Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
4
LONG
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.
5
DATE
Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
6
BINARY_FLOAT
32-bit floating point number. This datatype requires 5 bytes, including the length byte.
7
BINARY_DOUBLE
64-bit floating point number. This datatype requires 9 bytes, including the length byte.
8
TIMESTAMP [(fractional_seconds)]
Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.
9
TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
10
TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
  • Data is normalized to the database time zone when it is stored in the database.
  • When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
11
INTERVAL YEAR [(year_precision)] TO MONTH
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
12
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]
Stores a period of time in days, hours, minutes, and seconds, where
  • day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
  • fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
13
RAW(size)
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
14
LONG RAW
Raw binary data of variable length up to 2 gigabytes.
15
ROWID
Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
16
UROWID [(size)]
Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
17
CHAR [(size [BYTE | CHAR])]
Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.
18
NCHAR[(size)]
Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
19
CLOB
A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
20
NCLOB
A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
21
BLOB
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
22
BFILE
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.











Postgresql Data Types
Name Aliases Description
bigint int8 signed eight-byte integer
bigserial serial8 autoincrementing eight-byte integer
bit
fixed-length bit string
bit varying(n) varbit(n) variable-length bit string
boolean bool logical Boolean (true/false)
box
rectangular box in the plane
bytea
binary data
character varying(n) varchar(n) variable-length character string
character(n) char(n) fixed-length character string
cidr
IPv4 or IPv6 network address
circle
circle in the plane
date
calendar date (year, month, day)
double precision float8 double precision floating-point number
inet
IPv4 or IPv6 host address
integer int, int4 signed four-byte integer
interval(p)
time span
line
infinite line in the plane (not fully implemented)
lseg
line segment in the plane
macaddr
MAC address
money
currency amount
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric with selectable precision
path
open and closed geometric path in the plane
point
geometric point in the plane
polygon
closed geometric path in the plane
real float4 single precision floating-point number
smallint int2 signed two-byte integer
serial serial4 autoincrementing four-byte integer
text
variable-length character string
time [ (p) ] [ without time zone ]
time of day
time [ (p) ] with time zone timetz time of day, including time zone
timestamp [ (p) ] [ without time zone ] timestamp date and time
timestamp [ (p) ] with time zone timestamptz date and time, including time zone


MySQL datatype
Type {storage} Name Range Attributes Default
Numeric
{1 byte}		 TINYINT[(M)] -128 TO 127
[0 to 255 if UNSIGNED]		 AUTO_INCREMENT
UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE		 NULL
[0 if NOT NULL]
Numeric
{2 bytes}		 SMALLINT[(M)] -32,768 to 32,767
[0 to 65,535]		 AUTO_INCREMENT,
UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE		 NULL
[0 if NOT NULL]
Numeric
{3 bytes}		 MEDIUMINT[(M)] -8,388,608 to 8,388,607
[0 to 16,777,215]		 AUTO_INCREMENT,
UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE		 NULL
[0 if NOT NULL]
Numeric
{4 bytes}		 INT[(M)] -/+2.147E+9
[0 to 4.294E+9]		 AUTO_INCREMENT,
UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE		 NULL
[0 if NOT NULL]
Numeric
{8 bytes}		 BIGINT[(M)] -/+9.223E+18
[0 to 18.45E+18]		 AUTO_INCREMENT,
UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE		 NULL
[0 if NOT NULL]
Numeric
{4 or 8}		 FLOAT(p) p=0-24  --> "FLOAT"
p=25-53  --> "DOUBLE"		 UNSIGNED, ZEROFILL NULL
[0 if NOT NULL]
Numeric
{4 bytes}		 FLOAT[(M,D)] Min=+/-1.175E-38
Max=+/-3.403E+38		 UNSIGNED, ZEROFILL NULL
[0 if NOT NULL]
Numeric
{8 bytes}		 DOUBLE[(M,D)] Min=+/-2.225E-308
Max=+/-1.798E+308		 UNSIGNED, ZEROFILL NULL
[0 if NOT NULL]
Numeric
{M+2}		 DECIMAL[(M,[D])]
Stored as string		 Max Range = DOUBLE range
Fixed point vs. DOUBLE float		 UNSIGNED, ZEROFILL NULL
[0 if NOT NULL]
Bit
{8 bytes}		 BIT[(M)] Binary. Display by [add zero or converting with BIN()]. M=1-64 Prior to 5.03
TINYINT(1) Synonym		 NULL
[0 if NOT NULL]
String
{M char's}		 CHAR[(M)] M=0-255 Characters, FIXED.
Right padded with spaces.		 BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{M char's1}		 VARCHAR(M) M=0-65,535 Characters
M=0-255
BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{#char's1}		 TINYTEXT2 0-255 Characters BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{#char's1}		 TEXT2 0-65,535 Char's BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{#char's1}		 MEDIUMTEXT2 0-16,777,215 Char's BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{#char's1}		 LONGTEXT2 0-4,294,967,295 Char's BINARY, CHARACTER SET NULL
["" if NOT NULL]
String
{M bytes}		 BINARY[(M)] M=0-255 bytes, FIXED. Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{M bytes}		 VARBINARY(M) 0-65,535 bytes
M=0-255
Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{#bytes1}		 TINYBLOB 0-255 bytes Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{#bytes1}		 BLOB 0-65,535 bytes Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{#bytes1}		 MEDIUMBLOB 0-16,777,215 bytes Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{#bytes1}		 LONGBLOB 0-4,294,967,295 bytes Global Only
(case sensitive)		 NULL
["" if NOT NULL]
String
{1-2 bytes}		 ENUM2
("A1","A2",...)		 Column is exactly 1 of 1-65,535 values CHARACTER SET NULL [1st value if NOT NULL]
String
{1-8 bytes}		 SET2
("A1","A2",...)		 Column is 0 or more values in list of 1-64 members CHARACTER SET NULL
["" if NOT NULL]
Date & Time
{3 bytes}		 DATE "1000-01-01" - "9999-12-31" Global Only
(YYYY-MM-DD)		 NULL
["0000-00-00" if NOT NULL]
Date & Time
{8 bytes}		 DATETIME "1000-01-01 00:00:00" -
"9999-12-31 23:59:59"		 Global Only
(YYYY-MM-DD hh:mm:ss)		 NULL ["0000-00-00 00:00:00"
if NOT NULL]
Date & Time
{3 bytes}		 TIME "-838:59:59" - "838:59:59" Global Only
(hh:mm:ss)		 NULL
["00:00:00" if NOT NULL]
Date & Time
{4 bytes}		 TIMESTAMP 19700101000000 -
2037+		 Global Only
(YYYYMMDDhhmmss)		 Current Date & Time
Date & Time
{1 bytes}		 YEAR 1900 - 2155 Global Only
(YYYY)		 NULL
["0000" if NOT NULL]



Data type in MsSQL Server

Data type Length Description
bigint 8 Integer number from -2^63 (-9 223 372 036 854 775 808) to 2^63-1 (9 223 372 036 854 775 807).
int 4 Integer number from -2^31 (-2 147 483 648) to 2^31-1 (2 147 483 647).
smallint 2 Integer number from -2^15 (-32 768) to 2^15-1 (32 767).
tinyint 1 Integer number from 0 to 255.
bit 1 bit Integer number 0 or 1.
decimal(precision, scale) 5-17 Numeric data type with fixed precision and scale (precision 1-38, 18 by default and scale 0-p, 0 by default).
numeric 5-17 Same as data type 'decimal'.
money 8 Financial data type from -2^63 (-922 337 203 685 477.5808) to 2^63-1 (922 337 203 685 477.5807) with the precision of one ten-thousandth unit.
smallmoney 4 Financial data type from -2^31 (-214 748.3648) to 2^31-1 (214 748.3647) with the precision of one ten-thousandth unit.
float(n) 4-8 Numeric data type with float precision, where n is the number of mantis bits (1-24, precision of 7 digits, size of 4 bytes and 25-53, precision of 15 digits and size of 8 bytes).
real 4 Numeric data type with float precision that is defined as a float(24).
datetime 8 Date type representing date and time from 1.1.1753 to 31.12.9999 with precision about 3ms. Values are rounded to .000, .003 and .007.
smalldatetime 4 Date type representing date and time from 1.1.1900 to 6.6.2079 with precision of 1min. Values up to 29.998 are rounded down and values from 29.999 are rounded down to the nearest minute.
char n Char string of fixed length and max. length of 8000 chars.
varchar n Char string of variable length and max. length of 8000 chars.
text n Char string of variable length and max. length of 2^31-1 (2 147 483 647) chars.
nchar 2*n Unicode char string of fixed length and max. length of 4000 chars.
nvarchar 2*n Unicode char string of variable length and max. length of 4000 chars.
ntext 2*n Unicode char string of variable length and max. length of 2^30-1 (1 073 741 823) chars.
binary n+4 Binary data of fixed length and max. length of 8000 bytes.
varbinary n+4 Binary data of variable length and max. length of 8000 bytes.
image n Binary data of variable length and max. length of 2^31-1 (2 147 483 647) bytes.
cursor
For storing the reference to cursors in a variable or in a procedure (no for CREATE TABLE).
sql_variant
For storing value of another type (no text, ntext, image, timestamp, sql_variant) of max. length to 8016 bytes. ODBC doesn't fully support this data type.
table
For storing the query result for the later usage.
timestamp 8+4 Data type generates automatically binary numbers, unique in the database, used mostly to the rows identification. There can be only column of this data type in the table.
uniqueidentifier
Data type for storing GUID (new by means of the NEWID function or existing from the string in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, e.g. 6F9619FF-8B86-D011-B42D-00C04FC964FF).




লেবেলসমূহ: , , ,

Oracle Datatype Summary

এর দ্বারা পোস্ট করা assad
Wednesday, March 21, 2012


Code Datatype Description
1
 VARCHAR2(size [BYTE | CHAR])
 Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.
2
 NVARCHAR2(size)
 Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
3
 NUMBER [ (p [, s]) ]
 Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.
4
 FLOAT [(p)]
 A subtype of the NUMBER datatype having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.
5
 LONG
 Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.
6
 DATE
 Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
7
 BINARY_FLOAT
 32-bit floating point number. This datatype requires 5 bytes, including the length byte.
8
 BINARY_DOUBLE
 64-bit floating point number. This datatype requires 9 bytes, including the length byte.
9
 TIMESTAMP [(fractional_seconds_precision)]
 Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.
10
 TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
 All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
11
 TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE
 All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
  • Data is normalized to the database time zone when it is stored in the database.
  • When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
12
 INTERVAL YEAR [(year_precision)] TO MONTH
 Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
13
 INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]
 Stores a period of time in days, hours, minutes, and seconds, where
  • day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
  • fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
14
 RAW(size)
 Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
15
 LONG RAW
 Raw binary data of variable length up to 2 gigabytes.
16
 ROWID
 Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
17
 UROWID [(size)]
 Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
18
 CHAR [(size [BYTE | CHAR])]
 Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.
19
 NCHAR[(size)]
 Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
20
 CLOB
 A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
21
 NCLOB
 A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
22
 BLOB
 A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
23
 BFILE
 Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

Subscribe to: Posts (Atom)