Skip to content

Instantly share code, notes, and snippets.

@xzilla
Last active May 29, 2020 20:46
Show Gist options
  • Save xzilla/6e5358212ece1ccd29549bab4d878801 to your computer and use it in GitHub Desktop.
Save xzilla/6e5358212ece1ccd29549bab4d878801 to your computer and use it in GitHub Desktop.
Whats New In PostgreSQL 12 => Demos Only
\q
/*
What's New In PostgreSQL 12: All Demo Edition
Robert Treat (@robtreat2) - v5 - 2020-05-23
Requirements:
Postgres 12
Pagila Sample Database (https://github.com/xzilla/pagila)
## Table of Contents ##
Automagically Handle Extra Spaces in TO_DATE
WITH OIDS removed
Foreign Keys now named with all columns
Increase Default Set of FTS Languages
Friendlier Config Sizing
Create or Replace Aggregate now supported
Add entries to ENUM type
CHAINED COMMITS
Create Statistics for Multiple Columns
GENERATED COLUMN
REINDEX CONCURRENTLY
New Per Table Vacuum Options
New System Views
Strict Modes for Plpgsql
COMMON MATERIALIZED EXPRESSIONS
EXPLAIN (settings on)
forced plan caching
Pluggable Storage
Log Transaction Sample
COPY With Where Clause
SQL Callable Standby Promotion
Better Default Recovery Target
psql Tricks
Partition Introspection
JSON Path
## ENDTOC
*/
/* Automagically Handle Extra Spaces in TO_DATE */
SELECT TO_DATE('2019/10/03', ' YYYY/ MM/ DD ') ;
/* WITH OIDS removed */
create table withoids (x int) WITH OIDS;
\d pg_class
-- Note oids have always been there.
SELECT oid, relname FROM pg_class LIMIT 5;
/* New System Views */
\d pg_stat_progress_cluster
\d pg_stat_progress_create_index
/* Foreign Keys now named with all columns */
create table fknaming (a smallint, f smallint, foreign key (a,f) references film_actor(actor_id, film_id));
\d fknaming
/* Increase Default Set of FTS Languages */
\dF
/* Friendlier Config Sizing */
show maintenance_work_mem ;
set maintenance_work_mem = '21.12MB';
show maintenance_work_mem ;
/* Create or Replace Aggregate now supported
* Also, psql now shows help urls
*/
\h create aggregate
/* REINDEX CONCURRENTLY */
\h reindex
reindex (verbose) table concurrently actor;
/* CHAINED COMMITS
* You can now add 'AND CHAIN' clause to txn commands
* This will preserve txn specific settings across commit or rollback
* This is also available in stored procedures
*/
SHOW transaction_isolation;
BEGIN ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;
INSERT INTO actor(first_name, last_name)
VALUES ('DYLAN','LEE');
COMMIT AND CHAIN;
INSERT INTO actor(first_name, last_name)
VALUES ('ROBERT','LEE');
ROLLBACK AND CHAIN;
INSERT INTO actor(first_name, last_name)
VALUES ('EMMA','LEE');
COMMIT AND CHAIN ;
SHOW transaction_isolation ;
select * from actor where last_name = 'LEE';
COMMIT;
SHOW transaction_isolation ;
/* Create Statistics for Multiple Columns */
SELECT * FROM city, LATERAL (SELECT country FROM country WHERE country_id=city.country_id) country WHERE city LIKE 'L%n';
CREATE STATISTICS geopoints(mcv)
ON city,country_id FROM city;
SET default_statistics_target = 1000;
ANALYZE city;
SELECT s.* FROM pg_statistic_ext_data, pg_mcv_list_items(stxdmcv) s WHERE array_to_string(values,':') ~* 'Lon';
/* GENERATED COLUMN
# Must be based on computed values
# Cannot refence SQL queries
# Cannot reference other Generated Columns
*/
alter table film add column revenue_potential numeric(5,2) generated always as (rental_duration * rental_rate) stored;
select title, rental_duration, rental_rate, revenue_potential from film limit 5;
insert into film (title,language_id,rental_duration,rental_rate,revenue_potential) values ('The Rise Of SQLWalker',1,21,12.00,1000000);
SELECT * FROM film WHERE film_id = 1;
UPDATE film SET rental_duration = 5 WHERE film_id = 1;
SELECT * FROM film WHERE film_id = 1;
SELECT attname, attgenerated
FROM pg_attribute WHERE attrelid='film'::regclass;
SELECT * FROM information_schema.column_column_usage ;
/* New Per Table Vacuum Options */
alter table rental set
(vacuum_index_cleanup = off, vacuum_truncate = off );
\d+ rental
vacuum (verbose on) rental;
vacuum (verbose on, index_cleanup on) rental;
/* Strict Modes for Plpgsql */
DO $$
DECLARE x INTEGER ;
DECLARE y INTEGER ;
DECLARE z INTEGER ;
BEGIN
SELECT 1 INTO x,y;
RAISE NOTICE 'test #1 complete';
SELECT 1,2,3 INTO x,y;
RAISE NOTICE 'test #2 complete';
SELECT generate_series(1,2) INTO z ;
RAISE NOTICE 'test #3 complete' ;
END ;
$$ ;
SET plpgsql.extra_warnings = 'strict_multi_assignment';
SET plpgsql.extra_errors to 'too_many_rows' ;
/* COMMON MATERIALIZED EXPRESSIONS */
EXPLAIN WITH a AS MATERIALIZED (SELECT * FROM actor) SELECT * FROM a WHERE first_name = 'ROBERT';
EXPLAIN WITH a AS (SELECT * FROM actor) SELECT * FROM a WHERE first_name = 'ROBERT';
/* EXPLAIN (settings on) */
show random_page_cost ;
set random_page_cost = 1;
explain (settings on) select * from actor;
/* forced plan caching */
show plan_cache_mode;
create index idx_example_p2007_03_amount on payment_p2007_03(amount);
prepare ex(numeric(5,2)) as select * from payment_p2007_03 where amount = $1;
explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));
set enable_bitmapscan = off;
explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));
set plan_cache_mode = 'force_generic_plan';
explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));
set enable_bitmapscan = on;
explain (settings) execute ex('11.99'::numeric(5,2));
set plan_cache_mode = 'force_custom_plan';
explain (settings) execute ex('11.99'::numeric(5,2));
/* JSON Path */
CREATE TABLE rental_report (report jsonb);
INSERT INTO rental_report VALUES
('{ "customer": "KEN", "rental_date": "2005-07-08", "rentals": { "title": "SORORITY QUEEN", "mpaa-rating" : "R" } }')
,
('{ "customer": "ELSIE", "rental_date": "2005-08-17", "rentals": { "title": "CRAZY HOME", "mpaa-rating" : "PG-13" } } ')
,
('{ "customer": "JIMMY", "rental_date": "2005-08-18", "rentals": { "title": "POCUS PULP", "mpaa-rating" : "G" } } ')
,
('{ "customer": "KATHY", "rental_date": "2005-07-29", "rentals": { "title": "OUTFIELD MASSACRE", "mpaa-rating": "R" } } ')
,
('{ "customer": "GLADYS", "rental_date": "2005-08-23", "rentals": { "title": "OUTBREAK DIVINE", "mpaa-rating": "PG-13" } } ')
,
('{ "customer": "SHAWN", "rental_date": "2005-08-20", "rentals": { "title": "SCALAWAG DUCK", "mpaa-rating": "PG" } } ')
,
('{ "customer": "DIANE", "rental_date": "2005-07-31", "rentals": { "title": "MIDSUMMER GROUNDHOG", "mpaa-rating": "G" } } ')
;
select report->'customer' as customer from rental_report;
select jsonb_path_query(report,'$.customer') from rental_report;
select jsonb_path_query(report,'$.rentals[0].title') from rental_report;
select jsonb_path_query(report,'$.rentals[0].*') from rental_report;
/* Add entries to ENUM types */
\dT+ mpaa_rating
ALTER TYPE mpaa_rating ADD value 'XXX';
\dT+ mpaa_rating
/* Pluggable Storage */
\h create access method
\d actor
\set HIDE_TABLEAM on
\dA
/* Log Transaction Sample */
select * from pg_settings where name = 'log_transaction_sample_rate';
/* COPY With Where Clause */
COPY (select * from customer_list) to '/demo/pg12/customer_list.csv' WITH csv;
\! head /demo/pg12/customer_list.csv
CREATE TABLE us_customers AS SELECT * FROM customer_list LIMIT 0;
COPY us_customers from '/demo/pg12/customer_list.csv' with csv WHERE country = 'United States';
/* psql Tricks */
\conninfo
\pset format csv
select * from actor limit 5;
\pset format aligned
select * from actor limit 5;
// Label partitioned tables
\d payment
/* SQL Callable Standby Promotion */
\df pg_promote
/* Better Default Recovery Target */
select * from pg_settings where name = 'recovery_target_timeline';
/* Partition Introspection */
SELECT pg_partition_root('payment_p2007_06');
SELECT pg_partition_ancestors('payment_p2007_06');
SELECT pg_partition_tree('payment');
SELECT * FROM pg_partition_tree('payment');
-- FIN
/*
PostgreSQL License
Copyright (c) 2019,2020 Robert Treat
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment