ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Hello, I am struggling with an UPSERT in PostgreSQL 10.3 and have prepared a simple test case showing my 2 problems (at http://sqlfiddle.com/#!17/7e929/13 and also below) - There is a two-player word game: CREATE TABLE players ( uid SERIAL PRIMARY KEY, name text NOT NULL ); CREATE

Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Even the simpler INSERT statement INSERT INTO stats(uid, single_q_left) SELECT player1, COUNT(*) FROM games WHERE hand1 = '{Q}' GROUP BY player1 ON CONFLICT(uid) DO NOTHING; produces the same error. On Sat, May 5, 2018 at 4:49 PM, Alexander Farber <alexander.far...@gmail.com > wrote: &

Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Thanks, now I see the difference

Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Hi Adrian, On Sat, May 5, 2018 at 7:03 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/05/2018 07:49 AM, Alexander Farber wrote: > >> http://sqlfiddle.com/#!17/7e929/13 >> >> There is a two-player word game: >> >> CREATE TABLE players ( &g

UPDATE from CTE syntax error

Good evening, I am struggling with the syntax, please help. This query with a CTE works ok: WITH extract_letters AS ( SELECT mid, STRING_AGG(x->>'letter', '') AS letters

Re: UPDATE from CTE syntax error

Oops, I am sorry for the formatting - Mac + Terminal + Gmail :-/

Re: UPDATE from CTE syntax error

On Sat, May 26, 2018 at 6:37 PM, Adrian Klaver wrote: > > > https://www.postgresql.org/docs/10/static/sql-update.html > "column_name > > The name of a column in the table named by table_name. The column name > can be qualified with a subfield name or array

Re: Same condition in the CTE and in the subsequent JOIN using it

Thank you for the insightful comments. Actually in my specific case I have managed to get rid of the (almost) same condition in the outer query: CREATE OR REPLACE FUNCTION words_stat_scores_2( in_social integer, in_sidtext ) RETURNS TABLE (

Re: Same condition in the CTE and in the subsequent JOIN using it

s/ I can trust / I can't trust /

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Hi Martin - On Wed, Feb 21, 2018 at 1:26 PM, Martin Moore wrote: > I’m no expert but I’d think it unlikely an index would be considered for a > table with only 100 rows in. Also I’m pretty sure only one index per table > is used, so you’d want to put state1 and state2

Selecting the most recent timestamptz

Good morning, there are these 2 records in a table: # select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where gid=10; played | mid | action | gid | uid ---+-++-+- 2018-02-19 14:42:08.46222+01 | 12 | play

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Thank your for the comments

Re: Selecting the most recent timestamptz

Hi Ken - On Thu, Feb 22, 2018 at 10:24 AM, Ken Tanzer <ken.tan...@gmail.com> wrote: > On Thu, Feb 22, 2018 at 1:09 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> Good morning, there are these 2 records in a table: >> >> # select m.pla

Re: Selecting the most recent timestamptz

s/no it works/now it works/

Re: Selecting the most recent timestamptz

Ahh, thank you Laurenz - On Thu, Feb 22, 2018 at 10:18 AM, Laurenz Albe wrote: > > You are only checking if there is a later timestamp *for the same "mid"*. > > Since the two rows have different "mid", they are not compared. > no it works - # select m.played, m.mid,

Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Hello, in a 2 player game I store all games in the following PostgreSQL 10.2 table: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, finished timestamptz, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL

Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later

Here is the current DESC of the table (I already use few joins): words=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default

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

Also got this nice suggestion at https://stackoverflow.com/q/48050127/165071 - SELECT COALESCE( json_object_agg( gid, array_to_json(y) ), '{}'::json) FROM( SELECT gid, array_agg( json_build_object( 'uid', uid, 'created',

5 USD for PostgreSQL books at PacktPub

Hello fellow PostgreSQL users, there is currently a sale for books at https://www.packtpub.com/tech/PostgreSQL I am not affiliated in any way with them, it is just a "heads up". For myself I have pre-ordered https://www.packtpub.com/big-data-and-business-intelligence/mastering-postgresql-10

Selecting a JSON object of arrays from a PostgreSQL table

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

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

Hi Ivan, On Mon, Jan 1, 2018 at 3:34 PM, Ivan E. Panchenko < i.panche...@postgrespro.ru> wrote: > > select json_object_agg(gid, y) from > ( > select gid, jsonb_agg(row_to_json(chat)) y > from chat > group by gid > ) x; > > >

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

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 < 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":

Extract elements from JSON array and return them as concatenated string

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

Re: Multiple records returned by a JOIN

Ok thanks, I guess I should switch to a SELECT UNION (first on uid = player1 and the uid = player2) and that will fix the CASE ... END for me. On Tue, Apr 10, 2018 at 6:01 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Apr 10, 2018 at 8:44 AM, Alexand

Re: Multiple records returned by a JOIN

Or actually I can not use SELECT UNION here, because then I only get 10 records of the condition uid = player1 and then nothing would be left for the other condition uid = player2

Multiple records returned by a JOIN

Good evening, in PostgreSQL 10.3 I have written the following custom function (trying to fetch 10 latest games played by a user): CREATE OR REPLACE FUNCTION words_stat_games( in_social integer, in_sidtext ) RETURNS TABLE ( out_gid

Re: Multiple records returned by a JOIN

Last night I have inexplicably missed 2 conditions /facepalm Now my JOIN works ok, without multiple records - CREATE OR REPLACE FUNCTION words_stat_games( in_social integer, in_sidtext ) RETURNS TABLE ( out_gidinteger,

Adding AVG to a JOIN

Hello, in PostgreSQL 10.3 I run the following query to find top 10 players with the best ELO rating: # SELECT u.elo, u.uid, s.given, s.photo FROM words_users u JOIN words_social s USING (uid) WHERE u.elo > 1500 -- take the most recent record from words_social

Re: Adding AVG to a JOIN

Thank you for the explanation. I have rearranged my query and it works now (surprisingly fast too) - On Mon, Apr 23, 2018 at 9:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber < > alexander.far...@g

Same condition in the CTE and in the subsequent JOIN using it

Good evening, I have written a custom function which works, but wonder if using same condition twice looks suspicious and can be optimized. Here is calling my function, it returns average score / day and average time between moves / day: # select * from words_stat_scores(1,

Re: STRING_AGG and GROUP BY

Hi David - On Fri, Mar 16, 2018 at 4:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​First reaction is to ARRAY_AGG(DISTINCT x) and then write a function that > converts ​that array into a string by extracting 'letter' from each cell in > the array. > > Thinking it over a bit

You might be able to move the set-returning function into a LATERAL FROM item.

Good morning, for a word game in PostgreSQL 10.3 I have a table with jsonb column "tiles". The column holds either a JSON array of objects (word tiles played) or a string (of swapped letters). I am trying to fetch a history/protocol of a game with: CREATE OR REPLACE FUNCTION words_get_moves(

Re: You might be able to move the set-returning function into a LATERAL FROM item.

Thank you - On Tue, Mar 20, 2018 at 3:27 PM, Tom Lane wrote: > > I think you could push the conditionality into a plpgsql function, > something like (untested) > > create function jsonb_elements_if_array(j jsonb) returns setof jsonb as $$ > begin > if jsonb_typeof(j) =

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Good evening - On Fri, Mar 2, 2018 at 7:11 PM, Adrian Klaver wrote: > >> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind >> : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS >> apns, out_adm AS adm, out_body AS body FROM >>

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

# select * from words_moves where gid=609 limit 3; -[ RECORD 1

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Oops, I've got strings there too - when swapping instead of playing tiles: # select * from words_moves where gid=609 and action <> 'play'; mid | action | gid | uid |played | tiles | score

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

And here is how I call my stored function - https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914 - why wouldn't it store a jsonb array in the tiles column of words_moves table? 2018-03-02 15:29:42.644 CET [16693] LOG: statement: DISCARD ALL 2018-03-02 15:29:42.644 CET [16693] LOG:

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Hi Adrian, I 100% agree that nobody except me should debug my huge stored function, but if you look at my PostgreSQL 10.3 log - On Fri, Mar 2, 2018 at 3:31 PM, Adrian Klaver wrote: > > The little gray cells are not awake enough to work through the below:) If > it

jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Good afternoon, in PostgreSQL 10.3 I have the following table with a jsonb column: # \d words_moves; Table "public.words_moves" Column | Type | Collation | Nullable | Default

Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

Hi Adrian, thank you for the reply - On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 03/02/2018 05:52 AM, Alexander Farber wrote: > >> >> in PostgreSQL 10.3 I have the following table with a jsonb column:

SELECT UNION into a text[]

Good evening, I am trying to SELECT ARRAY_AGG into an array from 2 tables. But unfortunately here is what I get in PostgreSQL 10.5: SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE added > TO_TIMESTAMP(1539100913) UNION SELECT ARRAY_AGG(hashed) FROM

Adding LEFT JOIN to a query has increased execution time 10 times

Good evening, On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-server-10.6-1PGDG.rhel7.x86_64 pgbouncer-1.9.0-1.rhel7.x86_64

Re: Adding LEFT JOIN to a query has increased execution time 10 times

Oh ok, so it is not as simple as eliminating all "Seq Scan" occurrences... Thank you for replying Andrew - On Sat, Jan 5, 2019 at 9:18 PM Andrew Gierth wrote: That seems slow in itself, even before adding the extra join - the > explain suggests that you're both short on indexes and you're

Re: Adding LEFT JOIN to a query has increased execution time 10 times

Good evening, thank you for the useful hints! With the further help of the IRC folks the query has been optimized (7-10 seconds -> 0.3 second) by adding the following indices: CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY')); CREATE INDEX ON words_games (player2,

aggregate functions are not allowed in UPDATE

Good evening, I have prepared a simple test case for my question: https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0 There I create 3 tables: CREATE TABLE users ( uid SERIAL PRIMARY KEY, avg_time TEXT ); CREATE TABLE games ( gid SERIAL PRIMARY KEY, player1 INTEGER NOT NULL REFERENCES

Re: aggregate functions are not allowed in UPDATE

Unfortunately, I don't understand your advice, David - On Tue, Jan 15, 2019 at 5:46 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber > wrote: > > When I am trying > > > > WITH diffs AS ( > >

Re: aggregate functions are not allowed in UPDATE

Thank you, the following seems to have worked - On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > UPDATE users > SET avg_time = diffs.average_time_for_the_grouped_by_user > FROM diffs > WHERE users.uid = diffs.uid --< the missing "where" I commented about

Re: aggregate functions are not allowed in UPDATE

Ahh, the subqueries - On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber > wrote: > >> So calculate the average somewhere else, put the result in a column, > >> and then re

Re: aggregate functions are not allowed in UPDATE

Last question please - how to run the query for all users? I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way? How to refer to the outside "uid" from inside the CTE in the query below? WITH diffs AS ( SELECT gid, uid, played - LAG(played)

Re: aggregate functions are not allowed in UPDATE

And I should better change the avg_time column from TEXT to TIMESTAMPTZ (and use TO_CHAR on it later down the road) so that I can compare my players Regards Alex >

Re: Not sure which part of the query needs optimization

Thank you for the comments, Andrew - On Mon, Jan 7, 2019 at 8:40 PM Andrew Gierth wrote: > The obvious thing to do is to keep a computed average score for each > user - either in a separate table which you update based on changes to > words_moves, which you could do with a trigger, or using a

Not sure which part of the query needs optimization

Good afternoon, for each visitor of my website I generate a JSON list of 30 top players ( https://slova.de/words/top.php ), who played in the past week, with their average scores and average time between moves. With 5 seconds this query is taking quite a bit of time:

Re: Adding LEFT JOIN to a query has increased execution time 10 times

Hi Andrew - On Mon, Jan 7, 2019 at 12:00 AM Andrew Gierth wrote: > >>>>> "Alexander" == Alexander Farber writes: > Alexander> With the further help of the IRC folks the query has been > Alexander> optimized (7-10 seconds -> 0.3 second) >

How to always run UPDATE FROM despite missing records in the source table?

Good afternoon I have prepared a simplified test case for my question: https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0 In PostgreSQL 10.6 there are 2 tables: CREATE TABLE users ( uid SERIAL PRIMARY KEY, created timestamptz NOT NULL, visited timestamptz

Re: How to always run UPDATE FROM despite missing records in the source table?

Thank you Adrian - On Fri, Jan 11, 2019 at 4:55 PM Adrian Klaver wrote: > On 1/11/19 4:50 AM, Alexander Farber wrote: > > https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0 > > Why not put a test for the block in the function and then use different > UPDATE's depending o

Re: How to return ARRAY from SQL function?

Thank you, Laurenz and Tom - On Fri, Jun 14, 2019 at 3:25 PM Tom Lane wrote: > > Laurenz Albe writes: > > > You'll have to specify an array of which type you want, probably > > ... RETURNS text[] > > Right. Also, I don't recall the exact rules in this area, but I think > that SQL functions

Re: How to return ARRAY from SQL function?

Thank you - On Tue, Jun 18, 2019 at 3:10 PM Tom Lane wrote: > Alexander Farber writes: > > And then I shuffle the letters by - > > > CREATE OR REPLACE FUNCTION words_shuffle(in_array text[]) > > RETURNS text[] AS > > $func$ > > SELECT array_

Azure Database for PostgreSQL - how to add pgbouncer or similar?

Good morning, in Microsoft Azure how to add pgbouncer or a similar software enabling multiple simultaneous connections? After reading https://docs.microsoft.com/en-us/azure/postgresql/overview I could not find such options... Thank you Alex

Re: Azure Database for PostgreSQL - how to add pgbouncer or similar?

Thank you Adrian - On Sat, Jun 29, 2019 at 3:59 PM Adrian Klaver wrote: > On 6/29/19 12:34 AM, Alexander Farber wrote: > > Not from within. Supported extensions: > > https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions > > Using search term 'azure postgre

How to return ARRAY from SQL function?

Hello, in PostgreSQL 10.8 the following works - words_ru=> SELECT ARRAY[ words_ru-> '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А', words_ru-> 'Б', 'Б', 'В', 'В', 'В', 'В', 'Г', 'Г', 'Д', 'Д', words_ru-> 'Д', 'Д', 'Е', 'Е', 'Е', 'Е', 'Е',

Syntax error for UPDATE ... RETURNING INTO STRICT

Good morning, why does not PostgreSQL 10.11 please like the - CREATE OR REPLACE FUNCTION words_toggle_puzzle( in_mid bigint ) RETURNS table ( out_puzzle boolean ) AS $func$ UPDATE words_moves SET puzzle = NOT puzzle

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

Thank you Patrick - On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE wrote: > > It seems that STRICT is the issue. > > But why does your function return a table of boolean in this case ? > > As it only updates one record, it would probably be easier to return a > boolean only. > > CREATE OR

Re: Syntax error for UPDATE ... RETURNING INTO STRICT

Thanks for your replies! Tom has hinted that STRICT is pl/pgSQL syntax and not SQL Regards Alex >

CREATE INDEX ON words_moves(length(letters), score) WHERE action = 'play'

Good evening, in PostgreSQL 11 I have a table holding player moves (could be: 'play', 'swap', 'skip', ...) in a word game: # \d words_moves; Table "public.words_moves" Column | Type | Collation | Nullable | Default

Calling jsonb_array_elements 4 times in the same query

Hello, good afternoon! With PostgreSQL 10 I host a word game, which stores player moves as a JSON array of objects with properties: col, row, value, letter - CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY, action text NOT NULL, gid integer NOT NULL

Re: Calling jsonb_array_elements 4 times in the same query

Thank you Thomas - On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer wrote: > Alexander Farber schrieb am 21.10.2019 um 15:39: > > I am trying to construct a query, which would draw a game board when > given a move id (aka mid): > > > >

Re: Calling jsonb_array_elements 4 times in the same query

Apologies, I should have shown the JSON structure in my very first email - On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer wrote: > Use ->> to return the value as text (not as JSONB) and you need to use the > column alias, not the table alias: > > (t.tile ->> 'col')::int > > It is a

Re: Calling jsonb_array_elements 4 times in the same query

Thank you - On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver wrote: > As Thomas pointed there is a difference between -> and ->>: > > test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0 > -> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one'; > pg_typeof | ?column? >

Trying to fetch records only if preceded by at least another one

Good evening, for a word game hosted on PostgreSQL 10 I try to find interesting player moves (high score or played all 7 tiles) and generate a "puzzle" images out of them (example: https://imgur.com/a/StnXqoR ) The moves are stored in: words_ru=> \d words_moves

Re: Trying to fetch records only if preceded by at least another one

Thank you Michael - On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis wrote: > But it prints too many records: all moves cross-multiplied with each other. >> >> As if I have forgotten to add 1 more condition to the JOIN LATERAL >> > > LIMIT 1 inside your lateral should resolve that. Personally, I'd

Re: Counting booleans in GROUP BY sections

Thank you Adrian, but - On Fri, Nov 29, 2019 at 6:45 PM Adrian Klaver wrote: > On 11/29/19 8:38 AM, Alexander Farber wrote: > > > > CREATE OR REPLACE FUNCTION words_list_puzzles( > > in_start interval, > > in_end interval >

Re: Counting booleans in GROUP BY sections

My context is that I have a table of player moves with PK mid (aka "move id"). And I am able to find "interesting" moves by the high score or all 7 letter tiles used. But I do some human reviewing and set a "puzzle" boolean for truly interesting moves. For the reviewing tool I would like to

Counting booleans in GROUP BY sections

Good evening, I am trying to count the booleans per each GROUP BY section by the following stored function: CREATE OR REPLACE FUNCTION words_list_puzzles( in_start interval, in_end interval ) RETURNS TABLE ( out_label text,

How to call JSONB_INSERT with integer as the new to-be-inserted value?

Good evening, I am trying to take a JSONB object (comes from an HTTP cookie set by my app) and add a property "uid" to it, which should hold an integer: CREATE OR REPLACE FUNCTION words_get_user( in_users jsonb, OUT out_user jsonb ) RETURNS jsonb AS $func$

Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

Thank you!

Deleting takes days, should I add some index?

Hello, I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I have the following 2 tables there: words_ru=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default

Re: SELECT but only if not present in another table

Thank you, Steve - On Sun, Dec 6, 2020 at 6:50 PM Steve Baldwin wrote: > Can't you just use table aliases? So, the outer word_moves would become > 'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the > where clause 'WHERE wp.mid = wm.mid' ? > table aliases have worked

SELECT but only if not present in another table

Good evening, in PostgreSQL 13.1 I save player moves in the table: # \d words_moves Table "public.words_moves" Column | Type | Collation | Nullable | Default

Re: Using a boolean column with IF / THEN

On Sat, Dec 5, 2020 at 9:00 PM David G. Johnston wrote: > Maybe not “simpler” but for all those checks you could write a single > query that pulls out all the data at once into a record variable and test > against the columns pf that instead of executing multiple queries. > Thank you!

Using a boolean column with IF / THEN

Good evening, hopefully my question is not too stupid, but - in a 13.1 database I have a words_users table with a boolean column: -- the user is not allowed to chat or change the motto muted boolean NOT NULL DEFAULT false, Currently I check the value as follows, but I

Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston wrote: > Subqueries must be enclosed in parentheses. The parentheses that are part > of the function call do not count. > > Ah! Thank you David, this has worked now - CREATE OR REPLACE FUNCTION words_solve_puzzle( in_mid

Select a column and then apply JSONB_ARRAY_ELEMENTS to it

Hello, for a word puzzle using PostgreSQL 13.1: https://wortefarbers.de/ws/puzzle2?mid=138=c6f469786df7e8d44461381b62b2ce7d I am trying to improve a stored function - CREATE OR REPLACE FUNCTION words_solve_puzzle( in_mid bigint, in_uid int,

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

Tom, you are so eagle eyed - On Mon, Mar 8, 2021 at 8:53 PM Tom Lane wrote: > Alexander Farber writes: > > However there are cases, when I only have the out_gid value, I do not > want > > to return any other values. > > My question is: do I have to set the other OUT p

Localizing stored functions by replacing placeholders in their body

Hello, I have an app using PostgreSQL 13.2, in 6 different human languages (each using different database, but same source code). Currently to localize strings return/set by the stored functions I either get localized strings from a table or maintain stored function source code in 6 different

Re: Localizing stored functions by replacing placeholders in their body

I think I will try this approach: \set localized_declaration `sed 's/this/that/' my_func.sql` :localized_declaration Thank you for your input

Re: Deleting takes days, should I add some index?

Hi Pavel, trying to follow your advice "You should check so all foreign keys have an index" I look at the table where I want to delete older records: # \d words_games Table "public.words_games" Column | Type | Collation | Nullable |

Re: Localizing stored functions by replacing placeholders in their body

Yes, good point about the '\$', thank you Tom. The reason I am trying not to use sed, is because I deploy my database by executing a single command: psql words_en < words_en.sql And the file words_en.sql has the contents: \i words_hash.sql \i words_all_letters.sql \i words_get_hint.sql \i

Re: Localizing stored functions by replacing placeholders in their body

Thank you for the \! hint, Pavel, didn't know about that! Is it possible to have a pure SQL solution? (To avoid having to install "sed" on my Win 10 PC) Maybe by using EXECUTE? EXECUTE REGEXP_REPLACE( $localize$ CREATE OR REPLACE FUNCTION my_func() RETURNS text AS $func$ SELECT

Re: Localizing stored functions by replacing placeholders in their body

Or is it possible to call external commands from an sql script, like \i "sed 's/this/that/' some.sql"

Re: Localizing stored functions by replacing placeholders in their body

Ah, I understand, that was the wrong EXECUTE, thank you. Another idea: can't I use \set command for my purpose of localizing stored functions? \set my_func_declaration `sed 's/this/that/' my_func.sql` But how to execute the declaration? I can only echo it with select (:'my_func_declaration');

Re: Deleting takes days, should I add some index?

Pavel, thank you for asking! I have put the anonymized dump of my database at: http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB download) The question is why does the command take days (when I tried last time): delete from words_games where created < now() - interval '12

Re: Deleting takes days, should I add some index?

Thank you, Pavel! I didn't even think about trying to "explain analyze" deletion of just 1 record - On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule wrote: > čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < >> alexander.far...@gmail.com> napsal: >> >>&

Re: Deleting takes days, should I add some index?

Thank you for the explanation, David On Thu, Feb 25, 2021 at 9:49 PM David Rowley wrote: > > Since your foreign keys perform a cascade delete on the tables > referencing the tables you're deleting from, any records in those > referencing tables will be deleted too. You must also look at those

Re: Deleting takes days, should I add some index?

Hello, revisiting an older mail on the too long deletion times (in PostgreSQL 13.2)... I have followed the advices here, thank you - On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge wrote: > Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera a > écrit : > >> On 2020-Nov-27, Alexand

Re: Localizing stored functions by replacing placeholders in their body

Thanks for your input Actually, yes, that is what I have right now a translate() like stored function, with format %s sometimes. But that is "at runtime" and I would like to have a "at compile time"/"deploy once and forget" solution, that is why I have asked about approaches for modifying the

Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

Good evening, in PostgreSQL 13.2 I have a custom stored function: CREATE OR REPLACE FUNCTION words_join_new_game( in_uid integer, in_bid integer ) RETURNS table ( -- the player to be notified (sometimes there is no such user)

How to return a jsonb list of lists (with integers)

Good evening, In 13.2 I have 3 SQL queries, which work well and return integer values. The values I feed to Google Charts (and currently I switch to Chart.js). Currently I use the queries by calling 3 different custom stored functions by my Java servlet. I would like to convert the functions

Re: How to return a jsonb list of lists (with integers)

On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis wrote: > Aggregate functions work on a single column to summarize many rows into > fewer rows. You seem to be wanting to combine multiple columns which would > be done by concatenation or array[column1,column2] or something like that. > Ah right,

Re: How to return a jsonb list of lists (with integers)

Thank you, David, with json_build_array() it works for a single query - SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1

Re: How to return a jsonb list of lists (with integers)

Hello, thank you for the helpful replies. I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists. And the custom stored function below works mostly well, except for a special case - CREATE OR REPLACE FUNCTION words_stat_charts(

  1   2   >