Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to use the PostgreSQL command

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article introduces you how to use the PostgreSQL command, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Create a simple shopping list

First, enter the database by entering the psql command, and then create a table with the following command:

Create table groc (item varchar (20), comment varchar (10))

Enter the following command to add items to the list:

Insert into groc values ('milk',' K'); insert into groc values ('bananas',' KW')

There are two messages in parentheses (separated by commas): the front is what you need to buy, and the letters after that indicate where you want to buy and what you usually buy every week (W).

Because psql has a history, you can press the up key to edit the information in parentheses without having to enter the entire line of information about the item.

After entering a small number of items, enter the following command to check the previous input.

Select * from groc order by comment Item | comment-+- ground coffee | H butter | K chips | K steak | K milk | K bananas | KW raisin bran | KW raclette | L goat cheese | L onion | P oranges | P potatoes | P spinach | PW broccoli | PW Asparagus | PW cucumber | PW sugarsnap peas | PW salmon | S (18 rows)

This command sorts the results by the comment column so that items are grouped by place of purchase, making your shopping more convenient.

Use W to indicate what you want to buy each week, and when you clear the form to prepare for next week's list, you can keep the weekly items on the shopping list. Enter:

Delete from groc where comment not like'% W'

Note that in PostgreSQL,% represents a wildcard (not an asterisk). So, to save the input, you need to enter:

Delete from groc where item like 'goat%'

You can't use item = 'goat%',. It's useless.

When shopping, output the list and print or send it to your phone with the following command:

\ o groclist.txtselect * from groc order by comment;\ o

The last command\ o is followed by nothing and will be reset to the command line. Otherwise, all the output will continue to be output to the grocery store shopping file you created, groclist.txt.

Analyze complex tables

This itemized list is fine for tables with small amounts of data, but what about tables with large amounts of data? A few years ago, I helped FreieFarbe.de 's team create a free-color swatch book from the HLC palette. In fact, any imaginable printing color can be specified by hue, brightness, and concentration (saturation). The end result is HLC Color Atlas, and here's how we did it.

The team sent me files with color specifications, so I could write Python scripts that I could use with Scribus to easily generate color sample books. An example starts like this:

HLC, C, M, Y, KH010_L15_C010, 0.5, 49.1, 0.1, 84.5H010_L15_C020, 0.0, 79.7, 15.1, 78.9H010_L25_C010, 6.1, 38.3, 0.0, 72.5H010_L25_C020, 0.0, 61.8, 10.6, 67.9H010_L25_C030, 0.0, 79.5, 18.5, 62.7H010_L25_C040, 0.4, 94.2, 17.3 56.5H010_L25_C050, 0.0, 100.0, 15.1, 50.6H010_L35_C010, 6.1, 32.1, 0.0, 61.8H010_L35_C020, 0.0, 51.7, 8.4, 57.5H010_L35_C030, 0.0, 68.5, 17.1, 52.5H010_L35_C040, 0.0, 81.2, 22.0, 46.2H010_L35_C050, 0.0, 91.9, 20.4, 39.3H010_L35_C060, 0.1 100.0, 17.3, 31.5H010_L45_C010, 4.3, 27.4, 0.1, 51.3

This is slightly modified compared to the original data, which is separated by tabs. I convert it to CSV format (splitting values with commas), and I prefer to use it with Python (CSV text is also useful because it can be easily imported into spreadsheet programs).

In each line, the first item is the color name, followed by its C, M, Y, and K color values. This file contains 1793 colors, and I want a way to analyze the information to understand the range of these values. This is where PostgreSQL plays a role. I don't want to enter all the data manually-I don't think I can do it without mistakes, and it's a headache. Fortunately, PostgreSQL provides a command for this.

First create the database with the following command:

Create table hlc_cmyk (color varchar (40), c decimal, m decimal, y decimal, k decimal)

Then bring in the data with the following command:

\ copy hlc_cmyk from'/ home/gregp/HLC_Atlas_CMYK_SampleData.csv' with (header, format CSV)

It begins with a backslash because the permissions to use the pure copy command are limited to root users and Postgres superusers. In parentheses, header indicates that the first line contains the title, which should be ignored, and CSV indicates that the file format is CSV. Note that in this method, the color name does not need to be enclosed in parentheses.

If the operation is successful, you will see COPY NNNN, where N represents the number of rows inserted into the table.

Finally, you can query with the following command:

Select * from hlc_cmyk Color | c | m | y | k-+-H010_L15_C010 | 0. 5 | 49. 1 | 0. 1 | 84. 5 H010_L15_C020 | 0. 0. 7 | 15. 1 | 78. 9 H010_L25_C010 | 6 . 1 | 38.3 | 0.0 | 72.5 H010_L25_C020 | 0.0 | 61.8 | 10.6 | 67.9 H010_L25_C030 | 0.0 | 79.5 | 18.5 | 62.7 H010_L25_C040 | 0.4 | 94.2 | 17.3 | 56.5 H010_L25_C050 | 0.0 | 100.0 | 15.1 | 50.6 H010_L35_C010 | 6.1 | 32.1 | 0 | .0 | 61.8 H010_L35_C020 | 0.0 | 51.7 | 8.4 | 57.5 H010_L35_C030 | 0.0 | 68.5 | 17.1 | 52.5 |

All 1793 rows of data are like this. In retrospect, I can't say that this query was absolutely necessary for HLC and Scribus tasks, but it allayed some of my concerns about the project.

To generate the HLC color spectrum, I used Scribus to automatically create a color chart for more than 13000 colors in the swatch page.

I can use the copy command to output data:

\ copy hlc_cmyk to'/ home/gregp/hlc_cmyk_backup.csv' with (header, format CSV)

I can also use the where clause to limit output based on certain values.

For example, the following command will send only tonal values that begin with H10.

\ copy hlc_cmyk to'/ home/gregp/hlc_cmyk_backup.csv' with (header, format CSV) where color like 'H10% backup or transfer database or table

The last command I want to mention here is pg_dump, which is used to back up the PostgreSQL database and run outside the psql console. For example:

Pg_dump gregp-t hlc_cmyk > hlc.outpg_dump gregp > dball.out

The first row is to export the hlc_cmyk table and its structure. The second line dumps all tables in the gregp database. This is useful for backing up or transferring databases or tables.

To transfer a database or table to another computer (see the article getting started with PostgreSQL for more information), first create a database on the computer you want to transfer, and then do the opposite.

Psql-d gregp-f dball.out on how to use the PostgreSQL command to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report