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

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.

632 CHAPTER 28 FROM DATABASES TO DATATYPES Deleting a Table Deleting, or dropping, a table is accomplished via the DROP TABLEstatement. Its syntax follows: DROP TABLE tbl_name [, tbl_name…] [ CASCADE | RESTRICT ] For example, you could delete your employee table as follows: DROP TABLE employee; You could also simultaneously drop the employee2 and employee3 tables created in previous examples like so: DROP TABLE employee2 employee3; By default, dropping a table removes any constraints, indexes, rules, and triggers that exist for the table specified. However, to drop a table that is referenced by a foreign key (see the REFERENCES section later in the chapter for more information) in another table, or by a view, you must specify the CASCADE parameter, which removes any dependent views entirely. However, it removes only the foreign-key constraint in the other tables, not the tables entirely. Altering a Table Structure You ll find yourself often revising and improving your table structures, particularly in the early stages of development. However, you don t have to go through the hassle of deleting and recreating the table every time you d like to make a change. Rather, you can alter the table s structure with the ALTER statement. With this statement, you can delete, modify, and add columns as you deem necessary. Like CREATE TABLE, the ALTER TABLE statement offers a vast number of clauses, keywords, and options. You can look up the gory details in the PostgreSQL manual on your own. This section offers several examples intended to get you started quickly. Let s begin with adding a column. Suppose you want to track each employee s birth date with the employee table: ALTER TABLE employee ADD COLUMN birthday TIMESTAMPTZ; Whoops! You forgot the NOT NULL clause. You can modify the new column as follows: ALTER TABLE employee ALTER COLUMN birthday SET NOT NULL; Most people don t know what time they were born, so changing the datatype to a DATE would be more appropriate. In previous versions of PostgreSQL, this would have meant going through the trouble of creating a new column, updating it, dropping the old column, and then renaming the new column. Fortunately, as of PostgreSQL 8.0, you can now do it simply, using the ALTER TYPE command: ALTER TABLE employee ALTER COLUMN birthday TYPE DATE; Of course, now that it is a date column, maybe it would be better served to change the name of the column to birthdate. This is done with the RENAME command: ALTER TABLE employee RENAME COLUMN birthday TO birthdate;
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

CHAPTER 28 FROM DATABASES TO DATATYPES CREATE TEMPORARY TABLE emp_temp2 ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, email VARCHAR(45) NOT NULL ) ON COMMIT DROP ; Remember that this is only useful when used within BEGIN and COMMIT commands; otherwise, the table will be silently dropped as soon as it is created. Note In PostgreSQL, ownership of the TEMPORARY privilege is required to create temporary tables. See Chapter 29 for more details about PostgreSQL s privilege system. Viewing a Database s Available Tables You can view a list of the tables made available to a database with the dt command: company=# dt The result of running this command would look something like this: List of relations Schema | Name | Type | Owner ——–+———-+——-+—— public | employee | table | rob (1 row) Viewing Table Structure You can view a table structure by using the d command along with the table name: company=# d employee This produces results similar to the following: Table “public.employee” Column | Type | Modifiers ———–+———————–+—————-empid | integer | not null firstname | character varying(25) | not null lastname | character varying(25) | not null email | character varying(45) | not null phone | character varying(10) | not null Indexes: “employee_pkey” PRIMARY KEY, btree (empid)
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

