PostgreSQL Table Creation and Data Types

Introduction

In this lab, we will explore PostgreSQL table creation and data types. The goal is to understand fundamental data types like integers, text, dates, and booleans, which are crucial for defining table structures and ensuring data integrity.

We will connect to the PostgreSQL database using psql, create tables with primary keys using SERIAL, and add basic constraints such as NOT NULL and UNIQUE. We will then inspect the table structure and insert data to demonstrate the usage of different data types like INTEGER, SMALLINT, TEXT, VARCHAR(n), and CHAR(n).

Skills Graph

Explore PostgreSQL Data Types

In this step, we will explore some of the fundamental data types available in PostgreSQL. Understanding data types is crucial for defining table structures and ensuring data integrity. We'll cover common types like integers, text, dates, and booleans.

First, let's connect to the PostgreSQL database. Open a terminal and use the psql command to connect to the postgres database as the postgres user. Since the postgres user is the default superuser, you might need to use sudo to switch to that user first.

sudo -u postgres psql

You should now be in the PostgreSQL interactive terminal. You'll see a prompt like postgres=#.

Now, let's explore some basic data types.

1. Integer Types:

PostgreSQL offers several integer types with varying ranges. The most common are INTEGER (or INT) and SMALLINT.

• INTEGER: A typical choice for most integer values.
• SMALLINT: Used for smaller integer values to save space.

Let's create a simple table to demonstrate these types:

CREATE TABLE integer_example (
    id SERIAL PRIMARY KEY,
    quantity INTEGER,
    small_quantity SMALLINT
);

Here, SERIAL is a special type that automatically generates a sequence of integers, making it suitable for primary keys.

Now, insert some data:

INSERT INTO integer_example (quantity, small_quantity) VALUES (100, 10);
INSERT INTO integer_example (quantity, small_quantity) VALUES (2000000, 32767);

You can view the data using:

SELECT * FROM integer_example;

Output:

 id | quantity | small_quantity
----+----------+----------------
  1 |      100 |             10
  2 |  2000000 |          32767
(2 rows)

2. Text Types:

PostgreSQL provides TEXT, VARCHAR(n), and CHAR(n) for storing text.

• TEXT: Stores variable-length strings of unlimited length.
• VARCHAR(n): Stores variable-length strings with a maximum length of n.
• CHAR(n): Stores fixed-length strings of length n. If the string is shorter, it's padded with spaces.

Let's create another table:

CREATE TABLE text_example (
    id SERIAL PRIMARY KEY,
    name TEXT,
    short_name VARCHAR(50),
    code CHAR(5)
);

Insert some data:

INSERT INTO text_example (name, short_name, code) VALUES ('PostgreSQL Database', 'PostgreSQL', 'PG001');
INSERT INTO text_example (name, short_name, code) VALUES ('Another Database', 'Another', 'AD002');

View the data:

SELECT * FROM text_example;

Output:

 id |        name        | short_name | code
----+--------------------+------------+-------
  1 | PostgreSQL Database | PostgreSQL | PG001
  2 | Another Database   | Another    | AD002
(2 rows)

3. Date and Time Types:

PostgreSQL offers DATE, TIME, TIMESTAMP, and TIMESTAMPTZ for handling date and time values.

• DATE: Stores only the date (year, month, day).
• TIME: Stores only the time (hour, minute, second).
• TIMESTAMP: Stores both date and time without time zone information.
• TIMESTAMPTZ: Stores both date and time with time zone information.

Create a table:

CREATE TABLE datetime_example (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP,
    event_timestamptz TIMESTAMPTZ
);

Insert data:

INSERT INTO datetime_example (event_date, event_time, event_timestamp, event_timestamptz)
VALUES ('2023-10-27', '10:30:00', '2023-10-27 10:30:00', '2023-10-27 10:30:00+00');

View the data:

SELECT * FROM datetime_example;

Output:

 id | event_date | event_time |   event_timestamp   |    event_timestamptz
----+------------+------------+---------------------+----------------------------
  1 | 2023-10-27 | 10:30:00   | 2023-10-27 10:30:00 | 2023-10-27 10:30:00+00
(1 row)

4. Boolean Type:

The BOOLEAN type stores true/false values.

