Back Home

Installation

 zypper install postgres postresql-server 
# 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

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

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.

Clients

 
postgres_sql.txt · Last modified: 2017/10/09 07:56 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd