Back Home

Installation

 zypper install postgres postresql-server 

on Ubuntu

sudo apt-get install postgresql postgresql-contrib
# psql
psql: FATAL:  role "richard" does not exist
# su - postgres
postgres@r400:~> psql
psql (9.1.6)
Type "help" for help.
postgres=#

User postgres can also create, drop a database, connect and create tables;

postgres=# CREATE DATABASE testing;
CREATE DATABASE
postgres=# DROP DATABASE testing;
DROP DATABASE
postgres=#
postgres=# \c testing;
postgres=# create Table testTable;

Installing on AWS Redhat

to list the available posgres versions

 
yum list postgresql* 

to install

 sudo yum install postgresql-server.x86_64

to start

service postgresql initdb

Operations

  • create db
    create database crm  OWNER postgres;
    grant all privileges on database crm to postgres;

Modify the /var/lib/pgsql/data/pg_hba.conf

host all all 127.0.0.1/32 password

String Operations

Function Return Type Description Example Result
string || string text String concatenation 'Post' || 'greSQL' PostgreSQL
string || non-string or non-string || string text String concatenation with one non-string input 'Value: ' || 42 Value: 42
bit_length(string) int Number of bits in string bit_length('jose') 32
char_length(string) or character_length(string) int Number of characters in string char_length('jose') 4
lower(string) text Convert string to lower case lower('TOM') tom
octet_length(string) int Number of bytes in string octet_length('jose') 4
overlay(string placing string from int [for int]) text Replace substring overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas
position(substring in string) int Location of specified substring position('om' in 'Thomas') 3
substring(string [from int] [for int]) text Extract substring substring('Thomas' from 2 for 3) hom
substring(string from pattern) text Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. substring('Thomas' from '…$') mas
substring(string from pattern for escape) text Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. substring('Thomas' from '%#“o_a#“_' for '#') oma
trim([leading trailing both] [characters] from string) text Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string trim(both 'x' from 'xTomxx') Tom
upper(string) text Convert string to upper case upper('tom') TOM

Conditional Expressions

CASE aka Switch stamtent a conditional queries if (match) then value
COALESCE returns he first non-null argument. You can use it to substitute NULL by a default value.
NULLIF returns NULL if the first argument equals the second one.
CAST converts from one data type into another e.g., from a string into an integer, from a string into a date

Json and Jsonb

If you are doing a lot of JSON manipulation inside PostgreSQL, such as sorting, slicing, splicing, etc., you should use JSONB for speed reasons.

  • If you need indexed lookups for arbitrary key searches on JSON, then you should use JSONB.
  • If you are doing neither of the above, you should probably use JSON.
  • If you need to preserve key ordering, whitespace, and duplicate keys, you should use JSON.
CREATE TABLE person ( 'id PRIMARY KEY NOT NULL', 'PERSONDETAILS JSON')  

to filter the return result

SELECT persondetails->'name'  FROM person;

Where clause

SELECT persondetails->'name'  FROM person WHERE persondetails->>'name' = 'richard'

Bigger Example

CREATE TABLE associate ( 
id SERIAL,
json_data jsonb
);

Then we can insert some abstract data

INSERT INTO associate (json_data) VALUES ( '{
  "basics": {
    "name": "John Doe",
    "label": "Programmer",
    "picture": "",
    "email": "john@gmail.com",
    "phone": "(912) 555-4321",
    "website": "http://johndoe.com",
    "summary": "A summary of John Doe...",
    "location": {
      "address": "2712 Broadway St",
      "postalCode": "CA 94115",
      "city": "San Francisco",
      "countryCode": "US",
      "region": "California"
    },
    "profiles": [{
      "network": "Twitter",
      "username": "john",
      "url": "http://twitter.com/john"
    }]
  },
  "work": [{
    "company": "Company",
    "position": "President",
    "website": "http://company.com",
    "startDate": "2013-01-01",
    "endDate": "2014-01-01",
    "summary": "Description...",
    "highlights": [
      "Started the company"
    ]
  }],
  "volunteer": [{
    "organization": "Organization",
    "position": "Volunteer",
    "website": "http://organization.com/",
    "startDate": "2012-01-01",
    "endDate": "2013-01-01",
    "summary": "Description...",
    "highlights": [
      "Awarded Volunteer of the month"
    ]
  }],
  "education": [{
    "institution": "University",
    "area": "Software Development",
    "studyType": "Bachelor",
    "startDate": "2011-01-01",
    "endDate": "2013-01-01",
    "gpa": "4.0",
    "courses": [
      "DB1101 - Basic SQL"
    ]
  }],
  "awards": [{
    "title": "Award",
    "date": "2014-11-01",
    "awarder": "Company",
    "summary": "There is no spoon."
  }],
  "publications": [{
    "name": "Publication",
    "publisher": "Company",
    "releaseDate": "2014-10-01",
    "website": "http://publication.com",
    "summary": "Description..."
  }],
  "skills": [{
    "name": "Web Development",
    "level": "Master",
    "keywords": [
      "HTML",
      "CSS",
      "Javascript"
    ]
  }],
  "languages": [{
    "name": "English",
    "level": "Native speaker"
  }],
  "interests": [{
    "name": "Wildlife",
    "keywords": [
      "Ferrets",
      "Unicorns"
    ]
  }],
  "references": [{
    "name": "Jane Doe",
    "reference": "Reference..."
  }]
}' )
<code>
 
