postgresql 9.3 筆記-1

Having
可用於groupby後再做條件的過濾～

SELECT city, max(temp_lo)
FROM weather
GROUP BY city;

city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)

which gives us one output row per city. Each aggregate result is computed over the table rows match-ing that city. We can filter these grouped rows using HAVING:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo)<40;

city | max
---------+-----
Hayward | 37
(1 row)


Transactions
這個寫程式就不太會用到～因為程式大多有做transaction，jdbc的autocommit/commit/rollback就是囉～
不過註記一下如果在sql內下的話是～

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Alice’;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
-- oops ... forget that and use Wally’s account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Wally’;
COMMIT;

Inheritance
table繼承，以往得到的概念做法，都還是建兩個table，在程式實做時同時更新或異動，不過發現這裡有一個現成的做法
reference上寫了一個很棒的說明

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.
Let’s create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you’re really clever you might invent some scheme like this:

CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;

This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.
A better solution is this:

CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all columns (name,population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column,state, that shows their state.

In PostgreSQL,a table can inherit from zero or more other tables