Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Basic Data Definition Commands

Basic Data Definition Commands

Learn how to create a database and tables in PostgreSQL, and about truncating a table.

We'll cover the following...

Create a database

A database or schema is a named collection of objects within a database. It can contain tables, views, sequences, and functions. We use schemas to organize data and control access to it. When a database user can be assigned a schema, only the objects in the user’s schema are accessible to that user.

The following is the syntax for creating a database in PostgreSQL. 

CREATE DATABASE <database_name>
Syntax of the CREATE DATABASE statement

In PostgreSQL, we can create a new database by using the CREATE DATABASE statement. Here, <database_name> specifies the name of the database that we want to create.

We can specify additional properties for the database using the WITH clause, as in the statement below:

PostgreSQL
CREATE DATABASE customer_db 
WITH 
  OWNER = postgres 
  LC_COLLATE = 'C' 
  LC_CTYPE = 'C' 
  TABLESPACE = pg_default 
  CONNECTION LIMIT = -1;
\l customer_db

  • Line 1: The CREATE DATABASE statement creates a new database named customer_db.

  • Line 2: The OWNER = postgres part specifies that the owner of the database will be the user postgres.

  • Lines 3–4: The LC_COLLATE and LC_CTYPE properties specify the collation and character set for the database. Their value is set as C, indicating that it will follow the C language standard.

  • Line 5: The TABLESPACE property denotes that the tablespace, or location in which the tables and other objects are stored, will be the default tablespace for the PostgreSQL environment.

  • Line 6: There’s no limit on the number of connections that can be made to this database. The CONNECTION LIMIT setting can be used to limit the number of simultaneous connections allowed.

  • Line 9: The \l customer_db command prints the details of the newly created databases.

Displaying the list of databases in PostgreSQL

To print the list of databases in PostgreSQL, we use the \l command.

PostgreSQL
-- sets the expanded table formatting mode 
\x
-- displays the list of databases and its properties
\l

The \l shortcut is for the list command. The list command lists all the databases in the current PostgreSQL session.

  • The Name column displays the name of the databases.

  • The Owner column displays the database owner. In this case, all databases have the postgres user as the owner.

  • The Encoding column displays the character encoding scheme used in the database. The default encoding in PostgreSQL is SQL_ASCII.

  • The Collate column displays the collation order used in the database. The C locale is the default character classification scheme used in PostgreSQL.

  • The Ctype column denotes the character classification scheme used in the database. The C denotes C-style, POSIX-style, or default sorting order.

  • The Access privileges column displays users’ access privileges on the database. The T denotes total access (full read and write access). The c/postgres statement means that the user can connect to the database as postgres, while CTc/postgres means that a user can create new databases and connect to any database.

Create table

The following is the syntax of the command to create a table in PostgreSQL: 

CREATE TABLE <table_name> (
    <column_name> <data_type>, 
    <column_name> <data_type>, 
    <column_name> <data_type>,
    ..., 
    <column_name> <data_type>)
Syntax of a CREATE TABLE statement

To create a new table in PostgreSQL, we use the CREATE TABLE statement. Here, <table_name> specifies the name of the table that we want to create, <column_name> is the column’s name in the table, and <data_type> is the data type of the column. We can have one or more columns in a table. PostgreSQL has many different data types that we can use.

CREATE TABLE Customer (
    id INTEGER, 
    name VARCHAR(255), 
    dob DATE
);
Using the CREATE TABLE command in PostgreSQL

Displaying the list of tables in PostgreSQL

To print the list of tables in a database, we use the \dt command.

PostgreSQL
CREATE TABLE Customer (
    id INTEGER, 
    name VARCHAR(255), 
    dob DATE
);
\dt

The CREATE TABLE command creates a new table called Customer. The id column is of type INTEGER and can store whole numbers. The name column is of type VARCHAR(255) and can store strings up to 255 characters long. The dob column is of type DATE and can store date values.

The \dt command displays the list of relations in the current database. A relation is a table, view, index, sequence, or synonym.

  • The Schema column displays the schema that the relation is in. The command output shows that the Customer table is a relation in the public schema.

  • The Name column displays the relation’s name.

  • The Type column displays the relation’s type. In this case, the Customer table is a table. Other supported types are view, index, sequence, and synonym.

  • The Owner column displays the relation’s owner. In this case, the owner is postgres.

Displaying the structure of a table

To print the structure of a table, we use the \d command.

The syntax for the \d command as below:

 \d <table_name>
Syntax of the \d command

Here, <table_name> is the name of the table that we want to print the structure for.

PostgreSQL
CREATE TABLE Customer (
    id INTEGER,
    name VARCHAR(255), 
    dob DATE
);
\d Customer

  • The \d Customer command prints the structure of the Customer table.

  • The Column column displays the name of the column.

  • The Type column shows the data type of the column.

  • The Modifiers column indicates any column constraints, such as NOT NULL or UNIQUE.