GET the DATA 
 
<code sql>
SELECT * FROM associate
SELECT json_data->'basics'->'name' FROM associate

Overview of functions ..

Accessing json as their object types

Operator Right Operand Type Description Example
-> int Get JSON array element '[1,2,3]'::json->2
-> text Get JSON object field '{"a":1,"b":2}'::json->'b'

Double arrow coverts fields to text for easier comparison

Operator Right Operand Type Description Example
->> int Get JSON array element as text '[1,2,3]'::json→>2
->> text Get JSON object field as text '{“a”:1,”b”:2}'::json→>'b'

hash operator .. not sure

Operator Right Operand Type Description Example
#> array of text Get JSON object at specified path '{“a”:[1,2,3],“b”:[4,5,6]}'::json#>'{a,2}'
#>> array of text Get JSON object at specified path as text '{“a”:[1,2,3],“b”:[4,5,6]}'::json#»'{a,2}'

More info Posgres and Json on this blog and Offical Json functions

Nice functions Cheat Sheet

the @> contains

The @> contains element can be used to select down the index. Allegedly supposed to be fast

SELECT * FROM raw_promotion
WHERE data->'item->>'linenumber' = "535115"
 
 
select * from raw_promotion
where data @> '{"item":{"lineNumber": "535115"}}' 

Json Arrays

SELECT  json_data->'skills' FROM associate
 
[{"name": "Web Development", "level": "Master", "keywords": ["HTML", "CSS", "Javascript"]}]

and an individual item from the array

SELECT  json_data->'skills'->0->>'name' FROM associate
 
Web Development

Index on Json

