# Postgresql ## What is Postgresql? PostgreSQL is a free and open-source relational database management system. Using a database instead of regular spreadsheet will allow us to store a larger amount of data as well as offer a much higher capacity to later process our data. PostgreSQL databases run locally and therefore each of us will need to install and create our own PostgreSQL database. ## How to install Postgresql? ### INSTALLATION FOR LINUX [Installation for Linux](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04) ``` sudo apt update sudo apt install postgresql postgresql-contrib ``` **Create new role** ``` sudo -u postgres createuser --interactive ``` **Create new database** ``` sudo -u postgres createdb [database_name] ``` **Connect to PSQL with username** ``` sudo -u [user_name] psql ``` **Connect to another database** ``` psql -d [database_name] ``` ### INSTALLATION FOR WINDOWS - Download the latest Windows [PostgreSQL Installer](http://www.bigsql.org/postgresql/installers.jsp) and run the installer. Remember where you install postgres. - When asked for a port number, use 5432. - After the wizard has finised type `psql -U postgres` in the terminal - If the psql command failed to execute, add `postgres` to `Path` in window. _Adding environment variable to `PATH` in window:_ - Go to `Control Panel` - Search for `Edit the system environment variables` - Go to `Advance` tab - Choose `Environment Variable` - In `System variables`, select `Path` then click `Edit` - When a new table pop up, choose `New`. Paste the PostgreSQL installation path to Postgres bin. For example, I install Postgres in `D:`, so my path look like this: ![](https://i.imgur.com/J3IwzA4.png) - Try `psql -U posgres` in your terminal again. It should run flawlessly. ### Postgres command on window: Note: Don't confuse these command with PostgresSQL command. Postgres command allow you manage database like create new database, connect directly your local machine to your database and config users. PostgresSQL command let you manage your database using SQL language like create new table and insert new data. However we don't . If you don't use window, please refer to the section Linux above. __Create new user__ ``` createuser.exe --createdb --username postgres --no-createrole --pwprompt [user_name] ``` `--createdb` : the new user will be able to create new databases `--username postgres` : createuser will use the postgres user (superuser) `--no-createrole` : the new user will not be able to create new users `--pwprompt` : createuser will ask you the new user’s password __Enter PSQL shell__ ``` psql -U [user_name] [database_name] ``` The default database is `postgres`, so if you don't have any database available, use `postgre`. From the PostgresSQL shell you can directly enter SQL or PostgreSQL commands to interact with your database. ## PostgreSQL Commands **Create new Database** ``` CREATE DATABASE dbname; ``` **Check connection information** ```sql \conninfo ``` **View all tables** ```sql \dt ``` **Create Table** ```sql CREATE TABLE table_name ( column_name1 col_type (field_length) column_constraints, column_name2 col_type (field_length), column_name3 col_type (field_length) ); ``` **Alter Table** ```sql ALTER TABLE table_name ACTION...; ```