pgdevel / src / tutorial / advanced.source

---------------------------------------------------------------------------
--
-- advanced.sql-
--    Tutorial on advanced more PostgreSQL features
--
--
-- Copyright (c) 1994, Regents of the University of California
--
-- src/tutorial/advanced.source
--
---------------------------------------------------------------------------

-----------------------------
-- Inheritance:
--	A table can inherit from zero or more tables.  A query can reference
--	either all rows of a table or all rows of a table plus all of its
--	descendants.
-----------------------------

-- For example, the capitals table inherits from cities table. (It inherits
-- all data fields from cities.)

CREATE TABLE cities (
	name		text,
	population	float8,
	altitude	int		-- (in ft)
);

CREATE TABLE capitals (
	state		char(2)
) INHERITS (cities);

-- Now, let's populate the tables.
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
INSERT INTO cities VALUES ('Mariposa', 1200, 1953);

INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');

SELECT * FROM cities;
SELECT * FROM capitals;

-- You can find all cities, including capitals, that
-- are located at an altitude of 500 ft or higher by:

SELECT c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

-- To scan rows of the parent table only, use ONLY:

SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;


-- clean up (you must remove the children first)
DROP TABLE capitals;
DROP TABLE cities;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.