> For the complete documentation index, see [llms.txt](https://anon-coders-notes.gitbook.io/techwriterdev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://anon-coders-notes.gitbook.io/techwriterdev/database/sql/postgresql/02_table_operation.md).

# About table

## Create table

* Syntax

```sql
    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,

```sql
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,

```sql
    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

```sql
    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

```sql
    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

  ```sql
      ALTER TABLE table_name 
      ADD COLUMN column_name datatype column_constraint;
  ```

  * Drop a column

  ```sql
      ALTER TABLE table_name 
      DROP COLUMN column_name
  ```

  * Rename a column

  ```sql
      ALTER TABLE table_name 
      RENAME COLUMN column_name 
      TO new_column_name;
  ```

  * Change a default value of a column

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

  * Add a check constraint to a column

  ```sql
      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

  ```sql
      ALTER TABLE table_name 
      ADD CONSTRAINT constraint_name constraint_definition;

      -- Example
      ALTER TABLE links 
      ADD CONSTRAINT unique_url UNIQUE ( url );
  ```

  * Rename a table

  ```sql
      ALTER TABLE table_name 
      RENAME TO new_table_name;
  ```