The Customer table has three columns: id, name, and dob. The id column is an integer that can store whole numbers. The name column is of type VARCHAR(255), which can store strings up to 255 characters long. The dob column is a date that can store date values.

Creating a table with constraints

A column constraint is used to specify the rules for a column. To create a table with constraints, we use the following column constraints:

  • NOT NULL: It ensures that a column cannot store NULL values.

  • UNIQUE: It ensures that all values in a column are different.

  • PRIMARY KEY: It defines a primary key.

  • CHECK: It ensures that all values in a column satisfy a specific condition.

  • DEFAULT: It sets a default value for a column when we insert a new row without specifying the value for that column.

  • FOREIGN KEY: It defines a foreign key to ensure that all values in a column are valid values of another column in another table.

The NOT NULL, UNIQUE, and DEFAULT constraints

The following statement creates a Customer table with multiple constraints:

PostgreSQL
CREATE TABLE Customer (
    id INTEGER UNIQUE, 
    name VARCHAR(255) NOT NULL, 
    dob DATE DEFAULT '2000-01-01'
);
\d Customer

  • The name column in the Customer table can’t store NULL values because it has the NOT NULL constraint.

  • The id column has the UNIQUE constraint. The UNIQUE constraints in the id column ensure that a column contains unique values. The UNIQUE and PRIMARY KEY constraints are interchangeable; they both ensure that a column contains unique values.

  • The dob column as the DEFAULT constraints set to 2000–01-01. When we insert a new row into the Customer table without specifying the value for the dob column, PostgreSQL will insert 2000-01-01 into that column.

Check

The following statement creates a table with a CHECK constraint on the column dob to ensure that dob has a valid value: 

CREATE TABLE Customer (
    id INTEGER, 
    name VARCHAR(255), 
    dob DATE CHECK (dob > '1900-01-01'), 
);
\d Customer

The dob column has the CHECK constraint that ensures the column’s value is greater than 1900-01-01. PostgreSQL will raise an error if we try inserting a row with a dob less than 1900-01-01.

Primary key

We use the PRIMARY KEY column constraint to create a table with a primary key. A primary key is a column or a set of columns uniquely identifying a row in a table. 

CREATE TABLE Customer (
    id INTEGER PRIMARY KEY, 
    name VARCHAR(255), 
    dob DATE
);
CREATE TABLE with a PRIMARY KEY constraint

The id column is the primary key of the Customer table. The PRIMARY KEY column constraint requires that the id column contains unique values. PostgreSQL will raise an error if we try inserting a row with a duplicate id.

To verify that the Customer table is created, we use the \d command:

PostgreSQL
\d Customer

The \d Customer prints the structure of the Customer table we’ve just created. The table has three columns: id, name, and dob. The id column is of the INTEGER type that can store whole numbers. The name column is of character varying(255) type, storing strings up to 255 characters long. The dob column is a date that can store date values. The table also has a primary key constraint on the id column.

Set of columns as the primary key

We can create a table with a set of columns as the primary key by using the PRIMARY KEY column constraint, as shown in the example below: 

CREATE TABLE Customer (
    id INTEGER, 
    name VARCHAR(255), 
    dob DATE, 
    PRIMARY KEY (id, name)
);

Here, the id and name columns are the primary key of the Customer table. The PRIMARY KEY column constraint requires that the (id, name) combination contains unique values. PostgreSQL will raise an error if we try inserting a row with a duplicate (id, name) combination.

Creating a table with foreign key

A foreign key is a column or a set of columns in one table that provides a link between data in two tables. A foreign key value must match an existing primary key value in the other table. PostgreSQL will raise an error if we try to insert or update a row in the child table with values that don’t exist in the parent table. PostgreSQL uses the FOREIGN KEY column constraint to create a foreign key.

The customer_id column is a foreign key in the Order table. The data type of the customer_id column must be the same as the data type of the id column in the Customer table. 

CREATE TABLE Order (
    id INTEGER,
    customer_id INTEGER, 
    product_id INTEGER, 
    FOREIGN KEY (customer_id) REFERENCES Customer(id),
)

The FOREIGN KEY column constraint requires that the value of the customer_id column in the Order table to exist in the id column of the Customer table.

The TRUNCATE statement

If we want to remove the data from the table so that it can’t be recovered, we can use the TRUNCATE statement.

The syntax for the TRUNCATE statement is as below: 

TRUNCATE TABLE <table_name>;

Here, <table_name> is the name of the table that we want to remove all the data from and reset the auto-incrementing id column (if there is one) to 1.

The following statement would remove all the data from the Employee table:

PostgreSQL
TRUNCATE TABLE Employee;

It’s important to note that while the TRUNCATE statement can be faster and more efficient than deleting all the rows individually, and it doesn’t allow for any conditions to be specified in the WHERE clause.

Note: The TRUNCATE statement can’t be used if there are any foreign key constraints. Be careful when using this statement, as it can’t be undone. If we want to remove only certain rows from a table, it’s better to use the DELETE statement with a WHERE clause.