DAP Relational Database Server II: Difference between revisions

From OPeNDAP Documentation
⧼opendap2-jumptonavigation⧽
Line 91: Line 91:
== PostgreSQL Data Types ==
== PostgreSQL Data Types ==


==== Boolean and Binary Types ====
{| border="1" cellspacing="0"
{| border="1" cellspacing="0"
|+  Boolean and Binary Types
! Data Type  !! Description !! Standardization || Logical DAP data type association.
! Data Type  !! Description !! Standardization || Logical DAP data type association.
|-
|-
Line 100: Line 100:
|-
|-
| bit varying(n), varbit(n) || A variable n -length bit string (up to 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 !! Standardization || Logical DAP data type association.
|-
| character (n ), char(n ) ||A fixed n -length character string.|| SQL89 || String
|-
| character varying(n), varchar(n) || A variable length character string of up to n characters. || SQL92 || String
|-
| text || A variable length character string, of unlimited length. || PostgreSQL-specific || String
|}
==== Numeric Types ====
{| border="1" cellspacing="0"
! Data Type  !! Description !! Standardization || Logical DAP data type association.
|-
| smallint, int2 || A signed 2-byte integer || SQL89 || Int16
|-
| integer, int, int4 || A signed, fixed-precision 4-byte number. || SQL92 || Int32
|-
| bigint, int8 || A signed 8-byte integer, up to 18 digits in length. || PostgreSQL-specific || ''None (Need Int64)''
|-
| real, float4 || A 4-byte floating point number. || SQL89 || Float32
|-
| double precision, float8, float || An 8-byte floating point number || SQL89 || Float64
|-
| numeric(p,s), decimal(p,s) || An exact numeric type with arbitrary precision p, and scale s. || SQL99 || ''None''
|-
| money || A fixed precision, U.S.-style currency. || PostgreSQL-specific, deprecated. || ''None''
|-
| serial || An auto-incrementing 4-byte integer. || PostgreSQL-specific. || ''None''
|}
==== Date and Time Types ====
{| border="1" cellspacing="0"
! Data Type  !! Description !! Standardization || Logical DAP data type association.
|-
| date || A calendar date (day, month, year). || SQL92 || ''None''
|-
| time || The time of day. || SQL92 || ''None''
|-
| time with time zone || The time of day, including time zone information. || SQL92 || ''None''
|-
| timestamp (includes time zone) || Both date and time. || SQL92 || ''None''
|-
| interval || An arbitrary specified length of time || 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.
|-
|
|-
|
|-
|
|}
|}


Revision as of 18:39, 21 April 2009

Introduction

Previous Design/Implementation

Data model representation

Atomic (Simple) Types

Table Test 1

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

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

Data Type Description Standardization Logical DAP data type association.
character (n ), char(n ) A fixed n -length character string. SQL89 String
character varying(n), varchar(n) A variable length character string of up to n characters. SQL92 String
text A variable length character string, of unlimited length. PostgreSQL-specific String

Numeric Types

Data Type Description Standardization Logical DAP data type association.
smallint, int2 A signed 2-byte integer SQL89 Int16
integer, int, int4 A signed, fixed-precision 4-byte number. SQL92 Int32
bigint, int8 A signed 8-byte integer, up to 18 digits in length. PostgreSQL-specific None (Need Int64)
real, float4 A 4-byte floating point number. SQL89 Float32
double precision, float8, float An 8-byte floating point number SQL89 Float64
numeric(p,s), decimal(p,s) An exact numeric type with arbitrary precision p, and scale s. SQL99 None
money A fixed precision, U.S.-style currency. PostgreSQL-specific, deprecated. None
serial An auto-incrementing 4-byte integer. PostgreSQL-specific. None

Date and Time Types

Data Type Description Standardization Logical DAP data type association.
date A calendar date (day, month, year). SQL92 None
time The time of day. SQL92 None
time with time zone The time of day, including time zone information. SQL92 None
timestamp (includes time zone) Both date and time. SQL92 None
interval An arbitrary specified length of time SQL92 None

Geometric Types

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

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

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

Data Type Description Standardization Logical DAP data type association.

Desired Features

Implementation Target

Retrieved from "https://docs.opendap.org/index.php?title=DAP_Relational_Database_Server_II&oldid=3730"