GerardLemsonADQLDataTypes < IVOA

IVOA Web>GerardLemson>GerardLemsonTAP_031>GerardLemsonADQLDataTypes (2009-03-24, GerardLemson) (raw view)

Overview of SQL datataypes based on SQL 2003 (as it seems most extensive). "o" in column implies it exists also in other SQL versions, is anticipated by ADQL through its list reserved words. The columns JDBC and VOTable give the mapping to those two type systems.
A "[(P[,S])]" after a datatypeps indicates that a precision (P) and possibly a scale(S) can be provided.

For info on database support look at [[http://wiki.ivoa.net/internal/IVOA/InterOpMay2006VOQL/SQLcompliance.pdf][JVO presentation]] during Victoria 2006 interop. Also various JDBC mapping tables such as [[http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html][this]].

| *[[http://savage.net.au/SQL/sql-2003-2.bnf.html][SQL 2003]]* | *[[http://savage.net.au/SQL/sql-92.bnf.html][SQL 92]]* | *[[http://savage.net.au/SQL/sql-99.bnf.html][SQL 99]]* | *[[http://www.ivoa.net/Documents/REC/ADQL/ADQL-20081030.pdf][ADQL 2.0]]* | *[[http://java.sun.com/javase/6/docs/api/java/sql/Types.html][JDBC type]][[#FootNote4][<sup>4</sup>]]* | *[[http://wiki.ivoa.net/internal/IVOA/IvoaVOTable/VOTable-beta.xsd][VOTable 1.2 (beta)]]* |
|CHARACTER [(P)]|  o  |  o  |  o  |CHAR |char |
|CHAR [(P)] |  o  |  o  |  o  |CHAR |char  |
|CHARACTER VARYING[(P)] |  o  |  o  |  o  |VARCHAR |char  |
|CHAR VARYING [(P)] |  o  |  o  |  o  |VARCHAR  |char  |
|VARCHAR [(P)] |  o  |  o  |  o  |VARCHAR  |char  |
|CHARACTER LARGE OBJECT [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |CLOB   |char (?) |
|CHAR LARGE OBJECT [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |CLOB   |char (?) |
|CLOB [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |CLOB  |char (?) |
|NATIONAL CHARACTER [(P)]|  o  |  o  |  o  |NCHAR |char |
|NATIONAL CHAR [(P)] |  o  |  o  |  o  |NCHAR  |char  |
|NCHAR [(P)] |  o  |  o  |  o  |NCHAR  |char  |
|NATIONAL CHARACTER VARYING[(P)] |  o  |  o  |  o  |NVARCHAR  |char  |
|NATIONAL CHAR VARYING [(P)] |  o  |  o  |  o  |NVARCHAR   |char  |
|NCHAR VARYING [(P)] |  o  |  o  |  o  |NVARCHAR   |char  |
|NATIONAL CHARACTER LARGE OBJECT [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |NCLOB  |char (?) |
|NCHAR LARGE OBJECT [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |NCLOB  |char (?) |
|NCLOB [(P)][[#FootNote1][<sup>1</sup>]] |  x  |  o  |  x  |NCLOB  |char (?) |
|BINARY LARGE OBJECT [(P)][[#FootNote1][<sup>1</sup>]]  |  x  |  o  |  x  |BLOB |unsignedByte[[#FootNote6][<sup>6</sup>]]  |
|BLOB [(P)][[#FootNote1][<sup>1</sup>]]  |  x  |  o  |  x  | BLOB |unsignedByte[[#FootNote6][<sup>6</sup>]]  |
|NUMERIC [(P[,S])]|  o  |  o  |  o  |NUMERIC |  x[[#FootNote5][<sup>5</sup>]]  |
|DECIMAL [(P[,S])]|  o  |  o  |  o  |DECIMAL  |  x[[#FootNote5][<sup>5</sup>]]  |
|DEC [(P[,S])] |  o  |  o  |  x  |DECIMAL  |  x[[#FootNote5][<sup>5</sup>]]  |
|INTEGER |  o  |  o  |  o  |INTEGER  | int |
|INT |  o  |  o  |  o  |INTEGER   | int |
|SMALLINT |  o  |  o  |  o  |SMALLINT  | short |
|BIGINT |  x  |  x  |  x  | BIGINT | long |
|FLOAT [(P)] |  o  |  o  |  o  |FLOAT |double/float |
|REAL |  o  |  o  |  o  |REAL |float |
|DOUBLE PRECISION |  o  |  o  |  o  |DOUBLE |double |
|BOOLEAN |  x  |  o  |  x  |BOOLEAN | boolean|
|DATE |  o  |  o  |  o  |DATE  |  x  |
|TIME [(P)][[#FootNote2][<sup>2</sup>]]|  o  |  o  |  o  | TIME |  x  |
|TIMESTAMP [(P)][[#FootNote2][<sup>2</sup>]] |  o  |  o  |  o  |TIMESTAMP |  x  |
|INTERVAL ... [[#FootNote3][<sup>3</sup>]]|  o  |  o  |  o  |  x  |  x  |
|  x  |BIT [(P)] |  o  |  o  |BIT? | bit?|
|  x  |BIT VARYING [(P)]|  o  |  o  |BIT? |bit? |
|  x  |  x  |  x  |!STC|  x  |  x  |
|  x  |  x  |  x  |  x  |  x  |floatComplex |
|  x  |  x  |  x  |  x  |  x  |doubleComplex |
|  x  |  x  |  x  |  x  |  x  |unicodeChar|




#FootNote1 <sup>1</sup> The syntax for sizes of large objects is actually somewhat different from the "[(P)]" applicable to simpler data types.<br/>
#FootNote2 <sup>2</sup> The syntax for sizes of TIME and TIMESTAMP is actually somewhat different from the "[(P)]" applicable to simpler data types.<br/>
#FootNote3 <sup>3</sup> See the [[http://savage.net.au/SQL/sql-2003-2.bnf.html#interval%20type][SQL 2003 grammar]] for precise specification of INTERVAL type.<br/>
#FootNote4 <sup>4</sup> JDBC's java.sql.Types defines many other types that do not appear in any of the SQL standards, but correspond to database type systems.<br/>
#FootNote5 <sup>5</sup> Decimal might be mapped to floats or doubles in a result type, but have different semantics for mathematical operations etc.<br/>
#FootNote6 <sup>6</sup> Following [[http://www.ivoa.net/forum/dal/0903/1073.htm][Mark's suggestion]] on the mailing list.<br/>

<br/>
<!--
      * Set ALLOWTOPICRENAME = IVOA.TWikiAdminGroup
-->

Attachments

Topic attachments
I	Attachment	History	Action	Size	Date	Who	Comment
txt	adqltypes.txt	r1	manage	13.2 K	2009-03-23 - 07:04	GerardLemson

Topic revision: r6 - 2009-03-24 - GerardLemson

IVOA

Log in or Register

IVOA.net
Wiki Home
WebChanges
WebTopicList
WebStatistics

Twiki Meta & Help
IVOA
Know
Main
Sandbox
TWiki

TWiki intro
TWiki tutorial
User registration
Notify me

Working Groups

Interest Groups

Time Domain

Committees

Stds&Procs

www.ivoa.net
Documents
Events
Members
XML Schema