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:

# Set up a symlink to a larger RAID drive
mkdir /spiceflow/softraid/development/postgres-data
sudo mkdir /usr/local/pgsql
cd /usr/local/pgsql
ln -s /spiceflow/softraid/development/postgres-data data

START SERVER

You must have a postgres user:

# on gentoo
sudo useradd postgres

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

su postgres -c '/usr/local/bin/pg_ctl start -l /usr/local/pgsql/log.txt -D /usr/local/pgsql/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/