CREATE TABLE orders (created_at timestamp);

INSERT INTO orders VALUES
  ('2019-01-01 04:05:33'),
  ('2019-01-01 09:05:51'),
  ('2019-01-03 02:55:14'),
  ('2019-01-08 14:12:07');

SELECT date_trunc('day', created_at) AS day,
       COUNT(created_at) FROM orders
       GROUP BY day
       ORDER BY day;

day                 | count
----------------------------
2019-01-01T00:0 ... | 2
2019-01-08T00:0 ... | 1
2019-01-03T00:0 ... | 1

CREATE TABLE electrothingy
AS
  SELECT
    x::int AS id,
    (x::int % 20000)::int AS locid,  -- fake location ids in the range of 1-20000
    now() AS tsin,                   -- static timestmap
    97.5::numeric(5,2) AS temp,      -- static temp
    x::int AS usage                  -- usage the same as id not sure what we want here.
  FROM generate_series(1,1728000000) -- for 1.7 billion rows
    AS gs(x);

CREATE TABLE numbers (id int);
INSERT INTO numbers SELECT * FROM generate_series(1,1000);

CREATE EXTENSION pgstattuple;
SELECT * FROM public.pgstattuple('numbers');

DELETE FROM numbers WHERE id < 500;
SELECT * FROM public.pgstattuple('numbers');

-- blocks all writes
CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

-- only blocks other DDL
CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

ALTER TABLE items ADD COLUMN last_update timestamptz;

SET lock_timeout TO '2s'
ALTER TABLE items ADD COLUMN last_update timestamptz;

-- blocks queries for a long time
ALTER TABLE items ADD PRIMARY KEY (id);

-- takes a long time, but doesn’t block queries
CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id);
-- blocks queries, but only very briefly
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;

SELECT DISTINCT city FROM employees;

SELECT city FROM employees GROUP BY city;

CREATE TABLE people
  (name text, age int, interests text[]);

INSERT INTO people (name, age, interests)
  VALUES
  ('Jon', 12, ARRAY ['Golf', 'Food']),
  ('Jane', 45, ARRAY ['Art']);

SELECT row_to_json(ROW(name, age, interests)) FROM people;

row_to_json
-----------------------------------
{"f1":"Jon","f2":12,"f3":["Golf","Food"]}
{"f1":"Jane","f2":45,"f3":["Art"]}

SELECT row_to_json(q1) FROM
  (SELECT * FROM people LIMIT 1) q1;

row_to_json
-----------------------------------
{"name":"Jon","age":12,"interests":["Golf","Food"]}

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'