630 CHAPTER 28 FROM DATABASES TO DATATYPES Tip You can choose whatever naming convention you prefer when declaring PostgreSQL tables. However, you should choose one format and stick with it (for example, all lowercase and singular). Take it from experience, constantly having to look up the exact format of table names because a set format was never agreed upon can be quite annoying. As you read earlier in the discussion of schemas, you can also create a table in a schema other than the default schema. To do so, simply prepend the table name with the desired schema name, like so: schemaname.tablename. Copying a Table Creating a new table based on an existing one is a trivial task. The following query produces a copy of the employee table, naming it employee2: CREATE TABLE employee2 AS SELECT * FROM employee; The new table, employee2, will be added to the database. Be aware that while the new table may look like an exact copy of the employee table, it will not contain any default values, triggers, or constraints that may have existed in the original table (these are covered in more detail later in this chapter as well as in Chapter 34). Sometimes you might be interested in creating a table based on just a few columns found in an existing table. You can do so by simply specifying the columns within the CREATE SELECT statement: CREATE TABLE employee3 AS SELECT firstname,lastname FROM employee; Creating a Temporary Table Sometimes it s useful to create tables that have a lifetime that is only as long as the current session. For example, you might need to perform several queries on a subset of a particularly large table. Rather than repeatedly run those queries against the entire table, you can create a temporary table for that subset and then run the queries against the smaller temporary table instead. This is accomplished by using the TEMPORARY keyword (or just TEMP) in conjunction with the CREATE TABLE statement: CREATE TEMPORARY TABLE emp_temp AS SELECT firstname,lastname FROM employee; Temporary tables are created in the same way as any other table would be, except that they re stored in a temporary schema, typically something like pg_temp_1. This handling of the temporary schema is done automatically by the database, and is mostly transparent to the end user. By default, temporary tables last until the end of the current user session; that is, until you disconnect from the database. Sometimes, however, it can be handy to keep a temporary table around only until the end of the current transaction. (Well go into more detail on transactions in Chapter 36; for now, you can think of them as a grouped set of operations, designated by the BEGIN and COMMIT keywords.) You can do this by using the ONCOMMITDROP syntax:
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 List of relations Schema | Name | Type | Owner ————-+———-+——-+——mynewschema | customer | table | rob public | customer | table | rob (2 rows) This example shows two tables named customer located in the company database. The first table is in the schema we created called mynewschema, and the second table is in the default schema called public. Remember that the public schema is automatically created for you and that, by default, all tables will be created within that schema unless you designate otherwise. Working with Tables This section demonstrates how to create, list, review, delete, and alter tables in PostgreSQL. Creating a Table A table is created using the CREATE TABLE statement. A vast number of options and clauses specific to this statement are available, but it seems a bit impractical to introduce them all in what is an otherwise informal introduction. Instead, we ll introduce various features of this statement as they become relevant in future sections. The purpose of this section is to demonstrate general usage. As an example, let s create an employee table for the company database: company=# CREATE TABLE employee ( company(# empid SERIAL UNIQUE NOT NULL, company(# firstname VARCHAR(40) NOT NULL, company(# lastname VARCHAR(40) NOT NULL, company(# email VARCHAR(80) NOT NULL, company(# phone VARCHAR(25) NOT NULL, company(# PRIMARY KEY(empid) company(# ); NOTICE: CREATE TABLE will create implicit sequence “employee_empid_seq” for serial column “employee.empid” NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “employee_pkey” for table “employee” CREATE TABLE You can always go back and alter a table structure after it has been created. Later in the chapter, the section Altering a Table Structure demonstrates how this is accomplished via the ALTER TABLE statement. You will notice that creating this table produces several notices about things like sequences and indexes. Don t worry about these for now the meaning of SERIAL, UNIQUE, NOTNULL, and so on will be described later in the chapter.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

628 CHAPTER 28 FROM DATABASES TO DATATYPES Altering Schemas You can change the name of a schema by using the ALTERSCHEMA command: ALTER SCHEMA rob RENAME TO robert; Dropping Schemas Dropping a schema is done through the DROPSCHEMA command. By default, you cannot drop a schema that contains any objects. You can control this by using the CASCADE or RESTRICT keywords: DROP SCHEMA robert CASCADE; The Schema Search Path Once you begin adding schemas into your database, you will quickly begin to realize that working with multiple schemas can be a pain when you have to reference every object with a fully qualified schemaname.tablename notation. To get around this problem, PostgreSQL supports a schema search path setting akin to the search paths used for executables and libraries in most operating systems. In order for the operating system to find an executable or library, you first have to tell it where to look by giving it a list of directories that could contain the item of interest. Then, you have to place the item into one of these directories. The same applies to the PostgreSQL search path. When you reference a table with an unqualified name, PostgreSQL searches through the schemas listed in the search path until it finds a matching table. You can view the current search path with the following command: rob=# show search_path; Running this command will show the search path: search_path $user,public (1 row) The default search path is equivalent to a schema with the same name as the current user, and then the public schema, which is the default schema created for all databases. You can change the search path by issuing a set command, like so: set search_path=”$user”,public,mynewschema; This would add the schema mynewschemainto the search path, and allow any tables, views, or other system objects to be referenced unqualified. Consider the following command that lists all customer tables in the search path: company=# dt *.customer As you can see, the new schema is included in the results:
You want to have a cheap webhost for your apache application, then check apache web hosting services.

CHAPTER 28 FROM DATABASES TO DATATYPES ]$ dropdb company DROP DATABASE ]$ Modifying Existing Databases You can also modify certain aspects of a database by using the ALTER DATABASE command. One such example would be that of renaming an existing database: template1=# ALTER DATABASE company RENAME TO testing; ALTER DATABASE template1=# As with the DROP DATABASE command, you cannot rename a database that has any active connections. Although you can modify other attributes of a database, the ALTER DATABASE command has contained different options in every release since it was added in 7.3, and there will be additional changes in 8.1 as well, so we will refer you to the documentation for your specific version for a complete list of options. Tip You may have noticed that this text often uses all uppercase text for SQL keywords such as ALTER, DATABASE, and RENAME. This is not mandatory; you could accomplish all of the examples in this book using lowercase commands. However, using all uppercase is fairly common practice, and your code will be much more readable if you follow this convention. Working with Schemas Schemas contain a collection of tables, views, functions, and other types of objects, within a single database. Unlike with multiple databases, multiple schemas within a database are designed to allow any user to easily access any of the objects within any of the schemas in the database, as long as they have the proper permissions. A few of the reasons you might want to use schemas include: To organize database objects into logical groups to make them more manageable To allow multiple users to work within one database without interfering with each other To put third-party applications into separate schemas so that they do not collide with the names of existing objects in your database The commands discussed in this section will help you get started using schemas. Creating Schemas You can use the CREATESCHEMA command to create new schemas: CREATE SCHEMA rob;
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.

626 CHAPTER 28 FROM DATABASES TO DATATYPES Creating a Database There are two common ways to create a database. Perhaps the easiest is to create it using the CREATE DATABASE command from within the psql client: template1=# CREATE DATABASE company; CREATE DATABASE You can also create a database via the createdb command-line tool: ]$ createdb company CREATE DATABASE ]$ Common problems that lead to failed database creation include insufficient or incorrect permissions, or an attempt to create a database that already exists. Connecting to a Database Once the database has been created, you can connect to it with the cpsql command: template1=# c company You are now connected to database “company”. company=# Alternatively, you can connect directly into that database when logging in via the psql client by passing its name on the command line, like so: ]$ psql company In both cases, you ll immediately have the database tables and data at your disposal upon executing each command. Deleting a Database You delete a database in much the same fashion as you create one. You can delete it from within the psql client with the DROP DATABASE command: company=# DROP DATABASE company; ERROR: cannot drop the currently open database You should be aware that you cannot drop a database that is currently being accessed. If you are connected to the database, you must first connect to another database before the DROP command will work: company=# c template1 You are now connected to database “template1″. template1=# DROP DATABASE company; DROP DATABASE template1=# Alternatively, you can delete it with the dropdb command-line tool:
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 Taking time to properly design your project s data model is key to its success. Neglecting to do so can have dire consequences not only on storage requirements, but also on application performance, maintainability, and data integrity. In this chapter, you ll become better acquainted with the many facets of the hierarchy of objects within PostgreSQL. By its conclusion, you will be familiar with the following topics: The difference between the various levels of the PostgreSQL hierarchy, including clusters, databases, schemas, and tables. The purpose and range of PostgreSQL s supported datatypes. To facilitate reference, these datatypes are broken into four categories: date and time, numeric, textual, and Boolean. PostgreSQL s table attributes, which serve to further modify the behavior of tables and their columns. How to use advanced concepts, such as constraints and domains, to help further enforce data integrity. Working with Databases While most people think of a database as a single entity, the truth is that a single installation of PostgreSQL can handle many unique databases at the same time. This collection of databases is technically referred to as a cluster. In this section, we look at how to manipulate databases within a cluster. Default Databases By default, a PostgreSQL cluster comes with two template databases, template0 and template1. These databases contain all of the basic information that is needed to create new databases on the system. When you initially connect to a new installation of PostgreSQL, you ll want to connect to the template1 database and use that to create a new database. If there are schema objects or extensions that you need to load into PostgreSQL that you want all future databases to have access to, you can load them into the template1 database. The template0 database is mainly provided as a backup in case you manage to modify your template1 database in a manner that cannot be corrected.
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.