CREATE TABLE publishers(id INT, info JSON); 
CREATE INDEX ON publishers((info->>'name'));
  • ->> instead of -> The former one returns the value as text, the latter one returns a JSON object
  • and the double parenthesis (( is also important

Using Views with Json

A flexible approach to managing json(b) in postgres is to have a raw json table e.g

CREATE TABLE raw_product (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  DATA JSONB NOT NULL
);

One can then create a view including casting data types with:

CREATE VIEW sql_product AS
SELECT 
id AS id,
"data"->'item'->>'id' AS prod_id,
"data"->'item'->>'name' AS "name",
"data"->'item'->>'linenumber' AS linenumber,
CAST ("data"->'item'->>'productAvailable'AS BOOLEAN) AS productAvailable,
CAST("data"->'item'->>'organic' AS BOOLEAN) AS organic 
FROM raw_product;

Update Row (Where json may be Null)

UPDATE users
SET metadata = COALESCE(metadata::jsonb,'{}'::jsonb) || '{"test": true}'::jsonb
WHERE id=...
RETURNING *;

Row to Json

SELECT row_to_json(t)
FROM (
SELECT id, prod_id, "name" FROM sql_product
) t

returns

{"id":95254,"prod_id":"606753-86097-86098","name":"Dance Expression"}
{"id":95255,"prod_id":"606794-86101-86102","name":"Zotter Organic bird&#39;s eye chilli"}
{"id":95256,"prod_id":"606830-86105-86106","name":"First Sewing Kit"}

There are other functions like array_to_json and more examples https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

Counting Array Lengths (for JsonB)

  • json_array_length can return the json array length. For Jsonb we wrap it with “to_json”
SELECT COUNT(id) FROM raw_promotion
WHERE json_array_length(to_json("data"->'item'->'promotions')) > 1

Expanding Array (for JsonB)

For a poromotion array .. we can expand each value:

SELECT id, json_array_elements(to_json("data"->'item'->'promotions')) AS promotion FROM raw_promotion
WHERE json_array_length(to_json("data"->'item'->'promotions')) > 1

Xml

CREATE TABLE xml_data(id serial, msg xml);
INSERT INTO xml_data VALUES (1, '<promotions xmlns="http://www.demandware.com/xml/impex/promotion/2008-01-31">
    <campaign campaign-id="2013-1st-semester-jet-giveaways">
        <description>2013 1st Semester Jet Giveaways</description>
        <enabled-flag>true</enabled-flag>
        <start-date>2013-01-01T05:00:00.000Z</start-date>
        <end-date>2013-07-01T04:00:00.000Z</end-date>
        <customer-groups>
            <customer-group group-id="Everyone"/>
        </customer-groups>
    </campaign>
</promotions>')

do a standard insert

SELECT msg::text FROM xml_data 

do xpath select

SELECT xpath('//*[local-name() = "description"]/text()', msg)::text  
FROM xml_data 

Indexing

You can add indexes on any of these using PostgreSQL’s expression indexes, which means you can even add unique constraints based on your nested JSON data:

CREATE UNIQUE INDEX books_author_first_name ON books ((data->'author'->>'first_name'));

then unique index values will persist on the json object

INSERT INTO books VALUES (4,
  '{ "name": "Book the Fourth", "author": { "first_name": "Charles", "last_name": "Davis" } }');

ERROR:  duplicate key value violates unique constraint "books_author_first_name"
DETAIL:  Key (((data -> 'author'::text) ->> 'first_name'::text))=(Charles) already exists.

Partitioning and Inheritance

CREATE TABLE message (
id SERIAL,
messageName VARCHAR(40),
rawXml xml
);
 
CREATE UNIQUE INDEX message_idx ON message (id);
CREATE TABLE physical_asset_message () INHERITS (message);
 
CREATE TABLE physical_asset_message_y2016 (
notes VARCHAR(40)
) INHERITS (physical_asset_message);
 
CREATE TABLE physical_asset_message_y2017 ( ) INHERITS (physical_asset_message);
 
INSERT INTO  physical_asset_message_y2016(messageName, rawXml, notes) VALUES ('blah', '<xml>test\btest</xml>', 'hello harsha');
 
SELECT * FROM message
SELECT * FROM physical_asset_message
SELECT * FROM physical_asset_message_y2016
SELECT * FROM physical_asset_message_y2017

Loading Data

The data can be loaded into a PostgreSQL database using the JSONB data type with the following commands:

  • \copy
CREATE TABLE reviews(review jsonb);
\copy reviews FROM 'customer_reviews_nested_1998.json'
VACUUM ANALYZE reviews;

it is always a good idea to “VACUUM ANALYZE” a table after a bulk load of data. It ensures that all the internal structures used by PostgreSQL are up to date (hint bits, visibility map, etc…) and collects statistics about the content of the table, so the query planner can use these statistics to help determine the most efficient execution plans for queries.

We can also copy to stout

COPY country TO STDOUT WITH DELIMITER '|';

GIS

Json

Hstore

  • Does each id have many key/value pairs (hundreds+)? Don't use hstore.
  • Will any of your values contain large blocks of text (4kb+)? Don't use hstore.
  • Do you want to be able to search by keys in wildcard expressions? Don't use hstore.
  • Do you want to do complex joins/aggregation/reports? Don't use hstore.
  • Will you update the value for a single key? Don't use hstore.
  • Multiple keys with the same name under an id? Can't use hstore.

Live Listen/Notify Events

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
    BEGIN
        SELECT pg_notify('test', TG_TABLE_NAME);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Assign a trigger for every table you want to track:

CREATE TRIGGER table_change 
    AFTER INSERT OR UPDATE OR DELETE ON TABLE_NAME
    FOR EACH ROW EXECUTE PROCEDURE notify_change();

Clustering

* replicating tables, monitoring and management with https://pgdash.io/

Modern Driver with Async

Found at http://impossibl.github.io/pgjdbc-ng/index.html

  • JDBC 4.1 target (with goal of complete conformance)
  • UDT support through standard SQLData, SQLInput & SQLOutput
  • Support for JDBC custom type mappings
  • Pluggable custom type serialization
  • Compact binary format with text format fallback
  • Database, ResultSet and Parameter meta data
  • Transactions / Savepoints
  • Blobs
  • Updatable ResultSets
  • Callable Statements
  • Asynchronous Notifications

See also ShareDb for Websocket PostgreSQL sync

Clients

 
postgres_sql.txt · Last modified: 2018/08/09 13:15 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd