# PostgreSQL  - TABLE  Operations

## Intro

Lets review all of the Table-related functions in one place.

### Create tables in the database

Top create a table in your database use the `CREATE TABLE` command. Here, we define the schema of a table. We create *users* table which has two attributes/fields/columns, *name* and *email*. Each of these attributes are defined as character strings with max of 128 characters

```pgsql
people=> CREATE TABLE users(
people(> name VARCHAR(128),
people(> email VARCHAR(128)
people(> );
CREATE TABLE
people=>
```

### List tables in the database

To list tables/relations in the database use to he following command:

```pgsql
people=> \dt
Did not find any relations.
```

if you haven't created any tables in your database yet, you will get "Did not find any relations." message. Otherwise, you will get list of existing tables like what follows:

```pgsql
people=> \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | users | table | pg4e
(1 row)

people=>
```

### Get the schema of a table

Lets suppose we have forgotten what was the schema (the definition of the table and its attributes/columns), and we want to review the schema one more time. In this case we can use the following command:

```pgsql
people=> \d+ users
                                                  Table "public.users"
 Column |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name   | character varying(128) |           |          |         | extended |             |              |
 email  | character varying(128) |           |          |         | extended |             |              |
Access method: heap

people=>
```

## Basic CRUD commands

### INSERT/create row(s) into the table

Suppose we wan to insert several rows/objects into the table. We have to use the following command:

```pgsql
people=> INSERT INTO users (name, email) VALUES ('Peter', 'peter@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('Terry', 'terry@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('Eliza', 'eliza@example.edu');
INSERT 0 1
people=> INSERT INTO users (name, email) VALUES ('John', 'john@example.edu');
INSERT 0 1
```

### READ/SELECT row(s) from a table

In order to read the rows in a table we should use `SELECT` command:

```pgsql
people=> SELECT * FROM users;
 name  |       email
-------+-------------------
 Peter | peter@example.edu
 Terry | terry@example.edu
 Eliza | eliza@example.edu
 John  | john@example.edu
(4 rows)
```

We can include some criteria in the SELECT command as well:

```pgsql
people=> SELECT * FROM users WHERE email='terry@example.edu';
 name  |       email
-------+-------------------
 Terry | terry@example.edu
(1 row)
```

### UPDATE row(s) in a table

In order to update one/more piece(s) of information in one/more row(s), we use `UPDATE` command like what follows:

```pgsql
people-> UPDATE users SET name='Johnny' WHERE email='john@example.edu';
UPDATE 1
```

**Hint: this command will update any number of rows that meet its criteria.**

Lets see how this command changed the last entry of *users* table:

```pgsql
people=> SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Terry  | terry@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
(4 rows)
```

### DELETE row(s) from a table

To remove one/more row(s) from a table, use the DELETE command like what follows:

```pgsql
people=> DELETE FROM users WHERE email='terry@example.edu';
DELETE 1
```

**Hint: this command will delete any number of rows that meet its criteria.**

Lets see how this command removed an entry of *users* table:

```pgsql
people=> SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
(3 rows)
```

## ALTER TABLE

What happens if you missed something in the time of creation of a table in database? Is it over? ABSOLUTELY NOT! SQL lets us change the table schema in the times needed.

When you use `ALTER TABLE` and change the definition of one field, a SQL-based database applies that change to both the table's schema and to all of the existing data in that table, all while the system is running and functional. So there are a couple of actions like add, change, drop a column that you can do using `ALTER TABLE` key words. Lets suppose that we have created the following table and we want to make changes to this table.

```plaintext
CREATE TABLE users(
name VARCHAR(128),
email VARCHAR(128)
);
```

First, lets take a look at the table's schema:

```pgsql
people=# \d+ users
                                                  Table "public.users"
 Column |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name   | character varying(128) |           |          |         | extended |             |              |
 email  | character varying(128) |           |          |         | extended |             |              |
Access method: heap
```

### Add a column to a table

In order to add a column with " `address VARCHAR(128)` " definition to the *users* table, we have to use the following code. You can see the changes into the table's schema

```pgsql
people=# ALTER TABLE users ADD COLUMN address VARCHAR(128);
ALTER TABLE

people=# \d+ users
                                                  Table "public.users"
 Column  |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name    | character varying(128) |           |          |         | extended |             |              |
 email   | character varying(128) |           |          |         | extended |             |              |
 address | character varying(128) |           |          |         | extended |             |              |
Access method: heap
```

### Change type of a column

To change type of *address* column from `VARCHAR(128)` to `TEXT`, we have to use to following code:

```pgsql
people=# ALTER TABLE users ALTER COLUMN address TYPE TEXT;
ALTER TABLE
people=# \d+ users
                                                  Table "public.users"
 Column  |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name    | character varying(128) |           |          |         | extended |             |              |
 email   | character varying(128) |           |          |         | extended |             |              |
 address | text                   |           |          |         | extended |             |              |
Access method: heap
```

### Drop a column of a table

In order to drop the address column of users table, we have to use the following code:

```pgsql
people=# ALTER TABLE users DROP address;
ALTER TABLE
people=# \d+ users
                                                  Table "public.users"
 Column |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 name   | character varying(128) |           |          |         | extended |             |              |
 email  | character varying(128) |           |          |         | extended |             |              |
Access method: heap
```

## UPDATE

What happens if we wanted to update content of one/several row(s)? In this case we can you `UPDATE` key word.

### Update content of row(s) in a table

First Lets take a look at the current contents of the *users* table:

```pgsql
people=# SELECT * FROM users;
  name  |       email
--------+-------------------
 Peter  | peter@example.edu
 Eliza  | eliza@example.edu
 Johnny | john@example.edu
 Terry  | terry@example.edu
(4 rows)
```

Now, lets change the email address of the user named Johnny, and watch the results:

```pgsql
people=# UPDATE users SET email='johnny@example.edu' WHERE name='johnny';
UPDATE 1
people=# SELECT * FROM users;
  name  |       email
--------+--------------------
 Peter  | peter@example.edu
 Eliza  | eliza@example.edu
 Terry  | terry@example.edu
 Johnny | johnny@example.edu
(4 rows)
```