PostgreSQL学习笔记 - 墨天轮文档

PostgreSQL学习笔记

章芋文

3547

74页

20次

2019-08-16

5墨值下载

PostgreSQL

Notes for Professionals

PostgreSQL

Notes for Professionals

GoalKicker.com

Free Programming Books

Disclaimer

This is an unocial free book created for educational purposes and is

not aliated with ocial PostgreSQL® group(s) or company(s).

All trademarks and registered trademarks are

the property of their respective owners

60+ pages

of professional hints and tricks

Contents

About 1 ...................................................................................................................................................................................

Chapter 1: Getting started with PostgreSQL 2 ..................................................................................................

Section 1.1: Installing PostgreSQL on Windows 2 ...........................................................................................................

Section 1.2: Install PostgreSQL from Source on Linux 3 ...............................................................................................

Section 1.3: Installation on GNU+Linux 4 .........................................................................................................................

Section 1.4: How to install PostgreSQL via MacPorts on OSX 5 ...................................................................................

Section 1.5: Install postgresql with brew on Mac 7 ........................................................................................................

Section 1.6: Postgres.app for Mac OSX 7 ........................................................................................................................

Chapter 2: Data Types 8 ...............................................................................................................................................

Section 2.1: Numeric Types 8 ...........................................................................................................................................

Section 2.2: Date/ Time Types 8 ....................................................................................................................................

Section 2.3: Geometric Types 9 .......................................................................................................................................

Section 2.4: Network Adress Types 9 .............................................................................................................................

Section 2.5: Character Types 9 .......................................................................................................................................

Section 2.6: Arrays 9 .........................................................................................................................................................

Chapter 3: Dates, Timestamps, and Intervals 11 .............................................................................................

Section 3.1: SELECT the last day of month 11 ...............................................................................................................

Section 3.2: Cast a timestamp or interval to a string 11 ..............................................................................................

Section 3.3: Count the number of records per week 11 ...............................................................................................

Chapter 4: Table Creation 12 .....................................................................................................................................

Section 4.1: Show table deﬁnition 12 ...............................................................................................................................

Section 4.2: Create table from select 12 ........................................................................................................................

Section 4.3: Create unlogged table 12 ...........................................................................................................................

Section 4.4: Table creation with Primary Key 12 ..........................................................................................................

Section 4.5: Create a table that references other table 13 ..........................................................................................

Chapter 5: SELECT 14 ......................................................................................................................................................

Section 5.1: SELECT using WHERE 14 .............................................................................................................................

Chapter 6: Find String Length / Character Length 15 ...................................................................................

Section 6.1: Example to get length of a character varying ﬁeld 15 .............................................................................

Chapter 7: COALESCE 16 ...............................................................................................................................................

Section 7.1: Single non null argument 16 ........................................................................................................................

Section 7.2: Multiple non null arguments 16 ..................................................................................................................

Section 7.3: All null arguments 16 ...................................................................................................................................

Chapter 8: INSERT 17 ......................................................................................................................................................

Section 8.1: Insert data using COPY 17 ...........................................................................................................................

Section 8.2: Inserting multiple rows 18 ...........................................................................................................................

Section 8.3: INSERT data and RETURING values 18 .....................................................................................................

Section 8.4: Basic INSERT 18 ...........................................................................................................................................

Section 8.5: Insert from select 18 ....................................................................................................................................

Section 8.6: UPSERT - INSERT ... ON CONFLICT DO UPDATE.. 19 ................................................................................

Section 8.7: SELECT data into ﬁle 19 ..............................................................................................................................

Chapter 9: UPDATE 21 ....................................................................................................................................................

Section 9.1: Updating a table based on joining another table 21 ................................................................................

Section 9.2: Update all rows in a table 21 ......................................................................................................................

Section 9.3: Update all rows meeting a condition 21 ...................................................................................................

Section 9.4: Updating multiple columns in table 21 ......................................................................................................

Chapter 10: JSON Support 22 .....................................................................................................................................

Section 10.1: Using JSONb operators 22 ........................................................................................................................

Section 10.2: Querying complex JSON documents 26 ..................................................................................................

Section 10.3: Creating a pure JSON table 27 .................................................................................................................

Chapter 11: Aggregate Functions 28 ........................................................................................................................

Section 11.1: Simple statistics: min(), max(), avg() 28 .....................................................................................................

Section 11.2: regr_slope(Y, X) : slope of the least-squares-ﬁt linear equation determined by the (X, Y) pairs

28 ................................................................................................................................................................................

Section 11.3: string_agg(expression, delimiter) 29 ........................................................................................................

Chapter 12: Common Table Expressions (WITH) 31 .........................................................................................

Section 12.1: Common Table Expressions in SELECT Queries 31 .................................................................................

Section 12.2: Traversing tree using WITH RECURSIVE 31 ............................................................................................

Chapter 13: Window Functions 32 .............................................................................................................................

Section 13.1: generic example 32 .....................................................................................................................................

Section 13.2: column values vs dense_rank vs rank vs row_number 33 ...................................................................

Chapter 14: Recursive queries 34 .............................................................................................................................

Section 14.1: Sum of Integers 34 ......................................................................................................................................

Chapter 15: Programming with PL/pgSQL 35 ....................................................................................................

Section 15.1: Basic PL/pgSQL Function 35 ......................................................................................................................

Section 15.2: custom exceptions 35 ................................................................................................................................

Section 15.3: PL/pgSQL Syntax 36 ..................................................................................................................................

Section 15.4: RETURNS Block 36 .....................................................................................................................................

Chapter 16: Inheritance 37 ............................................................................................................................................

Section 16.1: Creating children tables 37 ........................................................................................................................

Chapter 17: Export PostgreSQL database table header and data to CSV ﬁle 38 ...........................

Section 17.1: copy from query 38 ....................................................................................................................................

Section 17.2: Export PostgreSQL table to csv with header for some column(s) 38 ...................................................

Section 17.3: Full table backup to csv with header 38 ...................................................................................................

Chapter 18: Triggers and Trigger Functions 39 .................................................................................................

Section 18.1: Type of triggers 39 ......................................................................................................................................

Section 18.2: Basic PL/pgSQL Trigger Function 40 .......................................................................................................

Chapter 19: Event Triggers 42 ....................................................................................................................................

Section 19.1: Logging DDL Command Start Events 42 ..................................................................................................

Chapter 20: Role Management 43 ...........................................................................................................................

Section 20.1: Create a user with a password 43 ............................................................................................................

Section 20.2: Grant and Revoke Privileges 43 ...............................................................................................................

Section 20.3: Create Role and matching database 44 .................................................................................................

Section 20.4: Alter default search_path of user 44 ......................................................................................................

Section 20.5: Create Read Only User 45 ........................................................................................................................

Section 20.6: Grant access privileges on objects created in the future 45 ................................................................

Chapter 21: Postgres cryptographic functions 46 ............................................................................................

Section 21.1: digest 46 .......................................................................................................................................................

Chapter 22: Comments in PostgreSQL 47 ............................................................................................................

Section 22.1: COMMENT on Table 47 ..............................................................................................................................

Section 22.2: Remove Comment 47 ...............................................................................................................................

Chapter 23: Backup and Restore 48 ........................................................................................................................

Section 23.1: Backing up one database 48 ....................................................................................................................

Section 23.2: Restoring backups 48 ................................................................................................................................

of 74

5墨值下载

postgresql pg

相关文档

评论