Street Fighter V: Toryuken 2018

postgres 10 range

int4range — Range of integer

int8range — Range of bigint

numrange — Range of numeric

tsrange — Range of timestamp without time zone

tstzrange — Range of timestamp with time zone

daterange — Range of date

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Containment
SELECT int4range(10, 20) @> 3;
false

-- Overlaps
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
true

-- Extract the upper bound
SELECT upper(int8range(15, 25));
25

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);
[15,20)

-- Is the range empty?
SELECT isempty(numrange(1, 5));
false

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-------------------------------------------------
CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');

ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1

postgres 9 composite types

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

since the name item is taken to be a table name, not a column name of on_hand, per SQL syntax rules. You must write it like this:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
or
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

postgres 8 type, array

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

-------------------------------------
If you really need to do something like that, you can either write a custom operator or add explicit casts to your query:

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
------+-----------
 Moe  |         4
(1 row)

--------------------------------
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

------------------------------------
an array of n elements starts with array[1] and ends with array[n].

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 array_positions
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}

postgres 7 with recursive

using recursive query to find all sub levels given top level for tree architecture.

A

B             C

              BA BB       CA CB CC       

...........

tree view

find total base elements of the tree

with recursive tree_map(sub_level, current_level, quantity) as(
 with tree as (
  select * from (
   values(array['B','C'], 'A' , 1),
 
    (array['BA', 'BB'], 'B', 3),
    (array['CA','CB','CC'], 'C', 2),
 
    (array['BAA','BAB','BAC'], 'BA', 4),
    (array['BBA','BBB'], 'BB', 2),
    (array['CAA','CAB'], 'CA', 2),
    (array['CBA'], 'CB', 5),
    (array['CCA','CCB','CCC','CCD','CCE'], 'CC', 1),
 
    (array['x'], 'BAA', 10),
    (array['y'], 'BAB', 6),
    (array['z'], 'BAC', 8),
    (array['y'], 'BBA', 3),
    (array['x'], 'BBB', 5),
    (array['x'], 'CAA', 2),
    (array['y'], 'CAB', 10),
    (array['x'], 'CBA', 1),
    (array['y'], 'CCA', 7),
    (array['z'], 'CCB', 11)
    )
  as t(sub_level, current_level, quantity)
 )
 select sub_level, current_level, quantity from tree where current_level='A'
 union all
 select tree.sub_level, tree.current_level, tree.quantity
 from tree , tree_map
 where tree_map.sub_level @> array[tree.current_level]   --contains
)
select sub_level, sum(quantity) as total_quantity from tree_map
where sub_level @> array['x'] or sub_level @> array['y'] or sub_level @> array['z']
group by sub_level

--------------------------------------
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

----------------------------------
WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

-----------------------------------
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

----------------------------------


https://www.postgresql.org/docs/current/queries-with.html

How to Install and configuration PostgreSQL on Ubuntu Linux

--open terminal
CTRL + ALT + T

--access root
sudo su -

--install postgres
apt-get install postgresql postgresql-contrib

--configure postgres to start upon server boot
update-rc.d postgresql enable

--install pgadmin
sudo apt-get install pgadmin4

--start postgres
service postgresql start

--set password
sudo su -
psql
alter user postgres with password 'xxx'

--open pgadmin
--right click servers -> create -> server
--gereral -> name: localhost
--connection -> address: 127.0.0.1 -> password: xxx
--save

reference:
https://www.youtube.com/watch?v=-LwI4HMR_Eg
https://www.godaddy.com/garage/how-to-install-postgresql-on-ubuntu-14-04/
https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/
http://www.indjango.com/ubuntu-install-postgresql-and-pgadmin/
https://milq.github.io/useful-terminal-commands-ubuntu-debian/

Ctrl + Alt + H  -  show hidden files
https://www.howtogeek.com/howto/ubuntu/view-hidden-files-and-folders-in-ubuntu-file-browser/

postgres 6 returning, lateral, group by

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

In a DELETE, the data available to RETURNING is the content of the deleted row.

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

LATERAL allows them to reference columns provided by preceding FROM items

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

ROLLUP ( e1, e2, e3, ... ) is equivalent to

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

CUBE ( a, b, c ) is equivalent to

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         )
)