Home > Articles > Data > SQL

This chapter is from the book

This chapter is from the book

Creating New Tables

The previous section described how to create and drop databases. Now let’s move down one level in the PostgreSQL storage hierarchy and talk about creating and dropping tables.

You’ve created some simple tables in the first two chapters; it’s time to talk about some of the more advanced features of the CREATE TABLE command. Here is the command that you used to create the customers table:

CREATE TABLE customers (
        customer_id   INTEGER UNIQUE,
        customer_name VARCHAR(50),
        phone         CHAR(8),
        birth_date    DATE,
        balance       DECIMAL(7,2)
);

This command creates a permanent table named customers. A table name must meet the naming criteria described earlier in this chapter. When you create a table, PostgreSQL automatically creates a new data type2 with the same name as the table. This means that you can’t create a table whose name is the same as an existing data type.

When you execute this command, the customers table is created in the database that you are connected to. If you are using PostgreSQL 7.3 or later, the customers table is created in the first schema in your search path. (If you are using a version older than 7.3, your copy of PostgreSQL does not support schemas). If you want the table to be created in some other schema, you can prefix the table name with the schema qualifier, for example:

CREATE TABLE joes_video.customers( ... );

The new table is owned by you. You can’t give ownership to another user at the time you create the table, but you can change it later using the ALTER TABLE...OWNER TO command (described later).

When you create a table (or an index), you can tell PostgreSQL to store the object in a specific tablespace by including a TABLESPACE tablespacename clause, like this:

CREATE TABLE joes_video.customers( ... ) TABLESPACE mytablespace;

If you don’t specify a tablespace, PostgreSQL creates the table in the tablespace assigned to the schema (if you’re creating an index without specifying a tablespace, the index is created in the tablespace of the parent table).

Temporary Tables

I mentioned earlier that the customers table is a permanent table. You can also create temporary tables. A permanent table persists after you terminate your PostgreSQL session; a temporary table is automatically destroyed when your PostgreSQL session ends. Temporary tables are also local to your session, meaning that other PostgreSQL sessions can’t see temporary tables that you create. Because temporary tables are local to each session, you don’t have to worry about colliding with the name of a table created by another session.

If you create a temporary table with the same name as a permanent table, you are effectively hiding the permanent table. For example, let’s create a temporary table that hides the permanent customers table:

CREATE TEMPORARY TABLE customers (
        customer_id   INTEGER UNIQUE,
        customer_name VARCHAR(50),
        phone         CHAR(8),
        birth_date    DATE,
        balance       DECIMAL(7,2)
);

Notice that the only difference between this command and the command that you used to create the permanent customers table is the TEMPORARY keyword3. Now you have two tables, each named customers. If you now SELECT from or INSERT into the customers table, you will be working with the temporary table. Prior to version 7.3, there was no way to get back to the permanent table except by dropping the temporary table:

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00
           8 | Wink Wankel          | 555-1000 | 1988-12-25 |    0.00
(5 rows)