Create a table:

CREATE TABLE boolean_example (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN
);

Insert data:

INSERT INTO boolean_example (is_active) VALUES (TRUE);
INSERT INTO boolean_example (is_active) VALUES (FALSE);

View the data:

SELECT * FROM boolean_example;

Output:

 id | is_active
----+-----------
  1 | t
  2 | f
(2 rows)

Finally, exit the psql terminal:

\q

You have now explored some of the fundamental data types in PostgreSQL. These data types form the building blocks for creating robust and well-defined database schemas.

Create Tables with Primary Keys

In this step, we will learn how to create tables with primary keys in PostgreSQL. A primary key is a column or a set of columns that uniquely identifies each row in a table. It enforces uniqueness and serves as a crucial element for data integrity and relationships between tables.

First, let's connect to the PostgreSQL database. Open a terminal and use the psql command to connect to the postgres database as the postgres user.

sudo -u postgres psql

You should now be in the PostgreSQL interactive terminal.

Understanding Primary Keys

A primary key has the following characteristics:

• It must contain unique values.
• It cannot contain NULL values.
• A table can have only one primary key.

Creating a Table with a Primary Key

There are two common ways to define a primary key when creating a table:

1. Using the PRIMARY KEY constraint within the column definition:

   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(100),
       price DECIMAL(10, 2)
   );

   In this example, product_id is defined as the primary key using the PRIMARY KEY constraint. The SERIAL keyword automatically creates a sequence to generate unique integer values for the product_id.

2. Using the PRIMARY KEY constraint separately:

   CREATE TABLE customers (
       customer_id INT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       PRIMARY KEY (customer_id)
   );

   Here, the PRIMARY KEY constraint is defined separately, specifying that the customer_id column is the primary key.

Example: Creating a users table with a primary key

Let's create a users table with a primary key using the SERIAL type for automatic ID generation:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registration_date DATE
);

In this table:

• user_id is the primary key, automatically generated using SERIAL.
• username is a unique username for each user.
• email is the user's email address.
• registration_date is the date the user registered.

Now, let's insert some data into the users table:

INSERT INTO users (username, email, registration_date) VALUES ('john_doe', '[email protected]', '2023-10-26');
INSERT INTO users (username, email, registration_date) VALUES ('jane_smith', '[email protected]', '2023-10-27');

You can view the data using:

SELECT * FROM users;

Output:

 user_id |  username  |        email        | registration_date
---------+------------+---------------------+---------------------
       1 | john_doe   | [email protected] | 2023-10-26
       2 | jane_smith | [email protected] | 2023-10-27
(2 rows)

Trying to insert a duplicate primary key

If you try to insert a row with a duplicate primary key, PostgreSQL will raise an error:

INSERT INTO users (user_id, username, email, registration_date) VALUES (1, 'duplicate_user', '[email protected]', '2023-10-28');

Output:

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (user_id)=(1) already exists.

This demonstrates the primary key constraint in action, preventing duplicate values.

Finally, exit the psql terminal:

\q

You have now successfully created a table with a primary key and observed how it enforces uniqueness. This is a fundamental concept in database design.

Add Basic Constraints (NOT NULL, UNIQUE)

In this step, we will learn how to add basic constraints to tables in PostgreSQL. Constraints are rules that enforce data integrity and consistency. We'll focus on two fundamental constraints: NOT NULL and UNIQUE.

First, let's connect to the PostgreSQL database. Open a terminal and use the psql command to connect to the postgres database as the postgres user.

sudo -u postgres psql

You should now be in the PostgreSQL interactive terminal.

Understanding Constraints

Constraints are used to limit the type of data that can be inserted into a table. This ensures the accuracy and reliability of the data in the database.

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. This is useful when a particular piece of information is essential for every row in the table.

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct. This is useful for columns that should have unique identifiers or values, such as usernames or email addresses (besides the primary key).

Adding Constraints During Table Creation

You can add constraints when you create a table. Let's create a table called employees with NOT NULL and UNIQUE constraints:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

In this table:

• employee_id is the primary key.
• first_name and last_name are declared as NOT NULL, meaning they must have a value for every employee.
• email is declared as UNIQUE, ensuring that each employee has a unique email address.

