In Getting started with PostgreSQL, I defined easy methods to set up, arrange, and start utilizing the open supply database software program. But there’s much more you are able to do with instructions in PostgreSQL.
For instance, I exploit Postgres to maintain monitor of my grocery purchasing checklist. I do many of the grocery purchasing in our house, and the majority of it occurs as soon as per week. I’m going to a number of locations to purchase the issues on my checklist as a result of every retailer affords a selected choice or high quality or possibly a greater value. Initially, I made an HTML type web page to handle my purchasing checklist, however it could not save my entries. So, I had to attend to make my checklist suddenly, and by then I normally forgot some objects we want or I need.
Instead, with PostgreSQL, I can enter bits after I consider them because the week goes on and print out the entire thing proper earlier than I’m going purchasing. Here’s how you are able to do that, too.
Create a easy purchasing checklist
First, enter the database with the psql command, then create a desk in your checklist with:
Create desk groc (merchandise varchar(20), remark varchar(10));Type instructions like the next so as to add objects to your checklist:
insert into groc values ('milk', 'Ok');
insert into groc values ('bananas', 'KW');
There are two items of knowledge (separated by a comma) contained in the parentheses: the merchandise you wish to purchase and letters indicating the place you wish to purchase it and whether or not it is one thing you normally purchase each week (W).
Since psql has a historical past, you can press the Up arrow and edit the information between the parentheses as a substitute of getting to sort the entire line for every merchandise.
After getting into a handful of things, examine what you have entered with:
Select * from groc order by remark;merchandise | remark
----------------+---------
floor espresso | H
butter | Ok
chips | Ok
steak | Ok
milk | Ok
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 orders the outcomes by the remark column in order that the objects are grouped by the place you purchase them to make it simpler to buy.
By utilizing a W to point your weekly purchases, you possibly can preserve your weekly objects on the checklist once you filter the desk to arrange for the following week’s checklist. To in order that, enter:
delete from groc the place remark not like '%W';Notice that in PostgreSQL, % is the wildcard character (as a substitute of an asterisk). So, to avoid wasting typing, you may sort:
delete from groc the place merchandise like 'goat%';You cannot use merchandise = ‘goat%’; it will not work.
When you are prepared to buy, output your checklist to print it or ship it to your telephone with:
o groclist.txt
choose * from groc order by remark;
o
The final command, o, with nothing afterward, resets the output to the command line. Otherwise, all output will proceed to go to the groc file you created.
Analyze advanced tables
This item-by-item entry could also be okay for brief tables, however what about actually huge ones? A few years in the past, I used to be serving to the staff at FreieFarbe.de to create a swatchbook of the free colours (freieFarbe means “free colors” in German) from its HLC coloration palette, the place just about any conceivable print coloration will be specified by its hue, luminosity (brightness), and chroma (saturation). The consequence was the HLC Color Atlas, and here is how we did it.
The staff despatched me information with coloration specs so I might write Python scripts that may work with Scribus to generate the swatchbooks of coloration patches simply. One instance began like:
HLC, C, M, Y, Ok
H010_L15_C010, zero.5, 49.1, zero.1, 84.5
H010_L15_C020, zero.zero, 79.7, 15.1, 78.9
H010_L25_C010, 6.1, 38.three, zero.zero, 72.5
H010_L25_C020, zero.zero, 61.eight, 10.6, 67.9
H010_L25_C030, zero.zero, 79.5, 18.5, 62.7
H010_L25_C040, zero.four, 94.2, 17.three, 56.5
H010_L25_C050, zero.zero, 100.zero, 15.1, 50.6
H010_L35_C010, 6.1, 32.1, zero.zero, 61.eight
H010_L35_C020, zero.zero, 51.7, eight.four, 57.5
H010_L35_C030, zero.zero, 68.5, 17.1, 52.5
H010_L35_C040, zero.zero, 81.2, 22.zero, 46.2
H010_L35_C050, zero.zero, 91.9, 20.four, 39.three
H010_L35_C060, zero.1, 100.zero, 17.three, 31.5
H010_L45_C010, four.three, 27.four, zero.1, 51.three
This is barely modified from the unique, which separated the information with tabs. I reworked it right into a CSV (comma-separated worth) file, which I choose to make use of with Python. (CSV information are additionally very helpful as a result of they are often imported simply right into a spreadsheet program.)
In every line, the primary merchandise is the colour title, and it is adopted by its C, M, Y, and Ok coloration values. The file consisted of 1,793 colours, and I needed a approach to analyze the knowledge to get a way of the vary of values. This is the place PostgreSQL comes into play. I didn’t wish to enter all of this information manually—I do not suppose I might with out errors (and complications). Fortunately, PostgreSQL has a command for this.
My first step was to create the database with:
Create desk hlc_cmyk (coloration varchar(40), c decimal, m decimal, y decimal, okay decimal);Then I introduced within the information with:
copy hlc_cmyk from '/house/gregp/HLC_Atlas_CMYK_SampleData.csv' with (header, format CSV);The backslash in the beginning is there as a result of utilizing the plain copy command is restricted to root and the Postgres superuser. In the parentheses, header means the primary line accommodates headings and may be ignored, and CSV means the file format is CSV. Note that parentheses are usually not required across the coloration title on this technique.
If the operation is profitable, I see a message that claims COPY NNNN, the place the N’s check with the variety of rows inserted into the desk.
Finally, I can question the desk with:
choose * from hlc_cmyk;coloration | c | m | y | okay
---------------+-------+-------+-------+------
H010_L15_C010 | zero.5 | 49.1 | zero.1 | 84.5
H010_L15_C020 | zero.zero | 79.7 | 15.1 | 78.9
H010_L25_C010 | 6.1 | 38.three | zero.zero | 72.5
H010_L25_C020 | zero.zero | 61.eight | 10.6 | 67.9
H010_L25_C030 | zero.zero | 79.5 | 18.5 | 62.7
H010_L25_C040 | zero.four | 94.2 | 17.three | 56.5
H010_L25_C050 | zero.zero | 100.zero | 15.1 | 50.6
H010_L35_C010 | 6.1 | 32.1 | zero.zero | 61.eight
H010_L35_C020 | zero.zero | 51.7 | eight.four | 57.5
H010_L35_C030 | zero.zero | 68.5 | 17.1 | 52.5
It goes on like this for all 1,793 rows of knowledge. In retrospect, I am unable to say that this question was completely vital for the HLC and Scribus process, however it allayed a few of my anxieties in regards to the undertaking.
To generate the HLC Color Atlas, I automated creating the colour charts with Scribus for the 13,000+ colours in these pages of coloration swatches.
I might have used the copy command to output my information:
copy hlc_cmyk to '/house/gregp/hlc_cmyk_backup.csv' with (header, format CSV);I additionally might prohibit the output in line with sure values with a the place clause.
For instance, the next command will solely ship the desk values for the hues that start with H10.
copy hlc_cmyk to '/house/gregp/hlc_cmyk_backup.csv' with (header, format CSV) the place coloration like 'H10%';Back up or switch a database or desk
The remaining command I’ll point out right here is pg_dump, which is used to again up a PostgreSQL database and runs outdoors of the psql console. For instance:
pg_dump gregp -t hlc_cmyk > hlc.out
pg_dump gregp > dball.out
The first line exports the hlc_cmyk desk together with its construction. The second line dumps all of the tables contained in the gregp database. This could be very helpful for backing up or transferring a database or tables.
To switch a database or desk to a different pc, first, create a database on the opposite pc (see my “getting started” article for particulars), then do the reverse course of:
psql -d gregp -f dball.outThis creates all of the tables and enters the information in a single step.
Conclusion
In this text, we now have seen easy methods to use the WHERE parameter to limit operations, together with using the PostgreSQL wildcard character %. We’ve additionally seen easy methods to load a considerable amount of information right into a desk, then output some or all the desk information to a file, and even your complete database with all its particular person tables.
