test=# CREATE TABLE posts (
test(#     noscript text NOT NULL PRIMARY KEY,
test(#     tagz  text[]
test(#     tags  text[] NOT NULL DEFAULT '{}'::text[]
test(# );
CREATE TABLE

test=# INSERT INTO posts (noscript, tags) VALUES
test-# ('Using PostgreSQL Arrays with Golang', '{"postgres","golang"}');
INSERT 0 1

CREATE TABLE foo(id SERIAL, c1 INT, c2 INT, c3 INT, c4 INT, c5 INT,
                     it TIMESTAMP NOT NULL DEFAULT NOW());
    INSERT INTO foo(c1, c2, c3, c4, c5)
      SELECT random() * 1000000, random() * 1000000, random() * 1000000,
             random() * 1000000, random() * 1000000
      FROM generate_series(1, 1000000);

SELECT * FROM foo WHERE c1 = 171267 AND c4=65555;
     -- Seq Scan on foo
     --   Filter: ((c1 = 171267) AND (c4 = 65555))
     --   Rows Removed by Filter: 999999

CREATE INDEX foo_c1c2c3c4c5
      ON foo USING bloom(c1, c2, c3, c4, c5)
      WITH (length=64, col1=3, col2=3, col3=3, col4=3, col5=3);

SELECT * FROM foo WHERE c1 = 171267 AND c4=65555;
     -- Bitmap Heap Scan on foo
     --   Recheck Cond: ((c1 = 171267) AND (c4 = 65555))
     --   Rows Removed by Index Recheck: 232
     --   Heap Blocks: exact=232
     --   ->  Bitmap Index Scan on foo_c1c2c3c4c5
     --       Index Cond: ((c1 = 171267) AND (c4 = 65555))

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'