Now, let's try to insert some data that violates these constraints.

Attempting to insert a NULL value into a NOT NULL column:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES (NULL, 'Smith', '[email protected]', '2023-10-27');

Output:

ERROR:  null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, null, Smith, [email protected], 2023-10-27).

This error indicates that you cannot insert a NULL value into the first_name column because of the NOT NULL constraint.

Attempting to insert a duplicate value into a UNIQUE column:

First, insert a valid row:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Smith', '[email protected]', '2023-10-27');

Now, try to insert another row with the same email:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', '[email protected]', '2023-10-28');

Output:

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

This error indicates that you cannot insert a duplicate email address because of the UNIQUE constraint.

Inserting valid data:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', '[email protected]', '2023-10-28');

View the data:

SELECT * FROM employees;

Output:

 employee_id | first_name | last_name |        email        | hire_date
-------------+------------+-----------+---------------------+------------
           1 | John       | Smith     | [email protected] | 2023-10-27
           2 | Jane       | Doe       | [email protected]  | 2023-10-28
(2 rows)

Finally, exit the psql terminal:

\q

You have now successfully created a table with NOT NULL and UNIQUE constraints and observed how they enforce data integrity.

Inspect Table Structure

In this step, we will learn how to inspect the structure of tables in PostgreSQL. Understanding the structure of a table, including column names, data types, constraints, and indexes, is essential for querying and manipulating data effectively.

First, let's connect to the PostgreSQL database. Open a terminal and use the psql command to connect to the postgres database as the postgres user.

sudo -u postgres psql

You should now be in the PostgreSQL interactive terminal.

The \d command

The primary tool for inspecting table structure in psql is the \d (describe) command. This command provides detailed information about a table, including:

• Column names and data types
• Constraints (primary keys, unique constraints, not-null constraints)
• Indexes

Inspecting the employees table

Let's inspect the structure of the employees table we created in the previous step:

\d employees

Output:

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 first_name  | character varying(50)  |           | not null |
 last_name   | character varying(50)  |           | not null |
 email       | character varying(100) |           |          |
 hire_date   | date                   |           |          |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

The output provides the following information:

• Table "public.employees": Indicates the table name and schema.
• Column: Lists the column names (employee_id, first_name, last_name, email, hire_date).
• Type: Shows the data type of each column (integer, character varying, date).
• Nullable: Indicates whether a column can contain NULL values (not null or blank).
• Default: Shows the default value for a column (if any).
• Indexes: Lists the indexes defined on the table, including the primary key (employees_pkey) and the unique constraint on the email column (employees_email_key).

Inspecting other tables

You can use the \d command to inspect any table in the database. For example, to inspect the users table created in step 2:

\d users

Output:

                                            Table "public.users"
      Column       |          Type          | Collation | Nullable |                Default
-------------------+------------------------+-----------+----------+----------------------------------------
 user_id           | integer                |           | not null | nextval('users_user_id_seq'::regclass)
 username          | character varying(50)  |           |          |
 email             | character varying(100) |           |          |
 registration_date | date                   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

Listing all tables

To list all tables in the current database, you can use the \dt command:

\dt

Output (will vary depending on the tables you have created):

              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | boolean_example  | table | postgres
 public | customers        | table | postgres
 public | datetime_example | table | postgres
 public | employees        | table | postgres
 public | integer_example  | table | postgres
 public | products         | table | postgres
 public | text_example     | table | postgres
 public | users            | table | postgres
(8 rows)

Finally, exit the psql terminal:

\q

You have now learned how to inspect the structure of tables in PostgreSQL using the \d and \dt commands. This is a fundamental skill for understanding and working with databases.

Summary

In this lab, we explored fundamental PostgreSQL data types, focusing on integers and text. We learned about INTEGER and SMALLINT for storing integer values, understanding their different ranges and use cases. We also examined TEXT, VARCHAR(n), and CHAR(n) for handling text data, noting the distinctions between variable-length and fixed-length strings.

Furthermore, we practiced creating tables using these data types, including the use of SERIAL for automatically generating primary key sequences. We inserted sample data into the tables and verified the data using SELECT statements, solidifying our understanding of how these data types behave in a practical database context.