...

или внеочередные заметки


Список ресурсов для разработчиков PostgreSQL
trekking, Himalaya
obartunov
Очень непричесанный список ресурсов для тех, кто решил разрабатывать PostgreSQL на C. Надеюсь, что когда-нибудь дойдут руки ( или кто поможет ) довести его до приличного вида.


Не надо забывать, что в директории contrib лежат много интересных расширений, на которых можно "играться" без опаски сломать сильно постгрес.


Read more...Collapse )
Tags: ,

SQL/JSON in PostgreSQL is online now
trekking, Himalaya
obartunov
It is possible to play with future SQL/JSON features:

1. Web interface to Postgres 10 + SQL/JSON - http://sqlfiddle.postgrespro.ru/#!21/
2. SQL to play with - https://github.com/postgrespro/sqljson/blob/sqljson/src/test/regress/expected/sql_json.out
3. You may compile sql/json branch yourself - https://github.com/postgrespro/sqljson/

Also, we are working on SQL/JSON primer and need your help ! Please, let me know (obartunov@postgrespro.ru), if you want to help us writing the primer.
Tags: , ,

Full-Text Search of JSON[b] data in PostgreSQL
trekking, Himalaya
obartunov
I always thought that full-text search in json is overestimated feature of Oracle, but new SQL/JSON specification in SQL Standard 2016 changed my mind and I started to think about adding this to postgres. Artur Zakirov made a quick prototype, but since he was quite busy with other projects, I asked Dmitry Dolgov to work on this feature before feature freeze. The implementation is quite straightforward and Depesz already blogged about it, but there is one interesting moment - support of phrase search, so we should avoid an accident matching of phrase between values of different keys. This was solved by adding an artificial extra position on the boundary between values of different keys. In the example it's clear seen, that 'moscow' has position of 5 instead of 4.