movies=# CREATE TEMPORARY TABLE customers
movies-# (
movies(#   customer_id   INTEGER UNIQUE, 
movies(#   customer_name VARCHAR(50),
movies(#   phone         CHAR(8),
movies(#   birth_date    DATE,
movies(#   balance       DECIMAL(7,2)
movies(#   );
CREATE

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
(0 rows)

movies=# DROP TABLE customers;
DROP

movies=# SELECT * FROM customers;
 customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
           1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00
           2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00
           3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00
           4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00
           8 | Wink Wankel          | 555-1000 | 1988-12-25 |    0.00
(5 rows)

Starting with release 7.3, you can access the permanent table by including the name of the schema where the permanent table resides.

A temporary table is like a scratch pad. You can use a temporary table to accumulate intermediate results. Quite often, you will find that a complex query can be formulated more easily by first extracting the data that interests you into a temporary table. If you find that you are creating a given temporary table over and over again, you might want to convert that table into a view. See the section titled "Using Views" in Chapter 1 for more information about views.

Table Constraints

In Chapter 2, "Working with Data in PostgreSQL," we explored the various constraints that you can apply to a column: NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, and CHECK(). You can also apply constraints to a table as a whole or to groups of columns within a table.

First, let’s look at the CHECK() constraint. The syntax for a CHECK() constraint is

    [CONSTRAINT constraint-name] CHECK( boolean-expression )

When you define a CHECK() constraint for a table, you are telling PostgreSQL that any insertions or updates made to the table must satisfy the boolean-expression given within the constraint. The difference between a column constraint and a table constraint is that a column constraint should refer only to the column to which it relates. A table constraint can refer to any column in the table.

For example, suppose that you had an orders table to track customer orders:

CREATE TABLE orders 
(
    customer_number    INTEGER,
    part_number        CHAR(8),
    quantity_ordered   INTEGER,
    price_per_part     DECIMAL(7,2)
);

You could create a table-related CHECK() constraint to ensure that the extended price (that is, quantity_ordered times price_per_part) of any given order is at least $5.00:

CREATE TABLE orders 
(
    customer_number    INTEGER,
    part_number        CHAR(8),
    quantity_ordered   INTEGER,
    price_per_part     DECIMAL(7,2),

    CONSTRAINT verify_minimum_order
     CHECK (( price_per_part * quantity_ordered) >= 5.00::DECIMAL )
);

Each time a row is inserted into the orders table (or the quantity_ordered or price_per_part columns are updated), the verify_minimum_order constraint is evaluated. If the expression evaluates to FALSE, the modification is rejected. If the expression evaluates to TRUE or NULL, the modification is allowed.

You may have noticed that a table constraint looks very much like a column constraint. PostgreSQL can tell the difference between the two types by their placement within the CREATE TABLE statement. A column constraint is placed within a column definition—after the column’s data type and before the comma. A table constraint is listed outside of a column definition. The only tricky spot is a table constraint that follows the last column definition; you normally would not include a comma after the last column. If you want a constraint to be treated as a table constraint, be sure to include a comma following the last column definition. At the moment, PostgreSQL does not treat table constraints and column constraints differently, but in a future release it may.

Each of the table constraint varieties is related to a type of column constraint.

The UNIQUE table constraint is identical to the UNIQUE column constraint, except that you can specify that a group of columns must be unique. For example, here is the rentals table as currently defined:

CREATE TABLE rentals
(
    tape_id     CHARACTER(8),
    customer_id INTEGER,
    rental_date DATE
);

Let’s modify this table to reflect the business rule that any given tape cannot be rented twice on the same day:

CREATE TABLE rentals
(
    tape_id     CHARACTER(8),
    customer_id INTEGER,
    rental_date DATE,

    UNIQUE( rental_date, tape_id )

);

Now when you insert a row into the rentals table, PostgreSQL will ensure that there are no other rows with the same combination of rental_date and tape_id. Notice that I did not provide a constraint name in this example; constraint names are optional.

The PRIMARY KEY table constraint is identical to the PRIMARY KEY column constraint, except that you can specify that the key is composed of a group of columns rather than a single column.

The REFERENCES table constraint is similar to the REFERENCES column constraint. When you create a REFERENCES column constraint, you are telling PostgreSQL that a column value in one table refers to a row in another table. More specifically, a REFERENCES column constraint specifies a relationship between two columns. When you create a REFERENCES table constraint, you can relate a group of columns in one table to a group of columns in another table. Quite often, you will find that the unique identifier for a table (that is, the PRIMARY KEY) is composed of multiple columns. Let’s say that the Virtual Video Store is having great success and you decide to open a second store. You might want to consolidate the data for each store into a single database. Start by creating a new table:

CREATE TABLE stores
(
    store_id     INTEGER PRIMARY KEY,
    location     VARCHAR
);

Now, change the definition of the customers table to include a store_id for each customer:

CREATE TABLE customers (
        store_id      INTEGER REFERENCES stores( store_id ),
        customer_id   INTEGER UNIQUE,
        customer_name VARCHAR(50),
        phone         CHAR(8),
        birth_date    DATE,
        balance       DECIMAL(7,2),

        PRIMARY KEY( store_id, customer_id )
);

The store_id column in the customers table refers to the store_id column in the stores table. Because store_id is the primary key to the stores table, you could have written the REFERENCES constraint in either of two ways:

store_id INTEGER REFERENCES stores( store_id )

or

store_id INTEGER REFERENCES stores

Also, notice that the primary key for this table is composed of two columns: store_id and customer_id. I can have two customers with the same customer_id as long as they have different store_ids.

Now you have to change the rentals table as well:

CREATE TABLE rentals
(
    store_id    INTEGER,
    tape_id     CHARACTER(8),
    customer_id INTEGER,
    rental_date DATE,

    UNIQUE( rental_date, tape_id )
    FOREIGN KEY( store_id, customer_id ) REFERENCES customers
);

The customers table has a two-part primary key. Each row in the rentals table refers to a row in the customers table, so the FOREIGN KEY constraint must specify a two-part foreign key. Again, because foreign key refers to the primary key of the customers table, I can write this constraint in either of two forms:

FOREIGN KEY( store_id, customer_id ) 
  REFERENCES customers( store_id, customer_id )

or

FOREIGN KEY( store_id, customer_id ) 
 REFERENCES customers

Now that I have the referential integrity constraints defined, they will behave as described in the "Column Constraints" section of Chapter 2. Remember, a table constraint functions the same as a column constraint, except that table constraints can refer to more than one column.

Dropping Tables

Dropping a table is much easier than creating a table. The syntax for the DROP TABLE command is

DROP TABLE table-name [, ...];

If you are using PostgreSQL 7.3 or later, you can qualify the table name with a schema. For example, here is the command to destroy the rentals table:

DROP TABLE rentals;

If the rentals table existed in some schema other than your current schema, you would qualify the table name:

DROP TABLE sheila.rentals;

You can destroy a table only if you are the table’s owner or if you are a PostgreSQL superuser. Notice that I used the word destroy here rather than drop. It’s important to realize that when you execute a DROP TABLE command, you are destroying all the data in that table.

PostgreSQL has a nice feature that I have not seen in other databases: You can roll back a DROP TABLE command. Try the following experiment. First, let’s view the contents of the tapes table:

movies=# SELECT * FROM tapes;
 
tape_id  |     title     | duration
----------+---------------+----------
 AB-12345 | The Godfather |
 AB-67472 | The Godfather |
 MC-68873 | Casablanca    |
 OW-41221 | Citizen Kane  |
 AH-54706 | Rear Window   |
(5 rows)

Now, start a multistatement transaction and destroy the tapes table:

movies=# BEGIN WORK;
BEGIN

movies=# DROP TABLE tapes;

NOTICE: DROP TABLE implicitly drops referential integrity trigger
         from table "rentals"
DROP

If you try to SELECT from the tapes table, you’ll find that it has been destroyed:

movies=# SELECT * FROM tapes;
ERROR:  Relation "tapes" does not exist

If you COMMIT this transaction, the table will permanently disappear; let’s ROLLBACK the transaction instead:

movies=# ROLLBACK;
ROLLBACK

The ROLLBACK threw out all changes made since the beginning of the transaction, including the DROP TABLE command. You should be able to SELECT from the tapes table again and see the same data that was there before:

movies=# SELECT * FROM tapes;
tape_id  |     title     | duration
----------+---------------+----------
 AB-12345 | The Godfather |
 AB-67472 | The Godfather |
 MC-68873 | Casablanca    |
 OW-41221 | Citizen Kane  |
 AH-54706 | Rear Window   | 
(5 rows)

This is a very nice feature. You can roll back CREATE TABLE, DROP TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, and so on. I’ll discuss transactions a bit later in this chapter. For now, I’d like to point out a few details that I glossed over in the previous example. You may have noticed that the DROP TABLE command produced a NOTICE.

movies=# DROP TABLE tapes;
NOTICE: DROP TABLE implicitly drops referential integrity trigger
         from table "rentals"
DROP

When you drop a table, PostgreSQL will automatically DROP any indexes defined for that table as well as any triggers or rules. If other tables refer to the table that you dropped (by means of a REFERENCE constraint), PostgreSQL will automatically drop the constraints in the other tables. However, any views that refer to the dropped table will not be removed—a view can refer to many tables and PostgreSQL would not know how to remove a single table from a multitable SELECT.

Inheritance

Another PostgreSQL feature that is uncommon in relational database systems is inheritance. Inheritance is one of the foundations of the object-oriented programming paradigm. Using inheritance, you can define a hierarchy of related data types (in PostgreSQL, you define a hierarchy of related tables). Each layer in the inheritance hierarchy represents a specialization of the layer above it4.

Let’s look at an example. The Virtual Video database defines a table that stores information about the tapes that you have in stock:

movies=# \d tapes
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 tape_id  | character(8)          | not null
 title    | character varying(80) | not null
 duration | interval              |

For each tape, you store the tape_id, title, and duration. Let’s say that you decide to jump into the twenty-first century and rent DVDs as well as videotapes. You could store DVD records in the tapes table, but a tape and a DVD are not really the same thing. Let’s create a new table that defines the characteristics common to both DVDs and videotapes:

CREATE TABLE video
(
  video_id        CHARACTER(8) PRIMARY KEY,
  title           VARCHAR(80),
  duration        INTERVAL
);

Now, create a table to hold the DVDs. For each DVD you have in stock, you want to store everything in the video table plus a region_id and an array of audio_tracks. Here is the new table definition:

movies=# CREATE TABLE dvds
movies-# (
movies(#   region_id    INTEGER,
movies(#   audio_tracks VARCHAR[]
movies(# ) INHERITS ( video );

Notice the last line in this command: You are telling PostgreSQL that the dvds table inherits from the video table. Now let’s INSERT a new DVD:

movies=# INSERT INTO dvds VALUES
movies=# ( 
movies(#   ‘ASIN-750’,                       -- video_id
movies(#   ‘Star Wars’,                      -- title
movies(#   ‘121 minutes’,                    -- duration
movies(#   1,                                -- region_id
movies(#   ‘{English,Spanish}’               -- audio_tracks
movies(# );

Now, if you SELECT from the dvds table, you’ll see the information that you just inserted:

 video_id |   title   | duration | region_id |   audio_tracks
----------+-----------+----------+-----------+-------------------
 ASIN-750 | Star Wars | 02:01:00 |         1 | {English,Spanish}

At this point, you might be thinking that the INHERITS clause did nothing more than create a row template that PostgreSQL copied when you created the dvds table. That’s not the case—if you simply want to create a table that has the same structure as another table, use the LIKE table-name clause instead of the INHERITS table-name clause. When we say that dvds inherits from video, we are not simply saying that a DVD is like a video, we are saying that a DVD is a video. Let’s SELECT from the video table now; remember, you haven’t explicitly inserted any data into the video table, so you might expect the result set to be empty:

movies=# SELECT * FROM video;
 video_id |   title   | duration
----------+-----------+----------
 ASIN-750 | Star Wars | 02:01:00

A DVD is a video. When you SELECT from the video table, you see only the columns that comprise a video. When you SELECT from the dvds table, you see all the columns that comprise a DVD. In this relationship, you say that the dvds table specializes5 the more general video table.

If you are using a version of PostgreSQL older than 7.2, you must code this query as SELECT * FROM video* to see the DVD entries. Starting with release 7.2, SELECT will include descendent tables and you have to say SELECT * FROM ONLY video to suppress descendents.

You now have a new table to track your DVD inventory; let’s go back and redefine the tapes table to fit into the inheritance hierarchy. For each tape, we want to store a video_id, a title, and a duration. This is where we started: the video table already stores all this information. You should still create a new table to track videotapes—at some point in the future, you may find information that relates to a videotape, but not to a DVD:

movies=# CREATE TABLE tapes ( ) INHERITS( video );
CREATE

This CREATE TABLE command creates a new table identical in structure to the video table. Each row in the tapes table will contain a video_id, a title, and a duration. Insert a row into the tapes table:

movies=# INSERT INTO tapes VALUES
movies-# (
movies(#   ‘ASIN-8YD’, 
movies(#   ‘Flight To Mars(1951)’,
movies(#   ‘72 min’
movies(# );
INSERT

When you SELECT from the tapes table, you should see this new row:

movies=# SELECT * FROM tapes;
 tape_id  |        title         | duration
----------+----------------------+----------
 ASIN-8YD | Flight To Mars(1951) | 01:12:00 
(1 row)

And because a tape is a video, you would also expect to see this row in the video table:

movies=# SELECT * FROM video;
 video_id |             title              | duration
----------+--------------------------------+----------
 ASIN-750 | Star Wars                      | 02:01:00
 ASIN-8YD | Flight To Mars(1951)           | 01:12:00
(2 rows)

Now here’s the interesting part. A DVD is a video—any row that you add to the dvds table shows up in the video table. A tape is a video—any row that you add to the tapes table shows up in the video table. But a DVD is not a tape (and a tape is not a DVD). Any row that you add to the dvds table will not show up in the tapes table (and vice versa).

If you want a list of all the tapes you have in stock, you can SELECT from the tapes table. If you want a list of all the DVDs in stock, SELECT from the dvds table. If you want a list of all videos in stock, SELECT from the videos table.

In this example, the inheritance hierarchy is only two levels deep. PostgreSQL imposes no limit to the number of levels that you can define in an inheritance hierarchy. You can also create a table that inherits from multiple tables—the new table will have all the columns defined in the more general tables.

I should caution you about two problems with the current implementation of inheritance in PostgreSQL. First, indexes are not shared between parent and child tables. On one hand, that’s good because it gives you good performance. On the other hand, that’s bad because PostgreSQL uses an index to guarantee uniqueness. That means that you could have a videotape and a DVD with the same video_id. Of course, you can work around this problem by encoding the type of video in the video_id (for example, use a T for tapes and a D for DVDs). But PostgreSQL won’t give you any help in fixing this problem. The other potential problem with inheritance is that triggers are not shared between parent and child tables. If you define a trigger for the topmost table in your inheritance hierarchy, you will have to remember to define the same trigger for each descendant.

We have redefined some of the example tables many times in the past two chapters. In a real-world environment, you probably won’t want to throw out all your data each time you need to make a change to the definition of an existing table. Let’s explore a better way to alter a table.

ALTER TABLE

Now that you have a video table, a dvds table, and a tapes table, let’s add a new column to all three tables that you can use to record the rating of the video (PG, G, R, and so on).

You could add the rating column to the tapes table and to the dvds table, but you really want the rating column to be a part of every video. The ALTER TABLE ... ADD COLUMN command adds a new column for you, leaving all the original data in place:

movies=# ALTER TABLE video ADD COLUMN rating VARCHAR;
ALTER

Now, if you look at the definition of the video table, you will see the new column:

movies=# \d video
                Table "video"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 video_id | character(8)          | not null
 title    | character varying(80) |
 duration | interval              |
 rating   | character varying     |
Indexes:
    "video_pkey" PRIMARY KEY, btree (video_id)

After the ALTER TABLE command completes, each row in the video table has a new column; the value of every rating column will be NULL. Because you have changed the definition of a video, and a DVD is a video, you might expect that the dvds table will also contain a rating column:

movies=# \d dvds
                       Table "dvds"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 video_id     | character(8)          | not null
 title        | character varying(80) |
 duration     | interval              |
 region_id    | integer               |
 audio_tracks | character varying[]   |
 rating       | character varying     |
Inherits: video

Similarly, the tapes table will also inherit the new rating column:

movies=# \d tapes
                    Table "tapes"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 video_id | character(8)          | not null
 title    | character varying(80) |
 duration | interval              |
 rating   | character varying     |
Inherits: video 

Starting with PostgreSQL version 8.0, you can change the data type of an existing column using ALTER TABLE. For example, to change the data type of the customers.customer_id column from INTEGER to NUMERIC( 7, 2 ), you could execute the command:

ALTER TABLE customers ALTER COLUMN customer_id TYPE NUMERIC( 7,2 )

As long as PostgreSQL knows how to convert a value from the old data type to the new data type, you can freely change data types. If PostgreSQL doesn’t know how to convert between the old and new types, you can include a USING expression clause to tell PostgreSQL how to perform the conversion. The expression following the USING keyword typically refers to the original column value. For example, if you want to change the data type of customers.customer_id and multiply each customer_id by 100 at the same time, use the following command:

ALTER TABLE customers ALTER COLUMN customer_id TYPE NUMERIC( 7,2 ) USING customer_id * 100

You can also refer to other columns in the USING expression. For example, say that you are currently storing each customer name in two columns, last_name and first_name, and you’ve decided to combine them into a single column named customer name. You can do that with the following commands:

movies=# ALTER TABLE customers
movies-#     ALTER COLUMN last_name 
movies-#         TYPE VARCHAR USING ( last_name || ‘,’ || first_name ),
movies-#     DROP COLUMN first_name;
ALTER TABLE

movies=# ALTER TABLE customers
movies-#     RENAME COLUMN last_name TO customer_name;
ALTER TABLE

The first ALTER TABLE command performs two alterations. First, for each row in the table, it evaluates the expression last_name || ‘,’ || first_name and assigns that value to the last_name column (converting the result into type VARCHAR along the way). Next, the (first) ALTER TABLE command removes the first_name column from each row. You’re left with a single column called last_name that contains the concatenation of the original last_name and first_name columns (with a comma in between). The second ALTER TABLE command renames the last_name column to customer_name.

Keep in mind that some ALTER TABLE commands will take longer to execute than others. It takes very little time to change the name of a column. It can take quite a while to change the data type of a column (because PostgreSQL has to traverse every row in the table and write out a new version). If you use ALTER TABLE ... SET TABLESPACE to move a table from one tablespace to another, the server must physically copy each block in the table. In most cases, it’s faster to execute a series of ALTER TABLE commands than it is to read the old data into a client application, change each row, and then write the result back to the server. When you use an ALTER TABLE command, the entire transformation occurs within the server; if you modify the structure of a table using a custom-written client application, you have to send every row to the client, perform the transformation, and then send every row back to the server.

The ALTER TABLE command is useful when you are in the development stages of a project. Using ALTER TABLE, you can add new columns to a table, define default values, rename columns (and tables), add and drop constraints, change the data type of a column, and transfer ownership. The capabilities of the ALTER TABLE command seem to grow with each new release—see the PostgreSQL Reference Manual for more details.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020