Overview of SQL datataypes based on SQL 2003 (as it seems most extensive). "x" in column implies it exists also in other SQL versions, or is anticipated by ADQL. VOTable column gives likely mapping to VOTable data type. a "[(P[,S])]" after a datatypeps indicates that a precision (P) and possibly a scale(S) can be provided.

For info on database support this look at JVO presentation during Victoria 2006 interop. Also various JDBC mapping tables such as this.

In table stick to predefined data types. SQL 2003 and 99 also various user defined types such as ROW etc.

NB complete matching to other versions of SQL etc.

SQL 2003 SortSorted ascending SQL 92 Sort SQL 99 Sort ADQL 2.0 Sort JDBC type4 Sort VOTable 1.2 (beta) Sort
BIGINT x x x BIGINT long
BINARY LARGE OBJECT [(P)]1 x o x BLOB x
BLOB [(P)]1 x o x BLOB x
BOOLEAN x o x BOOLEAN boolean
CHAR [(P)] o o o CHAR char
CHAR LARGE OBJECT [(P)]1 x o x CLOB char (?)
CHAR VARYING [(P)] o o o VARCHAR char
CHARACTER [(P)] o o o CHAR char
CHARACTER LARGE OBJECT [(P)]1 x o x CLOB char (?)
CHARACTER VARYING[(P)] o o o VARCHAR char
CLOB [(P)]1 x o x CLOB char (?)
DATE o o o DATE x
DEC [(P[,S])] o o x DECIMAL x5
DECIMAL [(P[,S])] o o o DECIMAL x5
DOUBLE PRECISION o o o DOUBLE double
FLOAT [(P)] o o o FLOAT double/float
INT o o o INTEGER int
INTEGER o o o INTEGER int
INTERVAL ... 3 o o o x x
NATIONAL CHAR [(P)] o o o NCHAR char
NATIONAL CHAR VARYING [(P)] o o o NVARCHAR char
NATIONAL CHARACTER [(P)] o o o NCHAR char
NATIONAL CHARACTER LARGE OBJECT [(P)]1 x o x NCLOB char (?)
NATIONAL CHARACTER VARYING[(P)] o o o NVARCHAR char
NCHAR [(P)] o o o NCHAR char
NCHAR LARGE OBJECT [(P)]1 x o x NCLOB char (?)
NCHAR VARYING [(P)] o o o NVARCHAR char
NCLOB [(P)]1 x o x NCLOB char (?)
NUMERIC [(P[,S])] o o o NUMERIC x5
REAL o o o REAL float
SMALLINT o o o SMALLINT short
TIME [(P)]2 o o o TIME x
TIMESTAMP [(P)]2 o o o TIMESTAMP x
VARCHAR [(P)] o o o VARCHAR char
x BIT [(P)] o o BIT? bit?
x BIT VARYING [(P)] o o BIT? bit?

1 The syntax for sizes of large objects is actually somewhat different from the "[(P)]" applicable to simpler data types.
2 The syntax for sizes of TIME and TIMESTAMP is actually somewhat different from the "[(P)]" applicable to simpler data types.
3 See the SQL 2003 grammar for precise specification of INTERVAL type.
4 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.
5 Decimal might be mapped to floats or doubles in a result type, but have different semantics for mathematical operations etc.


Topic attachments
I Attachment History Action Size Date Who Comment
Texttxt adqltypes.txt r1 manage 13.2 K 2009-03-23 - 07:04 GerardLemson  
Edit | Attach | Watch | Print version | History: r6 < r5 < r4 < r3 < r2 | Backlinks | Raw View | Raw edit | More topic actions...
Topic revision: r4 - 2009-03-23 - GerardLemson
 
This site is powered by the TWiki collaboration platform Powered by Perl This site is powered by the TWiki collaboration platformCopyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback