Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

644 CHAPTER 28 FROM DATABASES TO DATATYPES related tables can be left alone. We can also create foreign keys between tables based on a group of columns between the two tables. We will re-create our staff table again to show the syntax: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, email TEXT NOT NULL, lname TEXT, nickname TEXT, FOREIGN KEY (lname,nickname) REFERENCES friends(lname,nickname) ); This syntax sets up the relationship just like our previous example; any entry in staff must now have a corresponding entry, based on both the lname and fname columns, in the friends table. UNIQUE A column assigned the UNIQUE attribute ensures that all values possess distinct values, except that NULL values are repeatable. You typically designate a column as UNIQUE to ensure that all fields within that column are distinct for example, to prevent the same e-mail address from being inserted into a newsletter subscriber table multiple times, while at the same time acknowledging that the field could potentially be empty (NULL). An example of a column designated as UNIQUE follows: email TEXT UNIQUE Composite Datatypes A composite datatype defines the structure of a row or record. In simple terms, it is a list of field names and their datatypes. Once a composite type is created, it can be used much like any other datatype, such as when defining a column in a table or declaring a return type for a function. This can prove very useful when you want to tightly couple related information together into a single logical piece. Creating Composite Types You can use the CREATETYPEcommand to create composite types. As shown next, the syntax is similar to that of the CREATETABLE command, though only field names and datatypes can be specified. No constraints or default values can be included. CREATE TYPE im_accounts AS ( jabber text, aim text, irc text ); Let s run through a quick example so that you can see exactly how this works. First, we create a table in which to use our new composite type:
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES Creating an automatically incrementing primary key: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, email TEXT NOT NULL ); Creating a single-field primary key: CREATE TABLE citizen ( ssid VARCHAR(9) NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, zipcode VARCHAR(10) NOT NULL ); Creating a multiple-field primary key: CREATE TABLE friend ( fname TEXT NOT NULL, lname TEXT NOT NULL, nickname TEXT NOT NULL, PRIMARY KEY(lname, nickname) ); REFERENCES The REFERENCES attribute specifies that the values in a column (or group of columns) must match the values appearing in some row of another table. This is done to ensure referential integrity between the two tables. As an example, we could rewrite the staff table in our previous example to the following: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, ssid VARCHAR(9) REFERENCES citizen (ssid), email TEXT NOT NULL ); Created this way, it is now impossible to add an entry to the staff table that does not have a corresponding entry in the citizen table. While some would say this approach to staffing might be short-sighted in today s global economy, opponents of illegal immigration would surely applaud this design. This relationship between the two tables is often referred to as a foreign key (no pun intended), and it provides other benefits as well. You ll notice that we eliminated the fnameand lname columns from our table; we did this because we can now infer this information from the relationship between the two tables. This also means that, should someone s name change (for example, when someone gets married), we do not have to write extra application code to propagate the changes throughout our database: the change can be made in one place and all
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

642 CHAPTER 28 FROM DATABASES TO DATATYPES You can view the results of these entries easily enough: rob=# SELECT * FROM default_now_example; attempt | insert_time ———+——————————a | 2005-10-16 15:41:39.382608-05 b | 1492-01-13 21:12:00-05 | 2005-10-16 15:42:17.860467-05 rows) As you can see, in our first INSERT statement, the default time was entered because we passed in the DEFAULT keyword. In the second, the time we specified was entered. In the third, an autogenerated time was inserted because we did not specify a value; this is the same behavior as using the DEFAULT keyword. NOT NULL Defining a column as NOT NULL disallows any attempt to insert a NULL value into the column. Using the NOT NULL attribute, where relevant, is always suggested, because it results in at least baseline verification that all necessary values have been passed to the query. An example of a NOT NULL column assignment follows: zipcode VARCHAR(10) NOT NULL NULL Simply stated, the NULL attribute means that NULL values are acceptable for the given field. This is also the default value for the field if no data is given and there is no DEFAULT attribute specified. This is the default characteristic for columns in PostgreSQL, so you will not often see it stated explicitly. PRIMARY KEY The PRIMARY KEY attribute is used to guarantee uniqueness for a given row. No values residing in a column designated as PRIMARY KEY are repeatable or nullable within that column. It s quite common to see SERIAL columns designated as a primary key, because this column doesn t necessarily have to bear any relation to the row data, other than acting as its unique identifier. However, there are two other ways for ensuring a record s uniqueness: Single-field primary keys: Typically used when a pre-existing, nonmodifiable unique identifier exists for each row entered into the database, such as a part number or social security number. Note that this key should never change once it is set. Multiple-field primary keys: Can be useful when it is not possible to guarantee uniqueness from any single field within a record. Thus, multiple fields are conjoined to ensure uniqueness. If the number of columns required to ensure uniqueness grows cumbersome, it is common practice to simply designate a SERIALinteger as the primary key, to alleviate the need to somehow generate unique identifiers with every insertion. The following three examples demonstrate creation of the auto-increment, single-field, and multiple-field primary key fields, respectively.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

CHAPTER 28 FROM DATABASES TO DATATYPES You can also reference other columns in a check constraint: vacation_days_taken INTEGER CHECK (vacation_days_taken < vacation_days_earned) PostgreSQL also allows you to define constraints at the table level. One advantage of using a table constraint is that you can give each constraint a unique name, as follows, which vastly simplifies deducing error messages that are given when a constraint is violated: CREATE TABLE employee ( employee_id SERIAL UNIQUE NOT NULL, vacation_days_earned INTEGER CHECK (vacation_days_earned > 0), vacation_days_taken INTEGER CHECK (vacation_days_taken > 0), CONSTRAINT no_vacation_days_left CHECK (vacation_days_taken <= vacation_days_earned) ); The advantage of this becomes clear with an example error message; here we will try to insert an employee who has earned 5 days of vacation but has taken 10 days: company=# INSERT INTO employee VALUES (DEFAULT,5,10); ERROR: new row for relation "employee" violates check constraint "no_vacation_days_left" DEFAULT The DEFAULT attribute ensures that some designated value will be assigned when no other value is available. The value can be a literal value or a simple expression, but cannot include a subquery or reference other columns within its definition, and the value must result in a valid type for the given column. One common example of a DEFAULT is the value now() for TIMESTAMP columns: initial_registration TIMESTAMPTZ DEFAULT now() Using now()causes the system to insert the current system time into the field upon insert. If DEFAULT is not specified on a column, a default value of NULLwill be inserted into the column. In the following example, we create a small test table to hold just an example identifier and a column to hold our timestamp value. We then insert three different entries; the first passes in the DEFAULT keyword, the second specifies a specific time, and the third leaves out the TIMESTAMP column altogether. rob=# CREATE TABLE default_now_example ( rob-# attempt text, rob-# insert_time timestamptz DEFAULT now() rob -# ); CREATE TABLE rob=# INSERT INTO default_now_example VALUES ('a', DEFAULT); INSERT 0 1 rob=# INSERT INTO default_now_example (attempt, insert_time) rob-# VALUES ('b','1492-01-13 21:12'); INSERT 0 1 rob=# INSERT INTO default_now_example (attempt) VALUES ('c'); INSERT 0 1
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

