PostgreSQL: Up and Running: A Practical Introduction to the Advanced Open Source Database

PostgreSQL: Up and Running: A Practical Introduction to the Advanced Open Source Database

Language: English

Pages: 234

ISBN: 1449373194

Format: PDF / Kindle (mobi) / ePub

Thinking of migrating to PostgreSQL? This clear, fast-paced introduction helps you understand and use this open source database system. Not only will you learn about the enterprise class features in versions 9.2, 9.3, and 9.4, you'll also discover that PostgeSQL is more than a database system--it's also an impressive application platform.
With examples throughout, this book shows you how to achieve tasks that are difficult or impossible in other databases. This second edition covers LATERAL queries, augmented JSON support, materialized views, and other key topics. If you're a current PostgreSQL user, you'll pick up gems you may have missed before.
  • Learn basic administration tasks such as role management, database creation, backup, and restore
  • Apply the psql command-line utility and the pgAdmin graphical administration tool
  • Explore PostgreSQL tables, constraints, and indexes
  • Learn powerful SQL constructs not generally found in other databases
  • Use several different languages to write database functions
  • Tune your queries to run as fast as your hardware will allow
  • Query external and variegated data sources with foreign data wrappers
  • Learn how to use built-in replication features to replicate data













and users. Refer to “Server Backup: pg_dumpall” on page 140 for listing of available pg_dumpall command options. It’s a good idea to backup globals such as roles and tablespace definitions on a daily basis. Although you can use pg_dumpall to backup databases as well, we generally don’t bother or do it—at most, once a month—since it would take much longer to restore the plain text backup for large databases. To backup roles and tablespaces: pg_dumpall -h localhost -U postgres --port=5432 -f

of languages allows you to solve problems best addressed with a domain or more procedural language; for example, using R statistics functions and R succinct domain idioms to solve statistics problems; calling a web service via Python; or writing map reduce constructs and then using these functions within an SQL statement. You can even write aggregate functions in any of these languages that makes the combination more powerful than you can achieve in any one, straight language environment. In

constructor that can be used to insert more than one record at a time. The multi-row constructor has been in existence since 8.2. The constructor can in fact be used in any SQL and behaves exactly like a table. Example 6-5. Using multi-row consructor to insert data INSERT INTO logs_2011(user_name, description, log_ts) VALUES ('robe', 'logged in', '2011-01-10 10:15 AM EST') , ('lhsu', 'logged out', '2011-01-11 10:20 AM EST'); It’s much like a single row INSERT VALUES() syntax except you can add

ON DELETE RESTRICT; CREATE INDEX fki_facts_lu_tracts ON census.facts(tract_id); In this first constraint, we define a foreign key relationship between our facts and fact_types table. This prevents us from introducing fact types not already present in our fact types lookup table. We also define a cascade rule that automatically update the fact_type_id in our facts table should we renumber our fact types. We restrict deletes from our lookup table if any values are in use. Although RESTRICT is

c.relkind = 'r'::"char" AND c.relhassubclass = true UNION ALL SELECT c.oid As tableoid, n.nspname AS schemaname 92 | Chapter 7: SQL: The PostgreSQL Way , tbls.tablename || '->' || c.relname AS tablename FROM tbls INNER JOIN pg_inherits As th ON th.inhparent = tbls.tableoid INNER JOIN pg_class c ON th.inhrelid = c.oid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace ) SELECT * FROM tbls ORDER BY tablename; tableoid | schemaname | tablename

Download sample