|
|
| (80 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| == Introduction ==
| |
| === Previous Design/Implementation ===
| |
|
| |
|
| == Data model representation ==
| |
|
| |
| === Atomic (Simple) Types ===
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
| ==Table Test 1 ==
| |
|
| |
|
| |
| {| border="1" cellspacing="0"
| |
| |+ [http://msdn.microsoft.com/en-us/library/ms187752.aspx Transact-SQL (Microsoft)]
| |
| !Type !! Range !! Storage Bytes !! DAP equiv.
| |
| |-
| |
| |bigint || || 8 bytes || none
| |
| |-
| |
| |int || || 4 bytes ||
| |
| |-
| |
| |numeric || || ||
| |
| |-
| |
| |decimal || || ||
| |
| |-
| |
| | bit || || ||
| |
| |-
| |
| |smallint || || 2 bytes ||
| |
| |-
| |
| |tinyint || || 1 bytes||
| |
| |-
| |
| | smallmoney || || ||
| |
| |-
| |
| | money || || ||
| |
| |-
| |
| | float || || 4 or 8 bytes ||
| |
| |-
| |
| | real || || 4 bytes ||
| |
| |-
| |
| | date || || ||
| |
| |-
| |
| | datetimeoffset || || ||
| |
| |-
| |
| | datetime2 || || ||
| |
| |-
| |
| | smalldatetime || || ||
| |
| |-
| |
| | datetime || || ||
| |
| |-
| |
| | time || || ||
| |
| |-
| |
| | char || || ||
| |
| |-
| |
| | varchar || || ||
| |
| |-
| |
| | next || || ||
| |
| |-
| |
| | nchar || || ||
| |
| |-
| |
| | nvarchar || || ||
| |
| |-
| |
| | ntext || || ||
| |
| |-
| |
| | binary || || ||
| |
| |-
| |
| | varbinary || || ||
| |
| |-
| |
| | image || || ||
| |
| |-
| |
| | cursor || || ||
| |
| |-
| |
| | timestamp || || ||
| |
| |-
| |
| | hierarchyid || || ||
| |
| |-
| |
| | uniquieidentifier || || ||
| |
| |-
| |
| | sql_variant || || ||
| |
| |-
| |
| | xml || || ||
| |
| |-
| |
| | table || || ||
| |
| |}
| |
|
| |
| == PostgreSQL Data Types ==
| |
|
| |
| ==== Boolean and Binary Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | boolean, bool || A single true or false value. || SQL99 || Boolean
| |
| |-
| |
| | bit(n) || An n -length bit string (exactly n binary bits). || SQL92 || ''None''
| |
| |-
| |
| | bit varying(n), varbit(n) || A variable n -length bit string (up to n binary bits) || SQL92 || ''None''
| |
| |}
| |
|
| |
| ==== Character Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Storage !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | character (n ), char(n ) ||A fixed n -length character string.|| (4+n) bytes|| SQL89 || String
| |
| |-
| |
| | character varying(n), varchar(n) || A variable length character string of up to n characters. || Up to (4+n) bytes || SQL92 || String
| |
| |-
| |
| | text || A variable length character string, of unlimited length. || Variable || PostgreSQL-specific || String
| |
| |}
| |
|
| |
| ==== Numeric Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Storage !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | smallint, int2 || A signed 2-byte integer || 2 bytes || SQL89 || Int16
| |
| |-
| |
| | integer, int, int4 || A signed, fixed-precision 4-byte number. || 4 bytes || SQL92 || Int32
| |
| |-
| |
| | bigint, int8 || A signed 8-byte integer, up to 18 digits in length. || 8 bytes || PostgreSQL-specific || ''None (Need Int64)''
| |
| |-
| |
| | real, float4 || A 4-byte floating point number. || 4 bytes || SQL89 || Float32
| |
| |-
| |
| | double precision, float8, float || An 8-byte floating point number || 8 bytes || SQL89 || Float64
| |
| |-
| |
| | numeric(p,s), decimal(p,s) || An exact numeric type with arbitrary precision p, and scale s. || Variable || SQL99 || ''None''
| |
| |-
| |
| | money || A fixed precision, U.S.-style currency. || 4 bytes || PostgreSQL-specific, deprecated. || ''None''
| |
| |-
| |
| | serial || An auto-incrementing 4-byte integer. || 4 bytes || PostgreSQL-specific. || ''None''
| |
| |}
| |
|
| |
| ==== Date and Time Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Storage !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | date || A calendar date (day, month, year). || 4 bytes || SQL92 || ''None''
| |
| |-
| |
| | time || The time of day. || 4 bytes || SQL92 || ''None''
| |
| |-
| |
| | time with time zone || The time of day, including time zone information. || 4 bytes || SQL92 || ''None''
| |
| |-
| |
| | timestamp (includes time zone) || Both date and time. || 8 bytes || SQL92 || ''None''
| |
| |-
| |
| | interval || An arbitrary specified length of time || 12 bytes || SQL92 || ''None''
| |
| |}
| |
|
| |
| ==== Geometric Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | box || A rectangular box in a 2D plane. || PostgreSQL-specific || None
| |
| |-
| |
| | line || An infinite line in a 2D plane. || PostgreSQL-specific || ''None''
| |
| |-
| |
| | lineseg ||A finite line segment in a 2D plane. || PostgreSQL-specific || ''None''
| |
| |-
| |
| | circle || A circle with center and radius. || PostgreSQL-specific || ''None''
| |
| |-
| |
| | path || Open and closed geometric paths in a two-dimensional plane . || PostgreSQL-specific || ''None''
| |
| |-
| |
| | point || geometric point in a 2D plane || PostgreSQL-specific || ''None
| |
| |-
| |
| | polygon || A closed geometric path in a 2D plane || PostgreSQL-specific || ''None''
| |
| |}
| |
| ==== Network Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | cdir || An IP network specification || PostgreSQL-specific || ''None''
| |
| |-
| |
| | inet || A network IP address, with optional subnet bits. || PostgreSQL-specific || ''None''
| |
| |-
| |
| | macaddr || A MAC address (e.g., an Ethernet card's hardware address). || PostgreSQL-specific || ''None''
| |
| |}
| |
| ==== System Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| | oid || An object (row) identifier. || PostgreSQL-specific || ''None''
| |
| |-
| |
| | xid || A transaction identifier || PostgreSQL-specific || ''None''
| |
| |}
| |
|
| |
| == Template ==
| |
| ==== Types ====
| |
| {| border="1" cellspacing="0"
| |
| ! Data Type !! Description !! Standardization || Logical DAP data type association.
| |
| |-
| |
| |
| |
| |-
| |
| |
| |
| |-
| |
| |
| |
| |}
| |
|
| |
| == Desired Features ==
| |
|
| |
| == Implementation Target ==
| |
