Postgres: Difference between revisions

From Bitpost wiki
(Created page with "=== 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...")
 
No edit summary
Line 1: Line 1:
=== DEV INSTALL ===
* clone [https://github.com/postgres/postgres postgres]
cd development/Libraries/c
git clone git@github.com:postgres/postgres.git
cd postgres
mkdir default_configure && cd default_configure
../configure && make && make install
# may need: sudo apt install libreadline-dev bison flex
# use apt search libreadline (etc)
=== NOSQL via JSON ===
=== NOSQL via JSON ===
GORGEOUS POSTGRES JSON QUERY!  inject blobs of JSON, then you can crack open the json inside a cell!
GORGEOUS POSTGRES JSON QUERY!  inject blobs of JSON, then you can crack open the json inside a cell!

Revision as of 14:49, 29 August 2018

DEV INSTALL

cd development/Libraries/c
git clone git@github.com:postgres/postgres.git
cd postgres
mkdir default_configure && cd default_configure
../configure && make && make install
# may need: sudo apt install libreadline-dev bison flex
# use apt search libreadline (etc)


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/