Postgres OnLine Journal: March / April / May 2015
An in-depth Exploration of the PostgreSQL Open Source Database
 

Table Of Contents

PostgreSQL Q & A

PostgreSQL Q & A

 

LATERAL WITH ORDINALITY - numbering sets



One of the neat little features that arrived at PostgreSQL 9.4 is the WITH ORDINALITY ANSI-SQL construct. What this construct does is to tack an additional column called ordinality as an additional column when you use a set returning function in the FROM part of an SQL Statement.

Basic WITH ORDINALITY

Here is the simple example of its use:

SELECT *
FROM unnest('{my,dog, eats, dog food}'::text[] ) 
    WITH ordinality;
which outputs:
  unnest  | ordinality
----------+------------
 my       |          1
 dog      |          2
 eats     |          3
 dog food |          4

If you aren't happy with the default column names, you can change them by revising your query like this

 
SELECT f.*
FROM unnest('{my,dog, eats, dog food}'::text[] ) 
    WITH ordinality As f(phrase, sort_order);

Which would output this:

  phrase  | sort_order
----------+------------
 my       |          1
 dog      |          2
 eats     |          3
 dog food |          4

LATERAL WITH ORDINALITY

The greatest value of WITH ORDINALITY comes when you apply it to rows of data. How do you do that when you need to use WITH ORDINALITY. This is where one of our favorite constructs, the LATERAL construct comes to the rescue.

First let's construct our table with a text array column for demonstration. Note that the fish has no tags.

CREATE TABLE pets(pet varchar(100) PRIMARY KEY, tags text[]);
INSERT INTO pets(pet, tags)
    VALUES ('dog', '{big, furry, friendly, eats steak}'::text[]),
        ('cat', '{small, snob, eats greenbeans, plays with mouse}'::text[]),
        ('mouse', '{very small, fits in pocket, eat peanuts, watches cat}'::text[]),
        ('fish', NULL);

If you do a cross join, you'll leave out fish because he's got no tags

SELECT pet, sort_order, tag
FROM pets, unnest(tags) 
    WITH ORDINALITY As f(tag, sort_order) ;
 pet  | sort_order |       tag
------+------------+------------------
dog   |          1 | big
dog   |          2 | furry
dog   |          3 | friendly
dog   |          4 | eats steak
cat   |          1 | small
cat   |          2 | snob
cat   |          3 | eats greenbeans
cat   |          4 | plays with mouse
mouse |          1 | very small
mouse |          2 | fits in pocket
mouse |          3 | eat peanuts
mouse |          4 | watches cat

In order to include pets that have no tags, you need to do a LEFT JOIN like so

SELECT pet, sort_order, tag
FROM pets LEFT JOIN 
    LATERAL unnest(tags) 
        WITH ORDINALITY As f(tag, sort_order) ON true;

And voila, all our pets are accounted for:

 pet  | sort_order |       tag
------+------------+------------------
dog   |          1 | big
dog   |          2 | furry
dog   |          3 | friendly
dog   |          4 | eats steak
cat   |          1 | small
cat   |          2 | snob
cat   |          3 | eats greenbeans
cat   |          4 | plays with mouse
mouse |          1 | very small
mouse |          2 | fits in pocket
mouse |          3 | eat peanuts
mouse |          4 | watches cat
fish  |            |

PostgreSQL Q & A

 

DELETE all data really fast with TRUNCATE TABLE CASCADE



Though it is a rare occurrence, we have had occasions where we need to purge ALL data from a table. Our preferred is the TRUNCATE TABLE approach because it's orders of magnitude faster than the DELETE FROM construct. You however can't use TRUNCATE TABLE unqualified, if the table you are truncating has foreign key references from other tables. In comes its extended form, the TRUNCATE TABLE .. CASCADE construct which was introduced in PostgreSQL 8.2, which will not only delete all data from the main table, but will CASCADE to all the referenced tables.

Here is a quick demonstration. Suppose you had two tables orders and order_items where the order_items table references the orders.order_id column. If you do this:

TRUNCATE TABLE orders;

You'll get notice

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "order_items" references "orders".
HINT:  Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

It doesn't matter if you have CASCADE UPDATE or DELETE rule in place, the CASCADE approach will still be able to purge all data in the referenced tables by changing your statement to:

TUNCATE TABLE orders CASCADE;

You'll get a notice of the form:

NOTICE:  truncate cascades to table "order_items"

PostgreSQL Q & A

 

Adding properties to existing JSON object with PLV8



Lately I've been experimenting with building semi-schemaless apps. These are apps where much of the data may never be used for reporting aside from story telling and also that as time goes by some of these may be revisited and converted to more structured fields for easier roll-up and reporting. For the front-end UI, I'm using AngularJS which naturally spits out data as JSON and can autobind to JSON data of any complexity. My stored functions in PostgreSQL take JSON blobs as inputs spit it out into various tables and throws the whole thing in a jsonb field for later consumption (it's a bit redundant). Similarly they return JSON back. One of the things I wanted to be able to do was take this jsonb blob and tack on additional properties from well-structured fields or even a whole set of data like sub recordsets to feed back to my app in JSON. While there are lots of functions in PostgreSQL 9.3/9.4 that can easily build json objects from records, aggregate rows, etc. I couldn't find a function that allowed me to just add a property to an existing JSON object, so I went to my tried and true old-pal PL/V8 for some comfort. Here is a quickie function I created in PL/V8 that did what I needed. Hopefully it will be of use to others or others might have other ideas of doing this that I missed.

ujson_add_prop function: Add/Update properties of a JSON object

I used PL/V8 1.4.3, binaries for windows users here. I settled on taking json and outputting json from this function instead of jsonb, because when I tried to swap out json with jsonb, I got back the same json object as what I put in. Not sure why.

So here is the function

CREATE OR REPLACE FUNCTION ujson_add_prop(param_o json, 
        param_key text, param_value anyelement) 
    RETURNS json AS $$
    var result = param_o;
    result[param_key] = param_value;
    return (result)
$$ LANGUAGE plv8 IMMUTABLE STRICT;

Taking the function for a test drive

-- just tack on taker name 
SELECT ujson_add_prop(e_form::json, 
    'Taker Name', first_name || ' ' || last_name)::jsonb
FROM survey;

Which outputs

           ujson_add_prop
-------------------------------------------------------------------------------
 {"qa": [{"Favorite Color": "red"}, {"Significant Other": "Minnie"}], 
 "Taker Name": "Micky Mouse"}
 {"qa": [{"Favorite Color": "white"}, {"Significant Other": "Mick"}], 
 "Taker Name": "Minnie Mouse"}
(2 rows)
-- since it's defined as anyelement you can do this too
SELECT ujson_add_prop(e_form::json, 'Header', h)
FROM survey , LATERAL (SELECT id, first_name,last_name) As h;

Which outputs:

    ujson_add_prop
-----------------------------------------------------------------------------
 {"qa":[{"Favorite Color":"red"},{"Significant Other":"Minnie"}],
 "Header":{"id": 1,"first_name":"Micky","last_name":"Mouse"}}
 {"qa":[{"Favorite Color":"white"},{"Significant Other":"Mick"}],
 "Header":{"id": 2,"first_name":"Minnie","last_name":"Mouse"}}

The function also doubles as a setter. If I had for example a key already defined called "Header" in root of my JSON object, it would replace the old value with the new value.

On a side-note, I also discovered that my other tried and true friend hstore is a great companion to JSON allowing me to easily remorph JSON arrays such as survey questions into key-value for easier querying. More on that later.