Shulou(Shulou.com)05/31 Report--

This article shows you how to get started with PostgreSQL database, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Install, set up, create, and start using PostgreSQL databases.

Everyone may have something to save in the database. Even if you insist on using paper or electronic documents, they can become troublesome. Paper documents may be lost or confused, and the electronic information you need to access may be hidden in the depths of paragraphs and pages.

When I was working in medicine, I used PostgreSQL to track my inpatient list and submit information about inpatients. I print my daily patient list in my pocket so that I can quickly learn about any changes in the patient's room, diagnosis, or other details and make quick notes.

I thought everything was all right until last year my wife decided to buy a new car and I took over her old car. She kept a folder of car repair and maintenance service receipts, but as time went by, it became messy. Instead of taking the time to sift through all the notes to figure out when and what was done, I think PostgreSQL would be a better way to track 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 didn't even install it on my computer. Installing it is the first step. I use Fedora, so I run it in the console:

Dnf list postgresql*

Please note that you do not need to use sudo to use the list option. This command returns a long list of packages. After looking at it, I decided that I only needed three: postgresql, postgresql-server and postgresql-docs.

To understand what needs to be done next, I decided to look at the PostgreSQL documentation. The reference content of the documentation is very rich, in fact, it is daunting. Fortunately, I found some notes I made when upgrading Fedora, hoping to effectively export the database, restart PostgreSQL on the new version, and import the old database.

Set up PostgreSQL

Unlike most other software, you can't just install PostgreSQL and start using it. You must perform two basic steps in advance: first, you need to set up PostgreSQL, and second, you need to start it. You must perform these operations as the root user (sudo does not work here).

To set it, enter:

Postgresql-setup-initdb

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

Systemctl start postgresql.servicesystemctl enable postgresql.service

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

Create a user

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

Su postgres

Since you are doing this as root, you do not need to enter a password. Root users can operate as any user without knowing the password; this is 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 a user gregp) as follows:

Createuser gregpcreatedb gregp

You may see an error message, such as Could not switch to / home/gregp. This simply means that user postgres cannot access the directory. Nevertheless, your users and databases have been created. Next, type exit and press enter twice to return to the original user (root).

Set up the database

To start using PostgreSQL, enter 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 that it understands. You automatically get a database (my name is gregp) that has no content at all. For PostgreSQL, the database is just a workspace. In space, you can create tables. The 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 (6d2))

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

The first variable is named date, and its data type is also date, which doesn't matter in PostgreSQL. The second and third variables repairs and location are of type varchar (80), which means they can be up to 80 arbitrary characters (letters, numbers, etc.). The last variable, cost, uses the numeric type. The numbers in parentheses indicate a maximum of six digits, two of which are decimal places. Initially, I tried the real type, which will be a floating point number. The problem with the real type is that when used as a data type, it encounters a WHERE clause, similar to WHERE cost = 0 or any other specific number. Because the real value is somewhat imprecise, specific numbers will never match.

Input data

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

INSERT INTO autorepairs VALUES ('2017-08-11,' airbag recall', 'dealer', 0)

Note that parentheses make up a container of values, which must be separated by commas in the correct order, with a semicolon at the end of the command. Values of types date and varchar (80) must be enclosed in single quotation marks, but numeric values such as numeric are not used. As feedback, you should see:

INSERT 0 1

Like a regular terminal session, you have a history of entering commands, so when entering subsequent lines, you can usually press the up arrow key to display the last command and edit the data as needed, saving a lot of time.

What if something goes wrong? Use UPDATE to change the value:

UPDATE autorepairs SET date = '2017-11-08' WHERE repairs = 'airbag recall'

Or maybe you no longer need the rows in the table. Use DELETE:

DELETE FROM autorepairs WHERE repairs = 'airbag recall'

This deletes the entire row.

One last thing: even if I always use uppercase letters in the PostgreSQL command (as I do in most documents), you can type in lowercase letters, as I often do.

Output data

If you want to present the data, use SELECT:

SELECT * FROM autorepairs ORDER BY date

Without ORDER BY, the line will be displayed regardless of what you type. For example, here is the output of my car service data from my terminal:

SELECT date, repairs FROM autorepairs ORDER BY date Date | repairs-+-2008-08-08 | oil change, air filter Spark plugs2011-09-30 | 35000 service, oil change, rotate tires/balance wheels2012-03-07 | repl battery2012-11-14 | 45000 maint, oil/filter2014-04-09 | 55000 maint, oil/filter, spark plugs, air/dust filters2014-04-21 | replace 4 tires2014-04-21 | wheel alignment2016-06-01 | 65000 mile service, oil change2017-05-16 | oil change, replce oil filt housing2017-05-26 | rotate tires2017-06-05 | air filter, cabin filter,spark plugs2017-06-05 | brake pads and rotors Flush brakes2017-08-11 | airbag recall2018-07-06 | oil/filter change, fuel filter, battery svc2018-07-06 | transmission fl, p steering fl, rear diff fl2019-07-22 | oil & filter change, brake fluid flush, front differential flush2019-08-20 | replace 4 tires2019-10-09 | replace passenger taillight bulb2019-10-25 | replace passenger taillight assembly (19 rows)

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

\ o autorepairs.txt

Then run the SELECT command again.

Exit PostgreSQL

Finally, exit PostgreSQL from the terminal and type:

Quit

Or an abbreviated version of it:

\ Q

Although this is only a brief introduction to PostgreSQL, I hope it shows that using a database for such a simple task is neither difficult nor time-consuming.

The above content is how to get started with PostgreSQL database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.