Creating Tables and Databases in PostgreSQL (How to Define Data Structures and Constraints)

1. What is creating tables and databases?

In PostgreSQL, both tables and databases are basic units used for storing and managing data. Tables are two-dimensional arrays of columns used to organize data; they represent the physical implementation of data. Databases are logical units that contain multiple tables; they are a higher-level structure.

2. Steps to create a table

2.1 CREATE TABLE command

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );

For example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE
);

In the above example, we create a table named users, which contains three columns: id, name, and email.

2.2 Specify data types

PostgreSQL supports various data types, including integers (INT), text (VARCHAR), and dates (DATE).

2.3 Specify constraints

Constraints are rules used to ensure certain characteristics of the data in a table. Common constraints include:

  • PRIMARY KEY: Represents one or more columns that must be unique and cannot contain duplicates.
  • UNIQUE: Represents one or more columns that must be distinct, but may allow nulls (though uniqueness applies only to non-null values). Correction based on context of typical SQL docs where "allows duplicate" usually implies allowing NULLs in the set if not strictly enforced as a single value constraint vs PK. -> Refined Translation for accuracy: Represents one or more columns that must be unique; however, it allows null values (which are typically ignored by uniqueness checks).
  • NOT NULL: Indicates that a column cannot contain NULL values.

2.4 Adding constraints when creating the table

When creating a table, we can add constraints. For example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

In the above example, we added NOT NULL and UNIQUE constraints for columns name and email.

3. Steps to create a database

3.1 CREATE DATABASE command

CREATE DATABASE database_name;

For example:

CREATE DATABASE mydatabase;

In the above example, we created a database named mydatabase.

3.2 Start (connect) the database

Use the following command to connect to the newly created database.

\c mydatabase

In the above example, we use the \c command to connect to the mydatabase database.

4. Close a database

When you no longer need a specific database, you can close it using the following command:

\q

5. Operations on tables and data

PostgreSQL provides various methods for operating on tables and data, including INSERT, UPDATE, and DELETE.