PostgreSQL—the Linux under the Databases
Time and date values are handled in a very flexible manner. I will demonstrate this on a database that I use for tracking my telephone costs for connecting to my Internet provider. The structure of my database and a sample query look like this:
create table telephone ( datum abstime, online_secs int4, units int2, costs float8 ); select * from pppcosts where datum >= 'yesterday'::abstime; datum |online_secs|units|cost Tue Jul 08 00:16:22 1997 MET DST| 486| 3|0.36 Tue Jul 08 20:18:52 1997 MET DST| 1476| 10| 1.2 Wed Jul 09 01:06:33 1997 MET DST| 3317| 14|1.68
This query returns my on-line events from yesterday at 00:00:00 until now. The term 'yesterday'::abstime is an explicit type conversion that makes sure the parser gets the right type for literal values. In this case it is not necessary, but sometimes the input type might be guessed wrong and an error returned. Explicit notation avoids such errors.
It is also possible to get aggregates of data. From my telephone costs table, I can get the costs for the last 30 days by giving the following query:
select sum(online_secs) as seconds, sum(units) as units, sum(costs) as costs where datum > ('now'::abstime - '30 days'::reltime);
The keyword as sets the column titles to the specified values; otherwise, they would all read sum.
Also supported is the SQL date type which accepts dates in the form mm/dd/yyyy. There is even a mechanism to change the date format for foreign languages (currently only European and US formats are supported). Use the set command as follows:
set datestyle to 'european'; set datestyle to 'us';
The date format is changed to dd/mm/yyyy and then back to mm/dd/yyyy. In v6.1 a comma had to follow the keywords “european” and “us”. In later releases this bug has been fixed.
Another nifty feature is the sequence command. A frequent demand to a database is that some column contains a sequence of numbers which is automatically increased when new records are added. To do this, create sequences by giving the following statements:
create sequence id start 1000 increment 10; insert into table values (nextval(id),...);
This example will create a sequence, with the name ID, which starts at 1000 and increases by 10 every time the function nextval() returns a unique number.
The next example shows how to implement a user-defined function. This technique can be used to overcome a limitation of PostgreSQL—the database system is not able to process subqueries. Querys that contain another query, usually in a where clause, are called subquerys. PostgreSQL supports them only indirectly, i.e., when the subquery is hidden in a function.
-- I will use the cities table again create function big_city() returns set of cities as 'select * from cities* where population > 700000' language 'sql'; select name(big_city()) as highpop;
In the example, the function returns a tuple from the cities table. It is also possible to return only single values by writing e.g., returns int4. The language command in the example tells the parser that this is a query-language function. With programming-language functions (e.g., language 'c'), you can program your own functions in C and load them as dynamic modules directly into the back end. This mechanism is also used to create user-defined types. (Examples can be found in the tutorial directory of the source tree.) A function is permanently stored in the same way as a table. So, when it is no longer needed, it must be explicitly deleted by giving the drop function command.
I have presented only some highlights of the SQL language here, with an emphasis on special features of PostgreSQL. I also omitted examples for joining data from different tables, for creating views and for enclosing your operation in transaction blocks to ensure that either all actions are performed or none at all. I also omitted mentioning cursors, which allow you to limit the amount of tuples returned from a query. These and other features can be learned best from a book about the SQL query language. I have used the book A Visual Introduction to SQL from J.H. Trimble, D. Chappel and others, published by Wiley in 1989 for my learning. It is aimed at novices and easy to understand.
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
|Non-Linux FOSS: Seashore||May 10, 2013|
|Trying to Tame the Tablet||May 08, 2013|
|Dart: a New Web Programming Experience||May 07, 2013|
- RSS Feeds
- New Products
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Drupal Is a Framework: Why Everyone Needs to Understand This
- A Topic for Discussion - Open Source Feature-Richness?
- Home, My Backup Data Center
- New Products
- Validate an E-Mail Address with PHP, the Right Way
- Developer Poll
- Trying to Tame the Tablet
- not living upto the mobile revolution
15 min 22 sec ago
- Deceptive Advertising and
50 min 54 sec ago
- Let\'s declare that you have
51 min 51 sec ago
- Alterations in Contest Due
52 min 57 sec ago
- At a numbers mindset, your
54 min 8 sec ago
- Do not get Just Almost any
57 min 37 sec ago
- A fantastic rule-of-thumb to
59 min ago
- Keren mastah..
1 hour 56 min ago
- mini tablet compare
3 hours 15 min ago
- Looking Good
6 hours 48 min ago
Enter to Win an Adafruit Prototyping Pi Plate Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Prototyping Pi Plate Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- Next winner announced on 5-21-13!
Free Webinar: Linux Backup and Recovery
Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.
In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.