Managing tables

In this section, we will learn how to manage tables in a database.

PostgreSQL has three types of tables:

• Temporary tables: Very fast tables, visible only to the user who created them
• Unlogged tables: Very fast tables to be used as support tables common to all users
• Logged tables: Regular tables

We will now use the following steps to create a user table from scratch:

1. Let’s connect to forumdb as the forum user:

   postgres@learn_postgresql:~$ psql -U forum forumdb
   forumdb=>
   

2. Execute the following command:

   forumdb=> CREATE TABLE myusers (
    pk int GENERATED ALWAYS AS IDENTITY
    , username text NOT NULL
    , gecos text
    , email text NOT NULL
    , PRIMARY KEY( pk )
    , UNIQUE ( username )
    );
   CREATE TABLE
   

   The CREATE TABLE command creates a new table. The GENERATED AS IDENTITY command automatically assigns a unique value to a column.

3. Observe what was created on...