select to_tsvector(jb) from (values ('
{
     "abstract": "It is a very long story about true and false",
     "title": "Peace and War",
     "publisher": "Moscow International house"
 }
'::json)) foo(jb);
                                       to_tsvector
------------------------------------------------------------------------------------------
'fals':10 'hous':18 'intern':17 'long':5 'moscow':16 'peac':12 'stori':6 'true':8 'war':14

Notice, also, that for jsonb we'll get quite a different result, because keys in jsonb are sorted.
select to_tsvector(jb) from (values ('
{
     "abstract": "It is a very long story about true and false",
     "title": "Peace and War",
     "publisher": "Moscow International house"
 }
'::jsonb)) foo(jb);
                                       to_tsvector
------------------------------------------------------------------------------------------
 'fals':14 'hous':18 'intern':17 'long':9 'moscow':16 'peac':1 'stori':10 'true':12 'war':3

Now, we can see, that phrase search is really works as expected:
select  phraseto_tsquery('english','war moscow') @@ to_tsvector(jb) from (values ('
{
     "abstract": "It is a very long story about true and false",
     "title": "Peace and War",
     "publisher": "Moscow International house"
 }
'::jsonb)) foo(jb);
 ?column?
----------
 f
(1 row)
select  phraseto_tsquery('english','moscow international') @@ to_tsvector(jb) from (values ('
{
     "abstract": "It is a very long story about true and false",
     "title": "Peace and War",
     "publisher": "Moscow International house"
 }
'::jsonb)) foo(jb);
 ?column?
----------
 t
(1 row)

I'd like to thank also Andrew Dunstan, who spent his time to review the patch during PGConf.US and committed to Postgres 10.


I think that for the first version this is ok, but in future versions I would like to see support of fts in specified part of json[b], kind of json_fts(json, 'jsonpath').

Tags: , ,

Json in Postgres - The present and Future
trekking, Himalaya
obartunov
My slides from my PGConf US 2017 "Json in Postgres - The present and Future" are available.

More examples are available here: https://github.com/postgrespro/sqljson/blob/sqljson/src/test/regress/sql/sql_json.sql
Tags: , ,

Friendly interface for Postgres95
trekking, Himalaya
obartunov
One of my first post to postgres community. Who now remember NCSA Mosaic ? Interesting,
that wdb-p95 is still working http://www.sai.msu.su/cgi-bin/wdb/sn/sn.sncat/form

From megera@sai.msu.su Mon Sep 11 12:06:07 1995
Date: Mon, 11 Sep 1995 12:06:06 +0400 (MSK DST)
From: "O.Bartunov" <megera@sai.msu.su>
X-Sender: megera@ra
To: postgres <postgres95@nobozo.cs.berkeley.edu>
Subject: Q: Friendly interface for Postgres95
Message-ID: <pine.sv4.3.91.950911113736.16173a-100000@ra>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: O
X-Status:

Hi,
now when postgres95 v.1.0 installed and passed all tests (without regex :-()
on my Sparc 10/51 (Solaris 2.4) I'd like to have some friendly
interface to generate forms, reports. My users prefer to have an access
to postgres95 from PC-box (Dos or Windows). I try to find something in
Internet but failed. Does anybody know the friendly way for communication
with postgres5 from unix, Xwindows, MS-windows ?
I installed wdb-p95  - WWW interface to postgres95. It looks great and
it's possible via Netscape/Mosaic to talk with postgres95 from MsWindows,
but I'm not sure whether wdb-p95 provide a possibility to insert/edit
data or not. Also I'd like to know how to convert data from postgres95
to another database and vice versa.
        Hope you're not tired,
                Oleg Bartunov

_____________________________________________________________________________
Oleg Bartunov - researcher, hostmaster
Sternberg Astronomical Institute, Moscow University
(095)939-16-83, oleg@sai.msu.su, Moscow, Russia
-----------------------------------------------------------------------------
Tags: , ,

Wildcard query for jsonb
trekking, Himalaya
obartunov
Imagine, you want to find some value in jsonb data , but the only thing you know is the name of the key. For example, shop aggregator, which combines different hierarchies of goods, where some keys may be on different places.

Consider this unstructured data of type jsonb and query 'find something with "color":"red"'(I used this example several years ago to illustrate jsquery).

              Table "public.js_test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 value  | jsonb   |           |          |


SELECT * FROM js_test;
 id |                                 value
----+-----------------------------------------------------------------------
  1 | [1, "a", true, {"b": "c", "f": false}]
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
  7 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "colr": "red"}
  8 | {"a": "blue", "t": [{"color": "green", "width": 100}]}
  9 | {"color": "green", "value": "red", "width": 100}
(9 rows)



Variant 1 - Standard SQL (pretty complex)

WITH RECURSIVE t (id,VALUE) AS (
    SELECT
        *
    FROM
        js_test
    UNION ALL (
            SELECT
                t.id,
                COALESCE (kv.value, e.value) AS VALUE
            FROM
                t
            LEFT JOIN LATERAL jsonb_each ( 
                CASE
                    WHEN jsonb_typeof (t.value) = 'object' THEN t.value
                    ELSE NULL
                END
            ) kv
        ON TRUE
        LEFT JOIN LATERAL jsonb_array_elements ( 
                CASE
                   WHEN jsonb_typeof (t.value) = 'array' THEN t.value
                   ELSE NULL
                END
        ) e 
        ON TRUE
    WHERE
        kv.value IS NOT NULL
        OR e.value IS NOT NULL)
)
SELECT js_test.*
FROM (
        SELECT
            id
        FROM
            t
        WHERE
            VALUE @> '{"color": "red"}'
        GROUP BY id
) x
JOIN js_test ON js_test.id = x.id;

 id |                                 value
----+-----------------------------------------------------------------------
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
(5 rows)



Variant 2: jsquery (elegant query, but not standard way)

SELECT * FROM js_test WHERE value @@ '*.color = "red"'::jsquery;



Variant 3: Use our extension (** for any level) of jsonpath specification to a new SQL/JSON from SQL-2016.

SELECT * FROM js_test WHERE JSON_EXISTS( value,'$.**.color ? (@ == "red")');
 id |                                 value
----+-----------------------------------------------------------------------
  2 | {"a": "blue", "t": [{"color": "red", "width": 100}]}
  3 | [{"color": "red", "width": 100}]
  4 | {"color": "red", "width": 100}
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
  6 | {"a": "blue", "t": [{"color": "blue", "width": 100}], "color": "red"}
(5 rows)
 id |                                value
----+----------------------------------------------------------------------
  5 | {"a": "blue", "t": [{"color": "red", "width": 100}], "color": "red"}
(1 row)


More general syntax includes specification of range of levels to search, for example:

SELECT * FROM js_test WHERE JSON_EXISTS( value,'$.**{0,}.color ? (@ == "red")');

Tags: , ,

Бегом на холм
trekking, Himalaya
obartunov
Спустившись с горы Масада мы сели в машину и поехали к Мертвому морю, где хотели немного покупаться. Буквально через пару минут я вдруг понял, что хочу залезть на те песчаники, которые видел сверху. Миша снял нас с функцией замедления как мы по-пацански забежали на песчаник. Место красивое и хорошее, было много радости у нас. Хотелось бы погоняться в тех местах. Видео получилось уж больно замедленным, хотелось бы его немного ускорить.

Tags: ,

json vs jsonb in one query
trekking, Himalaya
obartunov
PostgreSQL has two json data types - json (string) and jsonb (binary) data types. The differences between these data types can be revealed from this query.

SELECT j::json AS json, j::jsonb AS jsonb FROM 
(SELECT '{"cc":0, "aa":  2, "aa":1,"b":1}' AS j) AS foo;
               json               |           jsonb
----------------------------------+----------------------------
 {"cc":0, "aa":  2, "aa":1,"b":1} | {"b": 1, "aa": 1, "cc": 0}
(1 row)


* json: textual storage «as is»
* jsonb: no whitespaces
* jsonb: no duplicate keys, last key win
* jsonb: keys are sorted (by length, then by key name)

It's possible to write queries using SQL/JSON implementation:


SELECT JSON_OBJECT('cc' VALUE 0, 'aa':  2, 'aa':1,'b':1  RETURNING JSON) as json;
                  json
-----------------------------------------
 {"cc" : 0, "aa" : 2, "aa" : 1, "b" : 1}

SELECT JSON_OBJECT('cc':0, 'aa':  2, 'aa':1,'b':1  RETURNING JSONB) as jsonb;
           jsonb
----------------------------
 {"b": 1, "aa": 1, "cc": 0}

Tags: , ,

Full-Text Search in PostgreSQL. A Gentle Introduction.
trekking, Himalaya
obartunov
Исторический пост.

2007 год.


Read more...Collapse )
Tags: ,

Неожиданное видео из прошлого
trekking, Himalaya
obartunov
Вот так бывает, покупаешь камеру, играешься, а потом забываешь. А она тебя снимала. Камеру покупал для похода в Непале в 2011 году, видео обнаружил случайно. Интересно было посмотреть на себя через 6 лет.

Tags:

?

Log in

No account? Create an account