Play with psql - Create Table and Insert Data (for Beginners)

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, 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.

                               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:

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;

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;

\c <databasename> <username>

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

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:

CREATE SCHEMA <schemaname>;

and

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.

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.

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.

 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

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 :)