Table Manipulations

This section discusses how to create databases and tables and manipulate the data inside them. In this section, databases are written to, and not just read, as in previous sections.

Data Types

PostgreSQL supports boolean, character, number, temporal (date and time-related data types), special types, and arrays. Data types exist to provide an initial data fidelity check at the point of entry.

Boolean

• boolean or bool are used to create Boolean (true/false) columns
• 1, yes, y, t, true are all converted to true
• 0, no, n, f, false are all converted to false
• PostgreSQL displays t for true, f for false, (space) for null

Character

• char declares a single character
• char(n) declares a fixed length character string. If a string is shorter than the length of the column, PostgreSQL will pad spaces. If longer, it will issue an error.
• varchar(n) declares a variable-length character string. It will store up to n characters, and PostgreSQL will not pad spaces if the string is shorter.

Number

• Integers come in three types:
  • smallint - 2-byte signed integer (-32768, 32767)
  • int - 4-byte integer (-214783648, 214783647)
  • serial - same as integer except PostgreSQL automatically populates it
• Floating-point numbers come in three types:
  • float(n) - n-, or maximum 8-, byte floating point number
  • real or float8 - double-precision (8-byte) floating point number
  • numeric or numeric(p,s) - real number with p digits and s numbers after the decimal point. numeric(p,) is the exact number.

Temporal

• date - stores date data
• time - stores time data
• timestamp - stores date and time
• interval - stores the difference in timestamps
• timestamptz - store both timestamp and timezone data

Primary and Foreign Keys

• Primary Keys are columns or groups of columns used to identify a row uniquely in a table.
  • Defined through primary key constraints
  • Tables can have one and only one primary key
  • Every table should have a primary key
  • When a primary key is added to a table, PostgreSQL creates a unique index on that column or group of columns
  • Datatype for primary keys is often serial because it auto-increments when new rows are added

Syntax for defining a primary key in a table:

CREATE TABLE table_name (
       column_name data_type PRIMARY_KEY,
       column_name data_type,
       ...);

• Foreign Keys are fields or groups of fields in a table that uniquely identifies a row in another table. It refers to the primary key of the other table.
  • referencing table or child table - the table that contains the foreign key
  • referenced table or parent table - the table that the foreign key references
  • A given table can have multiple foreign keys depending on how it is related to other tables
  • Defined through foreign key constraints
  • Foreign key constraints maintain referential integrity between child and parent tables

Create Table

To create a new table, use CREATE TABLE

  CREATE TABLE table_name(
         column_name TYPE column_constraint,
         table_constraint)
INHERITS existing_table_name;

The new table will “inherit” all columns of the existing table that is listed after the INHERITS keyword.

Column Constraints

• NOT NULL - value of the column cannot be null
• UNIQUE - value of the column must be unique across the whole table. The column can have many null values, however.
• PRIMARY KEY - combination of NOT NULL and UNIQUE
• CHECK - enables check of a condition when a user inserts or updates data
  • Values in price column must be positive, for example
• REFERENCES - defines the foreign key constraint

Table Constraints

• UNIQUE (column_list) - forces the value stored in the columns listed in the parentheses to be unique
• PRIMARY KEY (column_list) - defines the primary key that consists of multiple columns
• CHECK (condition) - checks a condition when inserting or updating data
• REFERENCES - to constrain the value stored in the column to one that exists in a column in another table.

Create Table Examples

CREATE TABLE account(
       user_id serial PRIMARY KEY,
       username VARCHAR(50) UNIQUE NOT NULL,
       password VARCHAR(50) NOT NULL,
       email VARCHAR(355) UNIQUE NOT NULL,
       created_on TIMESTAMP NOT NULL,
       last_login TIMESTAMP);

