Postgres

DEV INSTALL

• clone and install postgres

cd ~/development/Libraries/c
git clone git@github.com:postgres/postgres.git
cd postgres
mkdir default_configure && cd default_configure
../configure --prefix=/usr/local && 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 git@github.com:jtv/libpqxx.git
cd libpqxx
# add pg bin folder to path
export PATH=$PATH:/usr/local/bin
mkdir default_configure && cd default_configure
../configure --disable-documentation && make && sudo make install

• set up a data folder where postgres will put all its data and its log:

# You must have a postgres user; on gentoo:
sudo useradd postgres

# Standardize on [/home/m/development/postgres], but always consider symlinking to a large/redundant location.
# Here, we are choosing to use a larger RAID drive.
mkdir /spiceflow/softraid/development/postgres
mkdir /spiceflow/softraid/development/postgres/data
cd ~/development && ln -s /spiceflow/softraid/development/postgres postgres
chown -R postgres:postgres postgres /spiceflow/softraid/development/postgres

# We must init the data folder as a postgres "database cluster"
su postgres -c '/usr/local/bin/initdb -D /home/m/development/postgres/data'

START SERVER

If you installed as above, this is the command to start the server:

su postgres -c '/usr/local/bin/pg_ctl start  -l /home/m/development/postgres/log/log.txt -D /home/m/development/postgres/data'

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/