About table

Create table

• Syntax

    CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype(length) column_contraint,
    column2 datatype(length) column_contraint,
    column3 datatype(length) column_contraint,
    table_constraints
    );

• Creating a table that already exists will result in a error. The IF NOT EXISTS option allows you to create the new table only if it does not exist. When you use the IF NOT EXISTS option and the table already exists, PostgreSQL issues a notice instead of the error and skips creating the new table.

• The *column constraints include not null, unique, primary key, check, foreign key constraints.

• Table constraints are similar to column constraints except that they are applied to more than one column. Table constraints can be defined as column constraints like primary key, foreign key, check, unique constraints.

• Primary key column uniquely identify rows in a table. A table can have one and only one primary key. The primary key constraint allows you to define the primary key of a table.

• Example of creating table with column constraint,

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

• Example of creating table with table constraint,

    CREATE TABLE account_roles (
        user_id INT NOT NULL,
        role_id INT NOT NULL,
        grant_date TIMESTAMP,
        PRIMARY KEY (user_id, role_id),
        FOREIGN KEY (role_id) REFERENCES roles (role_id),
        FOREIGN KEY (user_id) REFERENCES accounts (user_id)
    );

Delete table

• Syntax

    DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];

• If you remove a table that does not exist, PostgreSQL issues an error. To avoid this situation, you can use the IF EXISTS option.

• In case the table that you want to remove is used in other objects such as views, triggers, functions, and stored procedures, the DROP TABLE cannot remove the table. In this case, you have two options:

  • The CASCADE option allows you to remove the table and its dependent objects.

  • The RESTRICT option rejects the removal if there is any object depends on the table. The RESTRICT option is the default if you don’t explicitly specify it in the DROP TABLE statement.

• Note: You need to have the roles of the superuser, schema owner, or table owner in order to drop tables.

• To remove multiple tables at once, you can place a comma-separated list of tables after the DROP TABLE keywords.

Alter table

• Syntax

    ALTER TABLE table_name action;

• Actions can be,

  • Add or drop a column

  • Change the datatype of a column

  • Rename a column

  • Set a default value of a column

  • Add a constraint for the column

  • Rename a table

• Syntax for above actions to alter table

  • Add a column

      ALTER TABLE table_name 
      ADD COLUMN column_name datatype column_constraint;

  • Drop a column

      ALTER TABLE table_name 
      DROP COLUMN column_name

  • Rename a column

      ALTER TABLE table_name 
      RENAME COLUMN column_name 
      TO new_column_name;

  • Change a default value of a column

      ALTER TABLE table_name 
      ALTER COLUMN column_name 
      [SET DEFAULT value | DROP DEFAULT];

  • Add a check constraint to a column

      ALTER TABLE table_name 
      ADD CHECK expression;
  
      -- Example
      ALTER TABLE employee 
      ADD CHECK (category IN ('manager', 'labour', 'executive', 'coder'));

  • Add a constraint to a table

      ALTER TABLE table_name 
      ADD CONSTRAINT constraint_name constraint_definition;
  
      -- Example
      ALTER TABLE links 
      ADD CONSTRAINT unique_url UNIQUE ( url );

  • Rename a table

      ALTER TABLE table_name 
      RENAME TO new_table_name;