Getting Started With PostgreSQL
There are numerous different types of databases out there and they are written in many languages. Relational Databases are adept at storing data in tables with rigid schemas that are related to one another through a common column, like an id that relates to the information for only one record. A rigid schema means that every record must have the same information and every column can only contain the same specified data type. Non Relational Databases, like MongoDB, are perfect for storing data that does not have a rigid schema or defined shape, like a never-ending chat log.
Once you decide which type of database you need you can then move on to selecting a database management system. PostgreSQL is a system that utilizes the Structured Query Language (SQL). It is popular because it is open-source and can be adapted for numerous purposes. You can interact with a PostgreSQL using a number of paid and free Graphical User Interfaces (GUI) or the command line and terminal.
PostgreSQL Setup
Installing and initializing PostgreSQL is straightforward. I’m on a Mac, the instructions for installing on windows can be found here. I followed the steps outlined by Nelson from freeCodeCamp.org in his video here. The Mac steps are:
- Go to postgresapp.com
- Navigate to the download page and select one of the options. I selected the second option (under the “Additional Releases” section) because it includes the current version as well as the last few previous versions. At this time PostgreSQL version 10, 11 and 12 come with this package. The “Latest Release” section only includes the ability to work with PostgreSQL 12 at the time of this writing. Once the file is downloaded and in your applications you can open the program.
- In the application window you can click the sidebar icon to view the different available databases by server location and you can add (+) or delete (-) listed servers as well.
- Once you click initialize you will have the option to start and stop the PostgreSQL application. The three default databases on your local machine are listed in the window; one with your username, “postgres,” and “template1”
You can choose to interact with a Postgres database with either a GUI or the command line/terminal. GUIs range in price and available features, however when you are working with remote servers chances are you will be using terminal (Mac) or command line (Windows).
If you click on any of the listed databases then a terminal window will open up and indicate that the command Applications/Postgres.app/Contents/Versions/12/bin/psql -p5432 "database_name" was entered. In future I will refer to this command, without the "database_name" included, as psql,
If you need to quit the shell you can type \q, you can re-enter the shell by typing the long command above or a designated shortcut.
When you are working with terminal you have to know whether you are in your base environment computer_name:~ user_name$ or the Postgres shell default_database=#. The Postgres App (above) must be running to enter the shell.
There is a help menu for PostgreSQL available in the base terminal and the PSQL shell. The terminal help menu can be accessed by typing:
Applications/Postgres.app/Contents/Versions/12/bin/psql -p5432 "database_name" --help
The PSQL shell help menu can be accessed by typing \h. The shell looks like:
There are useful commands listed in the psql commands section which can be accessed with \?. For instance \l will display all tables and \c + database_name will allow you to switch to another database within the server.
Connecting To A Database
With the Postgres App and the base terminal open you have the ability to access remote or local server data, if you have the proper permissions. The four parts to this command are -h for host, -p for port, -U for username, and -d for database name. The format to enter a server is psql -h localhost -p 5432 -U user_name -d database_name, where localhost (your computer) can be replaced with an IP address and port 5432 is the default Postgres port. Once you have accessed a database within a server you can utilize PostgreSQL commands.
Conclusion
Once you get Postgres up and running you will be able to interact with databases utilizing both SQL commands and specific PSQL commands. Postgres is powerful and open-source which is why it is popular. There are third party GUIs available to make PSQL easier for users however they are not always able to function with remote servers which is why terminal and command line are used.