640 CHAPTER 28 FROM DATABASES TO DATATYPES VARCHAR[(length)] The VARCHAR datatype offers PostgreSQL variable-length string representation. If a string longer than length is inserted, it will produce an error, unless the characters are all spaces, in which case the string will be truncated to length. (Again, this exception is required by the SQL standard.) If an inserted string does not occupy length spaces, the remaining space will not be padded; the shorter string will simply be stored as is. VARCHAR without length will accept a string of any size (this is a PostgreSQL extension). VARCHAR is equivalent to the SQL standard CHARACTER VARYING(n), and both names can be used interchangeably. TEXT The TEXT datatype also offers a variable-length string representation. The TEXT type accepts strings of any length. Although type TEXTis not in the SQL standard, it is common among database management systems, and is the recommended datatype for strings that do not require an absolute length requirement. Tip Unlike most database systems, PostgreSQL does not have performance differences between CHAR(n), VARCHAR(n), and TEXT. Likewise, the storage requirements between these types are the same (save for the space needed for padding for CHAR types). For this reason, in most cases it is simpler to use TEXT than to make up an arbitrary limit for one of the other types. Boolean Datatype The BOOLEAN datatype is PostgreSQL s logical Boolean representation, and it complies with the SQL standard notion of Boolean. PostgreSQL s Boolean can be one of three states: TRUE, FALSE, or NULL (where NULL implies the notion of being unknown). BOOLEAN accepts a number of different representations for TRUE and FALSE including TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1′, and FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘0′, respectively. The keywords TRUE and FALSE are SQL-compliant, and thus are generally preferred. While PostgreSQL s C library, and applications that build on top of that library, tends to display Booleans as ‘t’ and ‘f’, they are not equivalent to string data and are not stored as such; BOOLEANdatatypes require only 1 byte of storage. Datatype Attributes Although this list is not exhaustive, this section covers those attributes you ll most commonly use, as well as those that will be used throughout the remainder of this book. CHECK The CHECK attribute provides a means for restricting the values in a column and, as such, is commonly referred to as a check constraint. The constraint must equate to a Boolean expression, and the value in question must resolve the expression to either TRUEor NULL. A common example of a check constraint is creating a column that should not accept negative values. You could define such a column as follows: vacation_days_earned INTEGER CHECK (vacation_days > 0)
You want to have a cheap webhost for your apache application, then check apache web hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES SERIAL The SERIAL type is not a true type, but is actually a notational convenience for setting up autoincrementing identifier columns. In PostgreSQL 8.0, specifying CREATE TABLE tblname ( colname SERIAL ); is the equivalent of: CREATE TABLE tblname ( colname INTEGER DEFAULT nextval(’tblname_colname_seq’) NOT NULL ); Both cases create an INTEGER column and arrange for its value to be assigned from a sequence generator, with the difference being that the SERIAL syntax also attempts to create the sequence automatically upon table creation, rather than having to create the sequence manually. Conversely, sequences created via the SERIAL syntax will also be dropped automatically if the column or table is dropped. Normally, when creating a SERIAL column, you also want to specify a UNIQUEor PRIMARYKEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic. Primary keys are explained a bit later, in the PRIMARY KEY section. Since the SERIAL type is implemented using the INTEGER type, it can only hold up to 2,147,483,647 values. While this is usually enough for most applications, if you expect that you will need more identifiers, you can use the type BIGSERIAL. BIGSERIAL behaves in all the same respects as SERIAL, except that it uses the BIGINT type for its underpinnings, and thus can support a range up to 9,223,372,036,854,775,807 values. String Datatypes PostgreSQL s string types are greatly simplified compared to many other database systems, but they are still the basis for storing string data. This section introduces the string types. CHAR[(length)] The CHAR datatype offers PostgreSQL s fixed-length string representation. If a string longer than length is inserted, it will produce an error, unless the characters are all spaces, in which case the string will be truncated to length. (This exception, while odd to some, is required by the SQL standard.) If an inserted string does not occupy length spaces, the remaining space will be padded by blank spaces. A CHAR declaration without a length is equivalent to CHAR(1). CHAR is equivalent to the SQL standard CHARACTER(n), and both names can be used interchangeably. Note There is also a datatype “char” (note the quotes and lowercase) that is different from CHAR or CHAR(1) in that it uses only 1 byte for storage. It is used internally within the system tables and is not intended for general use. It is mentioned here because some applications and developers may accidentally quote the CHAR attribute, which can lead to unexpected behavior.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

638 CHAPTER 28 FROM DATABASES TO DATATYPES NUMERIC (P,(S)) The NUMERIC datatype can store numbers with up to 1,000 digits of precision, and will perform calculations exactly. It is normally recommended that you store monetary values in this datatype, though you should be aware that arithmetic on NUMERIC values may be slow relative to the other numeric types. The NUMERIC type can be declared with an optional precision and scale. The precision is the total number of digits on both sides of the decimal, whereas the scale is the total number of digits to the right of the decimal. For example, the value 123.45 would be represented as numeric(5,2). If you attempt to insert a number that is too large, PostgreSQL rounds the number to a legal value for INSERT; for example, inserting 123.456 into the above representation would be stored as 123.46. You should also be aware that you can declare NUMERIC without a scale, which implies 0 for the scale, or without either a scale or precision, which implies no limits, although this latter practice is not recommended, for performance reasons. The storage requirement for NUMERIC varies depending on the number being stored. The basic formula is 4 bytes for a variable-length header, 4 bytes for a numeric header, and 2 bytes for every 4 decimal digits, but even 0 requires 8 bytes. Note The DECIMAL(P,(S)) type is equivalent to the NUMERIC type. REAL The REAL datatype is an inexact, variable-precision, floating-point number. On most platforms it has a range of at least 1E-37 to 1E+37 and supports a precision of at least six decimal places. Using the NUMERIC datatype rather than REAL is usually recommended, because REAL stores numbers in an inexact, though standards-compliant, way. The storage requirement is 4 bytes. DOUBLE PRECISION The DOUBLE PRECISION datatype is a variable-precision, floating-point number, supporting a range of around 11E-307to 1E+308 and a precision of at least 15 digits. The storage requirement is 8 bytes. FLOAT [(p)] The FLOAT datatype is a SQL-standard notation for specifying inexact numeric types. FLOAT takes an optional argument, (p), which signifies the minimum acceptable precision in binary digits. PostgreSQL interprets FLOAT(1)to FLOAT(24) as the REAL datatype, and FLOAT(25) to FLOAT(53) as the DOUBLEPRECISION datatype. Note REAL, DOUBLE PRECISION, and FLOAT also accept three special values in addition to ordinary numeric values. Those values represent the IEEE 754 special values of ‘Infinity’, ‘ Infinity’ (negative infinity), and ‘NaN’ (not a number). On input, these strings are recognized in a case-insensitive manner.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES TIMESTAMP [(p)] [without time zone] The TIMESTAMP datatype is responsible for storing a combination of date and time information. Like DATE, TIMESTAMP values are stored in a standard format, YYYY-MM-DD HH:MM:SS; the values can be inserted in a variety of string formats. For example, both ‘20040810 153510′and ‘2004-08-10 15:35:10′would be accepted as valid input. The range for the TIMESTAMP datatype is 4713 BC to 5874897 AD. The storage requirement is 8 bytes TIMESTAMP [(p)] WITH TIME ZONE The TIMESTAMP WITH TIME ZONE datatype, often referred to as just TIMESTAMPTZ, is responsible for storing a combination of date and time information along with time zone information. Like DATE, TIMSTAMPTZ values are stored in a standard format, YYYY-MM-DD HH:MM:SS+TZ; the values can be inserted in a variety of string formats. For example, both ‘20040810 153510′and ‘2004-08-10 15:35:10+02′ would be accepted as valid input. The range for the TIMESTAMP WITH TIME ZONE datatype is 4713 BCto 5874897 AD. The storage requirement is 8 bytes INTERVAL [(p)] The INTERVAL datatype is responsible for holding time intervals. The format for INTERVAL data can take the form of either explicitly declared intervals or implied intervals. For example, ‘4 05:01:02′ and ‘4 days 5 hours 1 min 2 sec’ are equivalent, valid input formats. Valid units for the INTERVAL type include second, minute, hour, day, week, month, year, decade, century, and millennium (and their plurals). The range for the INTERVAL type is -178000000 years to 178000000 years and the storage requirement is 12 bytes. Here s the generic syntax of INTERVAL: quantity unit [quantity unit…] Numeric Datatypes Numeric datatypes consist of 2-, 4-, and 8-byte integers, 4- and 8-byte floating-point numbers, and selectable-precision decimals. SMALLINT The SMALLINT datatype offers PostgreSQL s smallest integer range, supporting a range of -32,768 to 32,767. It is also referred to as INT2. The storage requirement is 2 bytes. INTEGER The INTEGER datatype is the usual choice for integer type, supporting a range of -2,147,483,648 to 2,147,483,647. It is also referred to as INT or INT4. The storage requirement is 4 bytes. BIGINT The BIGINT datatype offers PostgreSQL s largest integer range, supporting a range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. It is also referred to as INT8. The storage requirement is 8 bytes.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

