Play with psql - Create Table and Insert Data (for Beginners)
Tags: beginners, postgres, database
Published: 2023-06-25 09:57:48

# Intro
Hello guys, today I try to write about psql again! In this article, we will play with psql to create tables and insert data.

I have written about psql setups, creating users and databases in a [previous post](https://dev.to/koshirok096/how-to-start-using-psql-in-terminal-for-beginners-2ok3), so please refer to that post if you need more information.

# Preparation
Before creating a Table, we need to create a database and set user permissions etc. So let's go into them step by step :D

## 1. Create Database
First of all, let's login to a superuser "postgres". And then run `CREATE DATABASE <yourdatabasename>` to create a database which we would play with.

This time I run `CREATE DATABASE "Apple Records"`, in order to create database called "Apple Records".　When you are done, run `\l` to see the list of databases.
```sql
                               List of databases
     Name      |  Owner   | Encoding | Collate | Ctype |   Access privileges   
---------------+----------+----------+---------+-------+-----------------------
 Apple Records | postgres | UTF8     | C       | C     | 
 postgres      | postgres | UTF8     | C       | C     | 
 template0     | postgres | UTF8     | C       | C     | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
 template1     | postgres | UTF8     | C       | C     | =c/postgres          +
               |          |          |         |       | postgres=CTc/postgres
```

Now you see, I have a database called "Apple Records" in the list!

### Tip: Spacing
If you want to use space in the name, use "" to enclose. Like, `CREATE DATABASE "Apple Records";`

### Tip: What is template1?
In PostgreSQL, template1 is a predefined template database that serves as a **blueprint** for creating new databases. When a new database is created, it is based on the template database specified, and template1 is often used as the default template.

In this article I won't go into depth. But if you are interested, you can check it out for yourself.

## 2. Grant Database Permissions to User
Next, grant this Database the necessary permissions to the user koshirok (this is my username, but you use your username you created). If you don't have a user yet, please refer to the previous article and create new one.

To grant permissions, run the code below:

```sql
GRANT ALL PRIVILEGES ON DATABASE "Apple Records" TO <username>;
```

In my case I already have a user called "koshirok", so I run `GRANT ALL PRIVILEGES ON DATABASE "Apple Records" TO koshirok;`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m9lpp2gw60jx2t2gz0xy.png)

## 3. Connect to Database with User

Next, we change the user from superuser "postgres" to new user. Use `\c` (c means connect) command like below;

```sql
\c <databasename> <username>
```

Or, you can also run \q to quite and run; 

```sql
psql -U <username> -d <databasename>
```
from out of psql. Both means same.

If it works, then you connected your database from the permitted user that you created. You are ready to start create table, finally!

### Tip: Schema Error
After this section, you will create a table. In my case, I got an error "ERROR: permission denied for schema public" when I run CREATE TABLE command.

Some cases, user can fix this problem by run this command: `GRANT CREATE ON SCHEMA public TO <username>;` from superuser "postgres", but I couldn't fix it by that (I got a message `WARNING:  no privileges were granted for "public"`, and getting same error).

I am sorry to say this, but I don't know yet what was the reason why - but at least I found the solution by create new schema like this:

```sql
CREATE SCHEMA <schemaname>;
```
and

```sql
GRANT USAGE, CREATE ON SCHEMA <schemaname> TO <username>;
```

After this, I could create a table with no error. So if you get a same error at next step, then consider to try this commands.

# Create Table
Let's create your table!

In this article, I would create the table called **Beatles**, which has the all band members with personal info. But before then, let's create a table and base.

```sql
CREATE TABLE Beatles ( id SERIAL PRIMARY KEY, name VARCHAR(100), height DECIMAL, weight DECIMAL, instrument VARCHAR(100), birth_place VARCHAR(100), active_years VARCHAR(100) );
```

**SERIAL PRIMARY KEY:**
SERIAL is a data type for automatically generating sequential numbers. It is typically used for primary key columns.
PRIMARY KEY is a constraint that defines a column as a primary key and it is an unique and uniquely identifies the record using the values in that column.

**VARCHAR(100):**
VARCHAR is a variable-length string data type. It can store strings up to a specified length. (100) is a parameter that specifies the maximum length of a VARCHAR column. In this case, up to 100 characters can be stored.

**DECIMAL:**
DECIMAL is a data type that represents a fixed-precision floating-point number. You can specify the precision of the decimal point with a fixed number of digits.

Now the table is created! But it has no band member yet. So let's `INSERT` data into it.

```sql
INSERT INTO Beatles (name, height, weight, instrument, birth_place, active_years)
VALUES ('John Lennon', 179, 70, 'Vocals, Guitar', 'Liverpool, England', '1960-1970'),
       ('Paul McCartney', 180, 68, 'Vocals, Bass', 'Liverpool, England', '1960-1970'),
       ('George Harrison', 178, 68, 'Vocals, Guitar', 'Liverpool, England', '1960-1970'),
       ('Ringo Starr', 173, 65, 'Vocals, Drums', 'Liverpool, England', '1962-1970');
```

All set! Let's run `SELECT * FROM Beatles;` to see contents of table.

```sql
 id |      name       | height | weight |   instrument   |    birth_place     | active_years 
----+-----------------+--------+--------+----------------+--------------------+--------------
  1 | John Lennon     |    179 |     70 | Vocals, Guitar | Liverpool, England | 1960-1970
  2 | Paul McCartney  |    180 |     68 | Vocals, Bass   | Liverpool, England | 1960-1970
  3 | George Harrison |    178 |     68 | Vocals, Guitar | Liverpool, England | 1960-1970
  4 | Ringo Starr     |    173 |     65 | Vocals, Drums  | Liverpool, England | 1962-1970
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3knzw5nb3xvxvbrr9hlc.png)

# Outro
I wanted to write about deletion and edit table, but I hope I can do it another time. Thank you for reading, happy coding :)