Re: json datatype and table bloat?

On Mon, Nov 4, 2013 at 8:31 PM, ajelinek@gmail.com <ajelinek@gmail.com> wrote:
>>>> Along the lines of the equality operator; I have ran into issues trying
> to
>>>> pivot a table/result set with a json type due what seemed to be no
>>>> equality
>>>> operator.
>>>
>>> For the curious, and also use-case considerations for development, would
>>> you
>>> be able to share what it is you are doing (and how) that combines full
>>> json
>>> documents with pivoting?
>>>
>>> Compound types holding source data for a pivot seems problematic since
>>> generally all the pivot components are single-valued and, for data, often
>>> numerical.
>
>>would also like to see this. json type has completely displaced
>>crosstab in my usage. I don't typically pivot json though: I pivot the
>>raw data then transform to json.  With limited exceptions I consider
>>storing json in actual table rows to be an anti-pattern (but it should
>>still work if you do it).
>
> I could not figure out what I was doing last month to reproduce this.  So  I
> did a small pivot poc, and it is erroring on the max function. So it is
> probably not the same issue. My guess is I tried the using the GREATEST
> function as a hail marry (which would not have worked) and got the following
> message; ERROR:  could not identify a comparison function for type json and
> then thought/hopped it was the same thing when reading the emails.
>
> CREATE TABLE bad_table_json(id int, detail_type text, details json);
> INSERT INTO bad_table_json values(1, 'a', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'b', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'c', '{"a":1}'::json);
>
> SELECT id
>       ,MAX(CASE WHEN detail_type = 'a' THEN details END) AS a
>       ,MAX(CASE WHEN detail_type = 'b' THEN details END) AS b
>       ,MAX(CASE WHEN detail_type = 'c' THEN details END) AS c
>   FROM bad_table_json
>  GROUP BY id

Aside: here's a way to do those type of things.  It's not faster
necessarily but seems cleaner to me.  This will bypass need for json
comparison.  IMMUTABLE plpgsql is generally the fastest way to
implement highly iterated trivial functions.

CREATE OR REPLACE FUNCTION PickInternal(State anyelement, WantValue
TEXT, PickValue TEXT, Value anyelement)
  RETURNS anyelement AS
$$
BEGIN
  RETURN CASE WHEN WantValue = PickValue THEN Value ELSE State END;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE AGGREGATE Pick(TEXT, TEXT, anyelement) (
  SFUNC=PickInternal,
  SType=anyelement);

SELECT id
      ,Pick('a', detail_type, details) AS a
      ,Pick('b', detail_type, details) AS b
      ,Pick('c', detail_type, details) AS c
  FROM bad_table_json
  GROUP BY id;

merlin