1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109

-- offset + fetch
OFFSET row_to_skip { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

-- grouping sets
SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);

-- CUBE
CUBE(c1,c2,c3) 

GROUPING SETS (
    (c1,c2,c3), 
    (c1,c2),
    (c1,c3),
    (c2,c3),
    (c1),
    (c2),
    (c3), 
    ()
 )

-- CTE
WITH cte_name (column1, column2, ...) AS (
    -- CTE query
    SELECT ...
)
-- Main query using the CTE
SELECT ... FROM cte_name;

WITH action_films AS (
  SELECT 
    f.title, 
    f.length 
  FROM 
    film f 
    INNER JOIN film_category fc USING (film_id) 
    INNER JOIN category c USING(category_id) 
  WHERE 
    c.name = 'Action'
) 
SELECT * FROM action_films;

-- upsert
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;

-- reset auto_increment
TRUNCATE TABLE persons RESTART IDENTITY;

-- serial
CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

-- get the recent value generated by the sequence
SELECT currval(pg_get_serial_sequence('fruits', 'id'));

-- sequence
CREATE SEQUENCE mysequence
INCREMENT 5
START 100;

-- nextval
select nextval('mysequence');

-- identity
CREATE TABLE color (
    color_id INT GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    color_name VARCHAR NOT NULL
);

-- generated columns
CREATE TABLE table_name(
   ...,
   colum_name type GENERATED ALWAYS AS (expression ) STORED | VIRTUAL,
   ...
);
-- --
  CREATE TABLE contacts(
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
    email VARCHAR(300) UNIQUE
  );
-- rename tables
-- -- When you rename a table, PostgreSQL will automatically update its dependent objects such as foreign key constraints, views, and indexes.
ALTER TABLE vendors RENAME TO suppliers;

-- copy table
CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- test schema & data
CREATE TABLE product_groups (
  group_id serial PRIMARY KEY,
  group_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
  product_id serial PRIMARY KEY,
  product_name VARCHAR (255) NOT NULL,
  price DECIMAL (11, 2),
  group_id INT NOT NULL,
  FOREIGN KEY (group_id) REFERENCES product_groups (group_id)
);

INSERT INTO product_groups (group_name)
VALUES
  ('Smartphone'),
  ('Laptop'),
  ('Tablet');

INSERT INTO products (product_name, group_id,price)
VALUES
  ('Microsoft Lumia', 1, 200),
  ('HTC One', 1, 400),
  ('Nexus', 1, 500),
  ('iPhone', 1, 900),
  ('HP Elite', 2, 1200),
  ('Lenovo Thinkpad', 2, 700),
  ('Sony VAIO', 2, 700),
  ('Dell Vostro', 2, 800),
  ('iPad', 3, 700),
  ('Kindle Fire', 3, 150),
  ('Samsung Galaxy Tab', 3, 200);

-- avg
SELECT
  group_name,
  AVG (price)
FROM
  products
INNER JOIN product_groups USING (group_id)
GROUP BY
  group_name;

-- window
SELECT
  product_name,
  price,
  group_name,
  AVG (price) OVER (
     PARTITION BY group_name
  )
FROM
  products
  INNER JOIN
    product_groups USING (group_id);

1
2
3
4
5
6
7

BEGIN TRANSACTION; BEGIN WORK; BEGIN;
  delete from products where id = 1;
ROLLBACK; COMMIT WORK; COMMIT TRANSACTION; COMMIT;
--
BEGIN TRANSACTION;
  update products set price = price + 200 where id = 1;
END;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- import by using copy
COPY persons(first_name, last_name, dob, email)
FROM '/sampledb/persons.csv'
DELIMITER ','
CSV HEADER;

-- export to local file
COPY persons TO '/sampledb/persons_export.csv'
DELIMITER ','
CSV HEADER;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- test schema
CREATE TABLE
    LOGS (
        event_id INTEGER,
        old_title VARCHAR(255),
        old_starts TIMESTAMP,
        old_ends TIMESTAMP,
        logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

-- execution function
CREATE
OR
REPLACE
    FUNCTION log_event () RETURNS TRIGGER AS $$ DECLARE BEGIN
INSERT INTO
    LOGS (event_id, old_title, old_starts, old_ends)
VALUES
    (OLD.event_id, OLD.title, OLD.starts, OLD.ends);
raise notice 'someone just changed event #%',
OLD.event_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- trigger
CREATE TRIGGER log_events AFTER
UPDATE ON EVENTS FOR EACH ROW
EXECUTE PROCEDURE log_event();

-- test
update events set ends='' where title='house party';

1
2
3
4
5
6
7

-- a new view
create view holidays as
    select event_id as holiday_id, title as name, starts as date from events
    where title like '%Day%' and venue_id is null;

-- select things from view
select name, to_char(date, 'Month DD, YYYY') as date from holidays where date <= '2018-04-01';