Re: Extract elements from JSON array and return them as concatenated string

[Ivan E. Panchenko]

Hi Alex,

SELECT  string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 
12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, 
"letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]'::json


) x;

Regards,

Ivan Panchenko
Postgres Professional
the Russian PostgreSQL Company

14.03.2018 19:27, Alexander Farber пишет:

Good afternoon,

A PostgreSQL 10.3 table contains JSON data like:

[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 
12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, 
"letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]


Please suggest, how to extract only the "letter" values and 
concatenate them to a string like "ABCD"?


I suppose at the end I should use the ARRAY_TO_STRING function, but 
which JSON function to use for extracting the "letter" values to an array?


I keep looking at 
https://www.postgresql.org/docs/10/static/functions-json.html but 
haven't found a good one yet


Thank you
Alex

Re: Extract elements from JSON array and return them as concatenated string

[Ivan E. Panchenko]

14.03.2018 20:02, Alexander Farber пишет:

Thank you, Ivan! I am trying to apply your suggestion to my table -

On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko 
mailto:i.panche...@postgrespro.ru>> wrote:


Hi Alex,

SELECT  string_agg(x->>'letter','') FROM json_array_elements(

'[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8,
"row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12,
"value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2,
"letter": "D"}]'::json

) x;


# select * from words_moves where gid=656 order by played desc limit 3;
 mid  | action | gid | uid  | played | 
tiles | score

--++-+--+---+--+---
 1353 | swap   | 656 |    7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР" 
| ¤
 1352 | play   | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col": 
4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 
3, "letter": "У"}]   |    19
 1351 | play   | 656 |    7 | 2018-03-14 16:38:48.132546+01 | [{"col": 
9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, 
"value": 2, "letter": "М"}] |    16

(3 rows)

by trying the following:

#  select string_agg(x->>'letter', ' ') from (select 
jsonb_array_elements(tiles) from words_moves where gid=656 and 
action='play' order by played desc limit 5) x;

ERROR:  42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
   ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

LOCATION:  op_error, parse_oper.c:728

I am probably missing something obvious?
Yes, here x is the alias for the record, not for the json field. So you 
need to write the query like


select string_agg(x->>'letter', ' ')
from (
   select jsonb_array_elements(tiles) x
   from words_moves
   where gid=656 and action='play'
   order by played desc limit 5
) y;



Regards
Alex



Regards,
Ivan

Re: PostgreSQL 13: native JavaScript Procedural Language support ?

[Ivan E. Panchenko]

On 26.03.2020 03:50, Bruce Momjian wrote:

On Wed, Mar 25, 2020 at 05:46:27PM +0200, Marius Andreiana wrote:

Thanks Tom, that makes sense. Appreciate your time to explain the context.

I'll followup with Heroku.

Also, I have heard PL/V8 is very hard to build for packagers (because of
changes by Google in the way V8 is packaged), which has decreased PL/V8
adoption.


True.

Btw, Nginx made their own JS implementation

https://nginx.org/en/docs/njs/index.html

It might be easier in integration than V8.

Re: How to do phrase search?

[Ivan E. Panchenko]

Hello,

On 10.07.2020 21:22, Anto Aravinth wrote:

Hello,

I have the following table:

so2, which has following column details:

​

|id, title, posts, body (tsvector). |

And I created the index on the following:

|"so2_pkey" PRIMARY KEY, btree (id)|

|"body" gin (body)|

​

And I wanted to query on my tsvector with the string: `Is it possible 
to toggle the visibility of an element`. I tried the following query:



