Introduction to PostgreSQL

Install, set up, create, and start using the PostgreSQL database.

Everyone probably has something that needs to be saved in a database. Even if you cling to paper or electronic documents, they can become cumbersome. Paper documents can be lost or cluttered, and the electronic information you need to access can be hidden deep in paragraphs and pages.

In my medical career, I used PostgreSQL to track my inpatient list and submit information about inpatients. I print out my daily patient list in my pocket to quickly see and record any changes in patient rooms, diagnoses, or other details.

I thought everything was fine until last year when my wife decided to buy a new car and I “took over” her old one. She kept a folder of receipts for car repair and maintenance services, but over time it became cluttered. Instead of spending time sifting through all the slips to figure out what was done when, I think PostgreSQL would be a better way to keep track of this information.

Install PostgreSQL

It’s been a while since I last used PostgreSQL, and I’ve forgotten how to use it. In fact, I don’t even have it installed on my computer. Installing it is the first step. I use Fedora, so I’m running on the console:

dnf list postgresql*
Copy the code

Note that you don’t need to use sudo to use the List option. This command returns a long list of packages. After looking around, I decided I only needed three: PostgresQL, PostgresqL-Server, and Postgresql-docs.

To see what I need to do next, I decided to look at the PostgreSQL documentation. The documentation references are rich, in fact, daunting. Fortunately, I found some notes I had taken when upgrading Fedora, hoping to export the database efficiently, restart PostgreSQL on the new version, and import the old database.

Set the PostgreSQL

Unlike most other software, you can’t just install PostgreSQL and start using it. You must perform two basic steps beforehand: first, you need to set up PostgreSQL, and second, you need to start it. You must perform these operations as root (sudo doesn’t work here).

To set it up, type:

Postgresql - setup - initdbCopy the code

This will determine the location of the PostgreSQL database on the computer. Then (again root) type the following two commands:

systemctl start postgresql.service
systemctl enable postgresql.service
Copy the code

The first command starts PostgreSQL for the current session (if you shut down the machine, PostgreSQL will also be shut down). The second command causes PostgreSQL to start automatically on subsequent restarts.

Create a user

PostgreSQL is running, but you still can’t use it because you don’t have users yet. To do this, you need to switch to the special user Postgres. While you are still running as root, type:

su postgres
Copy the code

Since you are doing this as root, you do not need to enter a password. The root user can operate as any user without knowing the password; That’s one of the things that makes it powerful and dangerous.

Now that you are Postgres, run the following two commands to create a user (create user gregp) as follows:

createuser gregp
createdb gregp
Copy the code

You might see error messages such as: Could not switch to /home/gregp. This simply means that user Postgres cannot access the directory. Nevertheless, your user and database have been created. Next, type exit and press Enter twice to return to the original user (root).

Setting up the database

To get started with PostgreSQL, type PSQL on the command line. You should see something like gregp=> on the left side of each line to show that you are using PostgreSQL and can only use commands it understands. You automatically get a database (my name is GreGP) that has absolutely no content in it. To PostgreSQL, a database is just a workspace. In space, you can create tables. A table contains a list of variables, and each variable in the table is the data that makes up the database.

Here’s how I set up the car service database:

CREATE TABLE autorepairs (date date, repairs varchar(80), location varchar(80), cost numeric(6,2));Copy the code

I could have typed it in one line, but to better illustrate the structure and show that PostgreSQL doesn’t interpret TAB characters and newline whitespace, I split it into multiple lines. Fields are enclosed in parentheses, each variable name and data type is separated from the next variable by a comma (except for the last one), and commands end with a semicolon. All commands must end with a semicolon!

The first variable name is date, and its data type is date, which doesn’t matter in PostgreSQL. The second and third variables, Repairs and Location, are both of type VARCHar (80), which means they can be up to 80 arbitrary characters (letters, numbers, and so on). The last variable cost uses the numeric type. The numbers in brackets indicate a maximum of six digits, two of which are decimals. Initially, I tried the real type, which would be a floating point number. The problem with real is that when used as a data type, it encounters a WHERE clause, like WHERE cost = 0 or any other specific number. Because the real value is somewhat imprecise, specific numbers will never match.

The input data

Next, you can add some data (called rows in PostgreSQL) using the INSERT INTO command:

INSERT INTO autorepairs VALUES ('2017-08-11'.'airbag recall'.'dealer', 0);
Copy the code

Note that the parentheses form a container of values that must be in the correct order, separated by commas, and followed by a semicolon at the end of the command. Values of type date and vARCHar (80) must be enclosed in single quotes, but numeric values (such as numeric) are not. As feedback, you should see:

INSERT 0 1
Copy the code

As with regular terminal sessions, you have a history of the commands you typed, so you can usually save a lot of time by pressing the up arrow key to display the last command and editing the data as needed as you type subsequent lines.

What if something goes wrong? Use UPDATE to change values:

UPDATE autorepairs SET date = '2017-11-08' WHERE repairs = 'airbag recall';
Copy the code

Or, maybe you don’t need rows in a table anymore. Use the DELETE:

DELETE FROM autorepairs WHERE repairs = 'airbag recall';
Copy the code

This will delete the entire line.

One last thing: even though I always use uppercase letters in PostgreSQL commands (and do so in most documents), you can type in lowercase, which I often do.

The output data

If you want to display data, use SELECT:

SELECT * FROM autorepairs ORDER BY date;
Copy the code

Without ORDER BY, the line will display whatever you type. For example, here is my car service data output from my terminal:

SELECT date, repairs FROM autorepairs ORDER BY date; date | repairs -----------+----------------------------------------------------------------- 2008-08-08 | oil change, air filter, spark plugs 2011-09-30 | 35000 service, oil change, rotate tires/balance wheels 2012-03-07 | repl battery 2012-11-14 | 45000 maint, oil/filter 2014-04-09 | 55000 maint, oil/filter, spark plugs, air/dust filters 2014-04-21 | replace 4 tires 2014-04-21 | wheel alignment 2016-06-01 | 65000 mile service, oil change 2017-05-16 | oil change, replce oil filt housing 2017-05-26 | rotate tires 2017-06-05 | air filter, cabin filter,spark plugs 2017-06-05 | brake pads and rotors, flush brakes 2017-08-11 | airbag recall 2018-07-06 | oil/filter change, fuel filter, battery svc 2018-07-06 | transmission fl, p steering fl, rear diff fl 2019-07-22 | oil & filter change, brake fluid flush, front differential flush 2019-08-20 | replace 4 tires 2019-10-09 | replace passenger taillight bulb 2019-10-25 | replace  passenger taillight assembly (19 rows)Copy the code

To send this to a file, change the output to:

\o autorepairs.txt
Copy the code

Then run the SELECT command again.

Exit the PostgreSQL

Finally, exit PostgreSQL from the terminal and type:

quit
Copy the code

Or an abbreviated version of it:

\q
Copy the code

While this is just a brief introduction to PostgreSQL, I hope it shows that using databases for such simple tasks is neither difficult nor time-consuming.

---

Via: opensource.com/article/19/…

By Greg Pittman (lujun9972

This article is originally compiled by LCTT and released in Linux China