636 CHAPTER 28 FROM DATABASES TO DATATYPES Date and Time Datatypes Numerous types are available for representing time- and date-based data. The TIME, TIMESTAMP, and INTERVAL datatypes can be declared with a precision value using the optional (p) argument. This argument specifies the number of fractional digits retained in the seconds field. DATE The DATE datatype is responsible for storing date information. By default, PostgreSQL displays DATE values in a standard YYYY-MM-DD format, although the values can be inserted using strings in a variety of different formats. For example, both ‘20040810′ and ‘2004-08-10′ would be accepted as valid input. The range for the DATE datatype is 4713 BCto 32767 AD, and the storage requirement is 4 bytes. Note For all date and time datatypes, PostgreSQL accepts any type of nonalphanumeric delimiter to separate the various date and time values. For example, ‘20040810′, ‘2004*08*10′, ‘2004, 08, 10′, and ‘2004!08!10′ are all the same as far as PostgreSQL is concerned. TIME [(p)] [without time zone] The TIME datatype is responsible for storing time information. The TIMEdatatype can take input in a number of string formats. The formats ‘04:05:06.789′, ‘04:05 PM’, and ‘040506′ are all examples of valid time input. The range for the TIME datatype is from 00:00:00.00to 23:59:59.99, and the storage requirement is 8 bytes. The following is an example of using the (p) argument in psql: company=# SELECT ‘12:34:56.543′::time(2); time 12:34:56.54 As you can see from the example, we cast the value to a time(2), meaning the time value will be stored only to the last two digits of precision. Normally, you do not have to worry about precision, because by default there is no explicit bound. TIME [(p)] WITH TIME ZONE The TIME datatype is responsible for storing time information along with time zone information. The TIME datatype can take input in a number of string formats. The formats ‘04:05:06.789 PST’, ‘04:05 PM’, and ‘040506-08′ are all examples of valid time input. The range for the TIME datatype is from 00:00:00.00to 23:59:59.99, and the storage requirement is 8 bytes. Tip For datatypes WITHTIMEZONE, if a time zone is not specified, the default system time zone is used. You can view the system time zone with the SHOW TIMEZONE command.
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES with the number passed into setval rather than the next value in the sequence. For example, if passed in with a value of 2112 and is_called set to FALSE, calling nextval will first return 2112 and then increase from there. Deleting a Sequence To delete a sequence, simply use the DROP SEQUENCE command: DROP SEQUENCE name [, …] [ CASCADE | RESTRICT ] The DROP SEQUENCE command allows you to enter one or more sequence names to be dropped in a given command. The CASCADE and RESTRICTkeywords function just like with other objects; if CASCADEis specified, any dependent objects will be dropped automatically; if RESTRICTis specified, PostgreSQL will refuse to drop the sequence. Datatypes and Attributes It makes sense that you would want to wield some level of control over the data placed into each column of a PostgreSQL table. For example, you might want to make sure that the value doesn t surpass a maximum limit, fall out of the bounds of a specific format, or even constrain the allowable values to a predefined set. To help in this task, PostgreSQL offers an array of datatypes that can be assigned to each column in a table. Each datatype forces the data to conform to a predetermined set of rules inherent to that datatype, such as size, type (string, integer, or decimal, for instance), and format (ensuring that it conforms to a valid date or time representation, for example). The behavior of these datatypes can be further tuned through the inclusion of attributes. This section introduces PostgreSQL s supported datatypes, as well as many of the commonly used attributes. Because many datatypes support the same attributes, the definitions are grouped under the heading Datatype Attributes rather than presented for each datatype. Any special behavior will be noted as necessary, however. PostgreSQL also offers the ability to create composite types and domains. A composite type is, in simple terms, a list of base types with associated field names. Domains are also derived from other types, but are based on a particular base type. However, they usually have some type of constraint that limits their values to a subset of what the underlying base type would allow. We will cover both of these features in this section as well. Datatypes Because PostgreSQL enables users to create their own custom types, any discussion of PostgreSQL s datatypes is bound to be incomplete. For purposes of the discussion here, we will cover the most common datatypes, offering information about the name, purpose, format, and range of each. If you would like more information on other datatypes offered by PostgreSQL, such as the inettype used for holding IP information, or the byteatype used for holding binary data, be sure to reference Chapter 8, Data Types, of the PostgreSQL online manual. To facilitate later reference of the material here, this section breaks down the datatypes into four categories: date and time, numeric, string, and Boolean.
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.