CREATE TABLE role(
       role_id serial PRIMARY KEY,
       role_name VARCHAR(255) UNIQUE NOT NULL);

 CREATE TABLE account_role(
        user_id integer NOT NULL,
        role_id integer NOT NULL,
        grant_date timestamp without time zone,
PRIMARY KEY (user_id,role_id),

CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
        REFERENCES role (role_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES account (user_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
)

INSERT

After creation, tables are setup, but will have zero rows. Adding data to a table requires usage of INSERT.

Syntax to enter a single row:

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

Syntax to enter multiple rows at a time:

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...),
       (value3, value4, ...), ...;

Sytax to enter data that comes form another table:

INSERT INTO table
SELECT column1, column2
  FROM another_table
 WHERE condition;

Insert Example

The following statement creates the empty table.

CREATE TABLE link(
    ID serial PRIMARY KEY,
       url VARCHAR(255) NOT NULL,
       name VARCHAR(255) NOT NULL,
       description VARCHAR(255),
       rel VARCHAR(50)
);

This statement INSERTs the first row into the table.

INSERT INTO link(url,name)
VALUES
('www.google.com','Google');

Following execution of these statements:

An example of inserting multiple rows:

INSERT INTO link(url,name)
VALUES
('www.bing.com','Bing'),
('www.amazon.com','Amazon');

An example of creating a table with the same structure as another. Tables created in this manner will be empty.

CREATE TABLE link_copy (LIKE link);

To write rows from the original link table into the new link_copy table:

INSERT INTO link_copy
SELECT * FROM link
 WHERE name = 'Bing';

UPDATE

UPDATE is used to update existing data in a table.

UPDATE table
   SET column1 = value1,
       column2 = value2, ...
 WHERE condition;

An example:

UPDATE link
   SET description = 'Google Website'
 WHERE name LIKE '%Google%';

UPDATE link
   SET description = name;

The RETURNING keyword allows users to see the rows that were just UPDATEd, rather than just seeing “query returned successfully.”

UPDATE link
   SET description = 'New Description'
 WHERE id = 1
RETURNING id,url,name,description;

DELETE

If the WHERE condition of a DELETE statement is omitted, then the entire table is deleted. The DELETE statement returns the number of rows deleted. If no rows are deleted, it returns zero.

DELETE FROM table
 WHERE condition

ALTER Table

To change existing table structure, use an ALTER TABLE statement.

ALTER TABLE table_name action;

There are several possible action statements, including:

• ADD COLUMN - adds a new column
• DROP COLUMN - deletes a column
• RENAME COLUMN - renames a column
• ADD CONSTRAINT - adds CHECK constraint to a column
• RENAME TO - renames a table

Create a new, empty table.

CREATE TABLE link(
       link_id serial PRIMARY KEY,
       title VARCHAR(512) NOT NULL,
       url VARCHAR(1024) NOT NULL UNIQUE);

Add a new column.

ALTER TABLE link
  ADD COLUMN active boolean;

ALTER TABLE link
 DROP COLUMN active;

 ALTER TABLE link
RENAME COLUMN title TO new_title;

 ALTER TABLE link
RENAME TO url_table;

Drop Table

To remove an existing table from the database, use DROP TABLE as follows. The IF EXISTS term is optional.

DROP TABLE [IF EXISTS] table_name

Two other keywords of note:

• RESTRICT - does not drop the table if any objects depend on it. PostgreSQL implicitly includes RESTRICT.
• CASCADE - drops this table as well as any dependent objects together.

Both these keywords are appended on to the end of the DROP TABLE statement, following the table_name.

Constraints

CHECK

A CHECK constraint is a kind of constraint that allows you to specify if a value in a column must meet a certain requirement. The constraint uses a boolean expression to evaluate the values of a column; if the value of the column passes the check, PostgreSQL will insert or update those values.

CREATE TABLE new_users(
       id serial PRIMARY KEY,
       first_name VARCHAR(50),
       birth_date DATE CHECK(birth_date > '1900-01-01')
       join_date DATE CHECK(join_date > birth_date),
       salary integer CHECK(salary > 0));

If any of the CHECKs above are violated, then PostgreSQL will generate an error and the data will not be added to the table.

It is also possible to name the check tests, as shown below, where the check test is named “positive_sales.” If the constraint is not named, then PostgreSQL automatically names it.

CREATE TABLE checktest(
       sales integer CONSTRAINT positive_sales CHECK(sales > 0));

INSERT INTO checktest(sales)
VALUES (-5);

The foregoing will return:

ERROR: New row for relation "checktest" violates check constraint "positive_sales"
DETAIL: Fail row contains (-5)
SQL state: 23514

NOT NULL

• As it pertains to databases, NULL is unknown or missing information. It is different than empty or zero.
  • If we don’t know a person’s email, we can use the NULL
  • If the person doesn’t have an email address, we can use the empty string
• A NOT NULL constraint means that the user must enter a value for that column

CREATE TABLE learn_null(
       first_name VARCHAR(50),
       sales integer NOT NULL);

INSERT INTO learn_null(first_name)
VALUES ('John');

ERROR: null value in column "sales" violates not-null constraint
DETAIL: Failing row contains (John, null).
SQL state: 23502

UNIQUE

• If a column has a UNIQUE constraint, every time an additional row is inserted, PostgreSQL checks if the value is already in the table.
  • If the value is already in the table, it will return an error and reject the INSERT or UPDATE

CREATE TABLE people(
       id serial PRIMARY KEY,
       first_name VARCHAR(50),
       email VARCHAR(100) UNIQUE);

INSERT INTO people(id,first_name,email)
VALUES (1,'Joe','[email protected]'),
       (2,'Joseph','[email protected]');

ERROR: duplicate key value violates unique constraint "people_email_key"
DETAIL: Key (email)=([email protected]) already exists.
SQL state: 23505