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
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:
&
Thanks, now I see the difference
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
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
Oops, I am sorry for the formatting - Mac + Terminal + Gmail :-/
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
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 (
s/ I can trust / I can't trust /
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
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
Thank your for the comments
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
s/no it works/now it works/
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,
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
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
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',
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
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
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;
>
>
>
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":
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
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
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
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
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,
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
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
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,
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
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(
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) =
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
>>
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
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
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:
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
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
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:
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
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
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
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,
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
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 (
> >
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
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
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)
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
>
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
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:
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)
>
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
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
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
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_
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
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
Hello,
in PostgreSQL 10.8 the following works -
words_ru=> SELECT ARRAY[
words_ru-> '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А',
words_ru-> 'Б', 'Б', 'В', 'В', 'В', 'В', 'Г', 'Г', 'Д', 'Д',
words_ru-> 'Д', 'Д', 'Е', 'Е', 'Е', 'Е', 'Е',
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
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
Thanks for your replies!
Tom has hinted that STRICT is pl/pgSQL syntax and not SQL
Regards
Alex
>
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
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
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):
> >
> >
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
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?
>
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
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
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
>
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
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,
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$
Thank you!
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
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
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
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!
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
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
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,
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
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
I think I will try this approach:
\set localized_declaration `sed 's/this/that/' my_func.sql`
:localized_declaration
Thank you for your input
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 |
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
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
Or is it possible to call external commands from an sql script, like
\i "sed 's/this/that/' some.sql"
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');
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
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:
>>
>>&
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
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
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
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)
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
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,
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
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 - 100 of 138 matches
Mail list logo