If you want phrase search, use the phraseto_tsquery function, like

   phraseto_tsquery( 'Is it possible to toggle the visibility of an 
element')


or

   phraseto_tsquery( 'my_ts_config', 'Is it possible to toggle the 
visibility of an element')



​

|SELECT id, ts_rank(body, keywords) AS rank|

|FROM so2,|

|to_tsquery('Is & it & possible & to & toggle & the & visibility & of 
& an & element')|


|keywords|

|WHERE keywords @@ body ORDER BY rank DESC;|

​

This give the result in which the "element" string count is large in 
number, however there is a record which exactly matches the search 
string, that comes as third in my result. So logically it just checked 
in my query which word count occurs the most across my body column, 
which I don't want.


​

Can I say somehow to postgresql, that I want the pharse search rather 
than the text spilt up weight in the document. Is it possible to do?


Edit: I tried with `|ts_rank_cd\|` as well. Same result.

Re: Incremental backup

[Ivan E. Panchenko]

On 28.10.2021 15:58, Ron wrote:

On 10/28/21 7:23 AM, Edu Gargiulo wrote:

Hi all,

Is there any way to make incremental backups in postgres-12?


Using pg_dump?  No.
Using pgBackRest?  Yes.


https://github.com/postgrespro/pg_probackup

--

Re: Counting the number of repeated phrases in a column

[Ivan E. Panchenko]

On 26.01.2022 11:11, Shaozhong SHI wrote:



On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko 
mailto:i.panche...@postgrespro.ru>> wrote:



On 26.01.2022 00:21, benj@laposte.net
 wrote:
> Le 25/01/2022 à 18:10, Shaozhong SHI a écrit :
>> There is a short of a function in the standard Postgres to do the
>> following:
>>
>> It is easy to count the number of occurrence of words, but it is
>> rather difficult to count the number of occurrence of phrases.
>>
>> For instance:
>>
>> A cell of value:  'Hello World' means 1 occurrence a phrase.
>>
>> A cell of value: 'Hello World World Hello' means no occurrence
of any
>> repeated phrase.
>>
>> But, A cell of value: 'Hello World World Hello Hello World'
means 2
>> occurrences of 'Hello World'.
>>
>> 'The City of London, London' also has no occurrences of any
repeated
>> phrase.
>>
>> Anyone has got such a function to check out the number of
occurrence
>> of any repeated phrases?
>>
>> Regards,
>>
>> David
>
> Don't know if it's exactly what you want, but you can replace all
> occurence of the phrase in the text by empty string and compute the
> diff between the initial and the result and next divide by the
length
> of your phrase.
>
> Example :
> WITH x AS (SELECT 'toto like tata and toto like titi and toto like
> tutu' , 'toto like' phrase)
> SELECT (char_length(texte) - char_length(replace(texte, phrase,
'')))
> / char_length(phrase) AS nb_occurence
> FROM x
>
This works if the user knows the phrase. As far as I understood, the
phrase is not known, and user wants to count number of repeats of any
phrases.
Of course this can be done with recursive CTE. Split into words,
generate all phrases (AFAIK requires recursion), then group and count.

But probably in PL/Perl this could be done  more effectively.


Is there an example of using recursive CTE to split a text string into 
words?


Recursion is not needed for splitting into words. This can be done by 
regexp_split_to_table function.


But generation of all possible phrases from the given list of words 
probably requires recursion. On the first step the list of words becomes 
a list of a single-worded phrases. On each iteration then, you add the 
next word to each existing phrase, if it is possible (i.e. until the 
last word is reached).




Regards,

David


Regards,
Ivan

Re: update field in jsonb

[Ivan E. Panchenko]

23.11.2017 04:45, support-tiger пишет:
is there a way to update a single field in jsonb without replacing the 
entire json document - couldn't find an example


for example

create table test (id primary key, data jsonb);

insert into test ({"name":"bill", "age":29});

 ?? update test   set data->age = 30


When a record in PostgeSQL is UPDATEd, its new version is created. So 
such partial JSON update would be not more than some syntax sugar. That 
is why it is not yet implemented, though plans for that exist.


Now you have to do something like:

UPDATE test SET data = jsonb_set(data, ARRAY['age'], to_jsonb(30)) WHERE ..

Re: jsonb and where clause?

[Ivan E. Panchenko]

Hi Bjorn,

28.11.2017 11:18, Bjorn T Johansen пишет:

Hi.

Just starting to look at how to use jsonb columns and I have a question. I have 
found out that I can use the following to search for a value inside
the jsonb column:

select * from orders where info ->> 'customer' = 'John Doe'(where info is 
the jsonb column)


But what if the jsonb column contains an json array, how can I search then?

info -> [ { "customer" : "John Doe" } ]


If you know the index in array, you can search like
info->0->>'customer'
If you want to search in any array element, you need to use JSQUERY 
extension,

see https://github.com/postgrespro/jsquery




btw, using PostgreSQL 9.6 but will be moving to 10 soon.


Regards,

BTJ


Regards,
Ivan

Re: jsonb and where clause?

[Ivan E. Panchenko]

28.11.2017 13:25, Bjorn T Johansen пишет:

On Tue, 28 Nov 2017 11:28:55 +0300
"Ivan E. Panchenko"  wrote:


Hi Bjorn,

28.11.2017 11:18, Bjorn T Johansen пишет:

Hi.

Just starting to look at how to use jsonb columns and I have a question. I have 
found out that I can use the following to search for a value
inside the jsonb column:

select * from orders where info ->> 'customer' = 'John Doe'(where info is 
the jsonb column)


But what if the jsonb column contains an json array, how can I search then?

info -> [ { "customer" : "John Doe" } ]

If you know the index in array, you can search like
info->0->>'customer'
If you want to search in any array element, you need to use JSQUERY
extension,
see https://github.com/postgrespro/jsquery



btw, using PostgreSQL 9.6 but will be moving to 10 soon.


Regards,

BTJ
  

Regards,
Ivan


Thx... :)

btw, just managed to use the following sql:

select * from orders where info @> '{"recs": [{ "customer":"John Doe"}]}'

(changed my json string to info -> { "recs: [ { "customer" : "John Doe" } ] }

And this seems to work but is this the "wrong" way of doing it or?

Yes, definitely it works, and is be supported by GIN index.

Nevertheless, I recommend you to have a look at JSQUERY which allows 
more complex queries, also with index support.



BTJ


Regards,
Ivan

Re: Selecting a JSON object of arrays from a PostgreSQL table

[Ivan E. Panchenko]

Hi Alex!

Why not:

select json_object_agg(gid, y) from
    (
 select gid, jsonb_agg(row_to_json(chat)) y
 from chat
 group by gid
    ) x;


Regards,

Ivan

01.01.2018 16:41, Alexander Farber пишет:

Hello and happy new year!

I have prepared a simple SQL Fiddle demonstrating my problem: 
http://sqlfiddle.com/#!17/2c9c5/1 


In a two-player game I store user chats in a table:

CREATE TABLE chat(
    gid integer,            /* game id */
    uid integer,            /* user id */
    created timestamptz,
    msg text
);

Here I fill the table with a simple test data:

INSERT INTO chat(gid, uid, created, msg) VALUES
    (10, 1, NOW() + interval '1 min', 'msg 1'),
    (10, 2, NOW() + interval '2 min', 'msg 2'),
    (10, 1, NOW() + interval '3 min', 'msg 3'),
    (10, 2, NOW() + interval '4 min', 'msg 4'),
    (10, 1, NOW() + interval '5 min', 'msg 5'),
    (10, 2, NOW() + interval '6 min', 'msg 6'),
    (20, 3, NOW() + interval '7 min', 'msg 7'),
    (20, 4, NOW() + interval '8 min', 'msg 8'),
    (20, 4, NOW() + interval '9 min', 'msg 9');

And can fetch the data by running the SELECT query:

SELECT ARRAY_TO_JSON(
  COALESCE(ARRAY_AGG(ROW_TO_JSON(x)),
           array[]::json[])) FROM (
SELECT
    gid,
    uid,
    EXTRACT(EPOCH FROM created)::int AS created,
    msg
FROM chat) x;

which returns me a JSON-array:

[{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
 {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
 {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
 {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
 {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
 {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
 {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
 {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
 {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]

However I would like to use "gid" as JSON object properties and the 
rest data as values in that object:


{"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
        {"uid":2,"created":1514813103,"msg":"msg 2"},
        {"uid":1,"created":1514813163,"msg":"msg 3"},
        {"uid":2,"created":1514813223,"msg":"msg 4"},
        {"uid":1,"created":1514813283,"msg":"msg 5"},
        {"uid":2,"created":1514813343,"msg":"msg 6"}],

 "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
        {"uid":4,"created":1514813463,"msg":"msg 8"},
        {"uid":4,"created":1514813523,"msg":"msg 9"}]}

Is that please doable by using the PostgreSQL JSON functions?

Thank you
Alex

Re: Mailing list archiver

[Ivan E. Panchenko]

Hi,

Btw, there is also another web interface to the mail list archives at 
Postgres Pro web site, https://postgrespro.com/list


Regards,

Ivan

 03.01.2018 05:45, Jordan Deitch пишет:

This project serves to organize and archive the postgres mailing list:

http://pg.rsa.pub/

Let me know what you think, or if you have any suggestions.
I left email addresses off the site to protect privacy.

p.s: you can click messages to collapse the tree

Thanks

Re: Slow alter sequence with PG10.1

[Ivan E. Panchenko]

I also confirm this problem:

Running Michael's script on 10.1 takes 314 seconds instead of 2.3 
seconds on 9.6.5.


Moreover adding some timing shows that on 10.1 the iteration execution 
time grows linearly with each iteration. (!!)


If we remove ALTER SEQUENCE, the difference is only 2.5 times (5 seconds 
for 10.1 and 2 - for 9.6.5), and the linear growth effect is not observed.


Removing advisory locks saves ~ 200ms in both cases, and still 9.6.5. 
seems faster.



Ivan Panchenko

Postgres Professional
the Russian PostgreSQL Company
+79104339846

22.01.2018 21:55, David G. Johnston пишет:
On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger 
mailto:michael@kruegers.email>>wrote:


Dear community,

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after
upgrading it from PG9.6.6. My application heavily uses sequences
and requires different increments of sequence numbers, e.g. a
range of 100, 1000 or 5000 numbers, so it is not possible to set a
fixed increment on a sequence that can be used by my application.

With PG10.1 the performance has dropped seriously so that my
application becomes unusable. After investigating different
aspects, I was able to isolate the issue to be related to the
sequences in Postgres 10.1.

Below shows a simple test script showing the problem:

​[...]​


On my computer I tried this code on PG9.6.6 and it executed in
roughly 3 seconds.
When running it on PG10.1 it takes over 7 minutes.

Further investigation showed that the problem is related to ALTER
SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without
providing a workaround or a way to switch to the old PG9.6
performance, at least I can't find anything in the documentation.

Is this a bug?


​Without testing/confirming I'd be inclined to agree that this is a 
regression for an unusual usage of sequences.  Work was done to make 
typical use cases of sequences more feature-full and it is quite 
possible the added effort involved hurts your specific scenario.  I'd 
expect a hacker to eventually pick this up, confirm the observation, 
and provide feedback.  This seems like sufficient amount of detail to 
get the ball rolling.


David J.

Re: How to Optimize pg_trgm Performance

[Ivan E. Panchenko]

Hi Igal,

29.01.2018 02:42, Igal @ Lucee.org пишет:


I want to use pg_trgm for auto-suggest functionality.  I created a 
Materialized View with the information that I need, with the relevant 
columns being (keywords text, rank int). keywords is the column from 
which I build the tri-grams, and rank is some popularity factor so 
that popular results will show up higher than less popular results 
given the same tri-gram distance.


I want to return results in the order of [distance], [distance_word], 
[rank].  The input comes from the user and is not known in advance.  
My query is as follows:


    SELECT title
    ,id
    ,(input <-> keywords) AS distance
        ,(input <<-> keywords) AS distance_word
    ,rank
    FROM  (VALUES (cast('red pill' AS text))) consts(input)
    ,mv_autosuggest
    ORDER BY 3, 4, 5
    LIMIT 20;

This gives me pretty good results, but it takes too long and is not 
likely to scale well.


I have created two indexes but neither seem to be used:

CREATE INDEX mv_autosuggest_keywords_tgrm_gist ON 
staging.mv_autosuggest USING gist (keywords gist_trgm_ops);


CREATE INDEX mv_autosuggest_keywords_tgrm_gin ON 
staging.mv_autosuggest USING gin (keywords gin_trgm_ops);


This is the result of explain analyze:

QUERY PLAN |
---|
Limit  (cost=356.41..356.46 rows=20 width=51) (actual 
time=163.132..163.135 rows=20 
loops=1)   |
  ->  Sort  (cost=356.41..372.96 rows=6619 width=51) (actual 
time=163.130..163.131 rows=20 
loops=1)    |
    Sort Key: (('red pill'::text <-> mv_autosuggest.keywords)), 
(('red pill'::text <<-> mv_autosuggest.keywords)), mv_autosuggest.rank |

    Sort Method: top-N heapsort  Memory: 28kB |
    ->  Seq Scan on mv_autosuggest  (cost=0.00..180.29 rows=6619 
width=51) (actual time=0.263..161.289 rows=6619 loops=1)  |

Planning time: 0.139 ms |
Execution time: 163.174 ms |

How can I improve the performance here?


This can be improved if you use sort only by distances
(try ORDER BY 3,4
or ORDER BY 3
or ORDER BY 4

Than you should get  plan  like

Index Scan using mv_autosuggest_keywords_tgrm_gist on mv_autosuggest
 Order By: ((keywords <-> 'red pill'::text) AND (keywords <->> 
'red pill'::text))


Which means that KNN  index search is enabled : 
http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf


If you want to sort it also by rank, you can make a two-level 
construction like:


SELECT * FROM (
   SELECT  ORDER BY 3,4 LIMIT   /* make some empirical 
redundant limit here */

) foo ORDER BY 5 LIMIT 


Regards,
Ivan Panchenko





Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org