Quick postgresql guide

quick-postgresql-guide.md

Quick PostgreSql guide

Table of content

• Install
• Creating a database
• Creating an user
• Assigning rights
• Creating a table
• Inserting data into a table
• Querying a table

Install

Start the installer and setup super account (default is postgres), for testing purpose a weak password like 1234 is ok.

Setup the port (default is 5432).

Setup the locale (Keep default).

Let the installer finish and start pqsl command line utility to test the connection.

Upon starting the psql utility you should be prompted to input parameters like below :

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Mot de passe pour l'utilisateur postgres :

Strings into [] are default parameters so pressing ENTER key on will use default parameters.

Once logged enter the following query : select version();.

If the result is like below then everything is ok.

------------------------------------------------------------
 PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
(1 ligne)

Creating a database

Now enter the following statement to create a new database : create database base_test;.

If the program returns CREATE DATABASE then the database has been created successfully.

Creating an user

In order to insert and query data on a database it is better to not use the super account. Let's create another account with the following command : create user user_test with password '1234';.

If the programs returns CREATE ROLE then the user has been created successfully.

Assigning rights

We now need to assign rights to the newly created user so the user is able to perform operation on the database. To assign rights to a user, the following command need to be played : grant all privileges on database base_test to user_test;.

If the programs returns GRANT then the user has been assigned rights successfully.

Creating a table

Quit the current psql session by entering \q. Restart the psql utility and use the new user to log in like below :

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]: user_test
Mot de passe pour l'utilisateur user_test :

In order to create a table we need to connect to the desired database with the following command : \c base_test;;

The program should output Vous êtes maintenant connecté à la base de données « base_test » en tant qu'utilisateur « user_test ». if the connection is successful.

To create the new table just enter create table if not exists table1 (field1 varchar(10), field2 int); where table1 is the table name, field1 is a field (or column) of a string type with a lenght of 10 and field2 is a field of integer type.

If the table is created successfully the program should output : CREATE TABLE.

Inserting data into a table

The statement to insert data into the newly created table is insert into table1 values ('test', 12);.

If the data have been inserted correctly the program should output something like INSERT 0 1.

Querying a table

Querying the newly inserted data is done with the select statement like this : select * from table1;.

The program should output the result like below :

 field1 | field2
--------+--------
 test   |     12
(1 ligne)