 |
|
EnterpriseDB SQL: Data Types
Oracle Tips by Burleson Consulting
|
Before
we start using SQL, it is important to understand just what it is
you're manipulating. As I said earlier, a column stores data. When
you define the column, you tell the database what kind of data you
plan to store in it.
There
are five types of data that you will primarily use: text, numeric,
date/time, binary and Boolean. These are called SCALAR types.
Being scalar means that the type has a single discrete value. Later
in this section, we will also discuss Arrays and Composite types
both of which use scalar types in their definitions.
Text
data are alphanumeric strings of data like name, address, phone
number, etc.
A
numeric column would be something you need to do math against or
that are intrinsically a number: money, counts, IDs.
A date
column is what it sounds like: date and time information.
Binary
data is a little bit harder to classify. Binary data can be
compressed data, encrypted data or file data like word processing or
spreadsheet files.
A
Boolean is a stand in for TRUE
or
FALSE. Oracle does not have a built-in BOOLEAN data type but
EnterpriseDB does.
I will
also cover miscellaneous data types, arrays and composite data types
below.
I will
cover each category of data type in greater detail. I believe in
examples. For each data type I discuss, I am going show an example
where I create a table, insert a record and select the record back
out.
Don't
worry about understanding the syntax just yet. There command’s
syntax will be described in plenty of detail later in this chapter.
Even if you aren't sure what's going on with the syntax at this
point, when you come back later for a reference, you will appreciate
the examples.
The
important point to get right now is what a data type is and what
kind of data you would store in each.
TEXT Data
VARCHAR2
In
Oracle, VARCHAR2 is the primary string data type. VARCHAR2 is a
variable length string. You must declare a maximum length before
using it. In Oracle 10g, a VARCHAR2 column may be up to 4000
characters (or bytes).
In
EnterpriseDB, I have created VARCHAR2 columns up to 32767
characters. To maintain compatibility I would recommend that you
limit VARCHAR2 in EnterpriseDB to 4000 characters.
In
EnterpriseDB, when you create a table with VARCHAR2, the VARCHAR2(n)
is converted to CHARACTER VARYING(n). That means that VARCHAR2 is a
synonym to CHARACTER VARYING. Your Oracle scripts will work in
EnterpriseDB if you use VARCHAR2 but if you extract scripts from
EnterpriseDB, they will extract as CHARACTER VARYING. If you want
to maintain compatibility with Oracle, it is a best practice to not
extract create table scripts from EnterpriseDB. Keep them in text
files, in CASE repositories or in Oracle.
To
declare a 50-character VARCHAR2 variable, you would simply enter the
variable name followed by VARCHAR2(50).
Synonyms for VARCHAR2 are:
* CHAR
VARYING
* CHARACTER VARYING
* VARCHAR
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
