Postgres

DEV INSTALL

• clone and install postgres

cd ~/development/Libraries/c
git clone [email protected]:postgres/postgres.git
cd postgres
mkdir default_configure && cd default_configure
../configure && make && sudo make install
# may need: sudo apt install libreadline-dev bison flex
# use apt search libreadline (etc)

• clone and install libpqxx

cd ~/development/Libraries/c++
git clone [email protected]:jtv/libpqxx.git
cd libpqxx
# add pg bin folder to path
export PATH=$PATH:/usr/local/pgsql/bin
mkdir default_configure && cd default_configure
../configure && make && sudo make install

NOSQL via JSON

GORGEOUS POSTGRES JSON QUERY! inject blobs of JSON, then you can crack open the json inside a cell!

   CREATE TABLE stocks (
     id integer NOT NULL,
     board_id integer NOT NULL,
     data jsonb
   );
   INSERT INTO stocks VALUES (1, 1, '{"symbol": "GOOG", "tags": ["hot", "trending"], "owned": true}');

   # returns count of data that HAS the field ingredients
   SELECT count(*) FROM stocks WHERE data ? 'ingredients';

   # ->> will return the property as integer or text (the parsed form of the attribute).
   SELECT * FROM stocks WHERE data->>'owned' = 'true';         # if data.owned == true

   # -> will return the attribute as a JSON object
   SELECT jsonb_array_elements_text(data->'tags') as tag FROM stocks WHERE id = 1;
   # returns the actual tag values, eg:
       tag
       --------------
       Improvements
       Office
       (2 rows)

   # NEXT: EXPLORE INDEXING!
   https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/