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

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.

634 CHAPTER 28 FROM DATABASES TO DATATYPES Sequence Functions The primary interaction with sequences is handled through several sequence-manipulation functions. The functions allow you to retrieve and manipulate the values within a sequence. nextval The nextval function is used to generate the next value of a sequence. We ll discuss functions more in Chapter 33, but for now the syntax should be straightforward enough: SELECT nextval(’sequence_name’); currval The currval function is used to determine the most recently obtained value of a sequence on the given connection. This assumes you have called nextval at least once during the session; otherwise, currval will fail. The syntax follows that of nextval: SELECT currval (’sequence_name’); lastval The lastval function, new in PostgreSQL 8.1, operates similarly to currval, except that instead of explicitly stating the sequence to be called against, lastval automatically returns the value of the last sequence nextval was called against: SELECT lastval(); This makes it a little easier to manipulate tables, because you can insert into a table and retrieve the generated serial key value without having to know the name of the sequence. Like currval, calling lastval in a session where nextvalhas not been called will generate an error. setval The last of the sequence-manipulation functions, setval is used to set a sequence s value to a specified number. The setvalfunction actually offers two different syntaxes, the first of which follows: SELECT setval(’sequence_name’,value); This version of setval is fairly straightforward, setting the named sequence s value to value. Once setval has been executed in this way, subsequent nextval calls will begin returning the next value based on the sequence definition. For example, if you call setval on a sequence and give it a value of 2112, calling nextval on the sequence will return 2113, and then increase from there. Optionally, you can pass in a third value to setval to control this behavior, using the following syntax: SELECT setval(’sequence_name’, value, is_called); In this form, the value determines if the sequence will treat the number passed in as having been called before. By setting is_called as TRUE, you achieve the same behavior as the two-parameter form of setval; however, by setting is_called as FALSE, the sequence will start
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

CHAPTER 28 FROM DATABASES TO DATATYPES Finally, after all that, you decide that it really isn t necessary to track the employee s birth date. Go ahead and delete the column: ALTER TABLE employee DROP COLUMN birthdate; Working with Sequences Sequences are special database objects created for the purpose of assigning unique numbers for input into a table. Sequences are typically used for generating primary key values, especially in cases where you need to do multiple concurrent inserts but need the keys to remain unique. Let s now look at how to work with sequences. Creating a Sequence The syntax for creating a sequence is as follows: CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] The TEMPORARY and TEMP keywords indicate that the sequence should be created only for the existing session and then dropped on session exit. By default, a sequence increments one at a time, but you can change this by using the optional INCREMENT BY keywords. The MINVALUE and MAXVALUE keywords work as expected, supplying a minimum and maximum value for the sequence to generate. The default values are 1and 263 (roughly 9 million trillion) 1. The START WITH keywords allow you to specify an initial number for the sequence to begin with other than 1. The CACHE option allows you to specify a number of sequence values to be pre-allocated and stored in memory for faster access. Finally, the CYCLEand NO CYCLE options control whether the sequence should wrap around to the starting value once MAXVALUE has been reached, or should throw an error, which is the default behavior. Modifying Sequences You can modify the majority of values of a sequence by using the ALTER SEQUENCE command. The syntax is as follows: ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] As you can see, the ALTER SEQUENCE command follows the same structure as the CREATE SEQUENCE command, and its keywords match those of the former command as well. Additionally, starting in PostgreSQL 8.1, you can issue the following command to change which schema a sequence is located in: ALTER SEQUENCE name SET SCHEMA new_schema
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.