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
+--+---+--+--
mid| bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid| integer | | not null |
uid| integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY (mid)
REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Here are some records (please pardon the non-english chars):
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]
mid| 1040
action | play
gid| 609
uid| 1192
played | 2018-03-02 10:13:57.943876+01
tiles | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 7,
"row": 7, "value": 3, "letter": "Я"}]
score | 10
-[ RECORD 2
]
mid| 1041
action | play
gid| 609
uid| 7
played | 2018-03-02 10:56:58.72503+01
tiles | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3,
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2,
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, {"col":
3, "row": 12, "value": 1, "letter": "А"}]
score | 14
-[ RECORD 3
]
mid| 1043
action | play
gid| 609
uid| 1192
played | 2018-03-02 11:03:58.614094+01
tiles | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1,
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1,
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
score | 13
I would like to get the length of the tiles array (because in my word game
7 played tiles mean +15 score bonus) - but that call fails for some reason:
# select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579
What am I doing wrong here please?
Regards
Alex
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
wrote:
> On 03/02/2018 05:52 AM, Alexander Farber wrote:
>
>>
>> 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
>> +--+---+--+-
>> -
>> mid| bigint | | not null |
>> nextval('words_moves_mid_seq'::regclass)
>> action | text | | not null |
>> gid| integer | | not null |
>> uid| integer | | not null |
>> played | timestamp with time zone | | not null |
>> tiles | jsonb| | |
>> score | integer | | |
>> Indexes:
>> "words_moves_pkey" PRIMARY KEY, btree (mid)
>> Check constraints:
>> "words_moves_score_check" CHECK (score >= 0)
>> Foreign-key constraints:
>> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
>> ON DELETE CASCADE
>> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
>> ON DELETE CASCADE
>> Referenced by:
>> TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>
>>
>> # select mid, jsonb_array_length(tiles) from words_moves where gid=609;
>> ERROR: 22023: cannot get array length of a scalar
>> LOCATION: jsonb_array_length, jsonfuncs.c:1579
>>
>> What am I doing wrong here please?
>>
>
> Are you sure all the values in tiles are correctly formatted because when
> I use jsonb_array_length with the provided data:
>
> test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2,
> "letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 5,
> "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 0,
> "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');
> jsonb_array_length
>
> 5
>
>
I fill that table with the following stored function (please pardon the
huge listing):
CREATE OR REPLACE FUNCTION words_play_game(
in_uid integer,
in_gid integer,
in_tiles jsonb
) RETURNS table (
out_uid integer, -- the player to be notified
out_fcm text,
out_apns text,
out_adm text,
out_body text
) AS
$func$
DECLARE
_tile jsonb;
_letter char;
_valueinteger;
_col integer;
_row integer;
_pos integer;
_mid bigint;
_totalinteger;
_hand_len integer;
_pile_len integer;
_move_len integer;
_pile char[];
_hand char[];
_letters char[][];
_values integer[][];
_opponent integer;
_finished timestamptz;
_reason text;
_score1 integer;
_score2 integer;
BEGIN
IF EXISTS (SELECT 1 FROM words_users
WHERE uid = in_uid AND
banned_until > CURRENT_TIMESTAMP) THEN
RAISE EXCEPTION 'User % is banned', in_uid;
END IF;
-- fetch the 4 arrays (_hand, _pile, _letters, _values) for the
current game
SELECT
hand1,
pile,
letters,
values
INTO
_hand,
_pile,
_letters,
_values
FROM words_games WHERE
gid = in_gid AND
player1 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is first player's turn
(played1 IS NULL OR played1 < played2);
IF NOT FOUND THEN
SELECT
hand2,
pile,
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: duration: 0.015 ms
2018-03-02 15:30:33.645 CET [16693] LOG: statement: select 1
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.094 ms
2018-03-02 15:30:33.645 CET [16693] LOG: statement: SET DateStyle='ISO';
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.050 ms
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.021 ms parse
: SET extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.003 ms bind
: SET extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG: execute : SET
extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.006 ms
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.007 ms parse
: SET application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.002 ms bind
: SET application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG: execute : SET
application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG: duration: 0.005 ms
2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.110 ms parse
: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns,
out_adm AS adm, out_body AS body FROM words_play_game($1::int, $2::int,
$3::jsonb)
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 words_play_game($1::int, $2::int,
$3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 =
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
2018-03-02 15:30:33.646 CET [16693] LOG: execute : SELECT
out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_adm AS adm,
out_body AS body FROM words_play_game($1::int, $2::int, $3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 =
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
2018-03-02 15:30:33.653 CET [16693] LOG: duration: 7.567 ms
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 where me I would first confirm there was malformed data by looking at
> the data itself. If there are not that many records for gid = 609 maybe a
> simple select of tiles would be sufficient. Otherwise maybe a simple
> plpgsql function that loops through the records applying jsonb_array_length
> and raising a notice on the error. In any case the point is to identify the
> presence of malformed data and if present the nature of the malformation.
> That would help reverse engineer any issues with below.
>
>
>> CREATE OR REPLACE FUNCTION words_play_game(
>> in_uid integer,
>> in_gid integer,
>> in_tiles jsonb
>> ) RETURNS table (
>> out_uid integer, -- the player to be notified
>> out_fcm text,
>> out_apns text,
>> out_adm text,
>> out_body text
>> ) AS
>>
>
2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.110 ms parse
: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns,
out_adm AS adm, out_body AS body FROM words_play_game($1::int, $2::int,
$3::jsonb)
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 words_play_game($1::int, $2::int,
$3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 = '[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter"
:"И","row":4,"value":1}]'
2018-03-02 15:30:33.646 CET [16693] LOG: execute : SELECT
out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_adm AS adm,
out_body AS body FROM words_play_game($1::int, $2::int, $3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 = '[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter"
:"И","row":4,"value":1}]'
I just pass as the 3rd argument in_tiles to my stored function:
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter"
:"И","row":4,"value":1}]'
and then take the in_tiles and store it unchanged in the words_moves table:
INSERT INTO words_moves (
action,
gid,
uid,
played,
tiles
) VALUES (
'play',
in_gid,
in_uid,
CURRENT_TIMESTAMP,
in_tiles
) RETURNING mid INTO STRICT _mid;
Does anybody happen to see what could I do wrong there?
Thank you for any hints
Alex
P.S: Here my stored fuinction:
https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
Here my table:
https://gist.github.com/afarber/06cc37114ff8dd14f05077f312904361
And here is how I call the stored function from Java:
String SQL_PLAY_GAME=
"SELECT " +
"out_uid AS uid, " +
"out_fcm AS fcm, " +
"out_apns AS apns, " +
"out_adm AS adm, " +
"out_body AS body " +
"FROM words_play_game(?::int, ?::int, ?::jsonb)";
private void handlePlay(int gid, String tiles) throws SQLException,
IOException {
LOG.info("handlePlay: {} -> {} {}", mUid, gid, tiles);
try (Connection db = DriverManager.getConnection(DATABASE_URL,
DATABASE_USER, DATABASE_PASS);
PreparedStatement st = db.prepareStatement(SQL_PLAY_GAME)) {
st.setInt(1, mUid);
st.setInt(2, gid);
st.setString(3, tiles);
runPlayerAction(st, gid);
}
}
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
>> words_play_game($1::int, $2::int, $3::jsonb)
>>
>> where is $3::jsonb coming from?
>>
>>
>> I was thinking more about the ultimate source of the data. The
> words_play_game() function, AFAICT, just passes the jsonb from input into
> the word_moves table. If that is the case the problem may occur further
> upstream where the jsonb array is actually built.
>
>
that argument comes over Websocket - either from my Android app,
or from my HTML5 game at https://slova.de/words/Words.js (just an array of
objects, stringified):
var tiles = [];
for (var i = boardTiles.length - 1;
i >= 0; i--) {
var tile = boardTiles[i];
tiles.push({
letter: tile.letter,
value: tile.value,
col:tile.col,
row:tile.row
});
}
var play = {
social: SOCIAL,
sid: SID,
auth:AUTH,
action: 'play',
gid: gid,
tiles: tiles
};
ws.send(JSON.stringify(play));
Regards
Alex
Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]
mid| 1040
action | play
gid| 609
uid| 1192
played | 2018-03-02 10:13:57.943876+01
tiles | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 7,
"row": 7, "value": 3, "letter": "Я"}]
score | 10
-[ RECORD 2
]
mid| 1041
action | play
gid| 609
uid| 7
played | 2018-03-02 10:56:58.72503+01
tiles | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3,
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2,
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, {"col":
3, "row": 12, "value": 1, "letter": "А"}]
score | 14
-[ RECORD 3
]
mid| 1043
action | play
gid| 609
uid| 1192
played | 2018-03-02 11:03:58.614094+01
tiles | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1,
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1,
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
score | 13
# select column_name, data_type from information_schema.columns where
table_name='words_moves';
column_name |data_type
-+--
mid | bigint
action | text
gid | integer
uid | integer
played | timestamp with time zone
tiles | jsonb
score | integer
(7 rows)
# select jsonb_array_length(tiles) from words_moves where gid=609 limit 3;
jsonb_array_length
5
5
4
(3 rows)
BUT:
# select jsonb_array_length(tiles) from words_moves where gid=609 ;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579
Which means only some data is bad, but how to find it please?
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 --++-+--+---+--+--- 1063 | swap | 609 | 1192 | 2018-03-02 14:13:24.684301+01 | "ТСНЦУЭ" | ¤ 1067 | swap | 609 | 1192 | 2018-03-02 15:31:14.378474+01 | "ЕЯУЕФП" | ¤ 1068 | swap | 609 |7 | 2018-03-02 15:52:07.629119+01 | "ОЕЕАУ" | ¤ 1072 | swap | 609 |7 | 2018-03-02 16:06:43.365012+01 | "ЕЕЫ"| ¤ 1076 | swap | 609 |7 | 2018-03-02 16:20:18.933948+01 | "Ъ" | ¤ (5 rows) So my problem was simple. I am sorry for the numerous mails! (it is just such a long chain - android/html5 - Jetty - PostgreSQL, so that sometimes I am confused. At least I am glad to have most of my logic as Pl/pgSQL right at the data) Regards Alex
Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar
I see, thank you for your comments, David and Adrian. In the "tiles" column actually save either the JSON array of tiles - when the user plays them Or a string (which is jsonb too) concatenated of letters - when the user swaps the letters. Maybe I should rethink my table structure (I just want to "log" all plays, swaps, skips, resigns in the words_moves table)... Or maybe I should always check for the "action" column first (acts as enum) - before accessing "tiles" column Regrads Alex
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 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
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": 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?
Regards
Alex
Re: Extract elements from JSON array and return them as concatenated string
Thank you - On Wed, Mar 14, 2018 at 8:41 PM, Ivan E. Panchenko < i.panche...@postgrespro.ru> wrote: > 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; > > This has worked perfectly: words=> select string_agg(x->>'letter', ' ') words-> from ( words(>select jsonb_array_elements(tiles) x words(>from words_moves words(>where gid=656 and action='play' words(>order by played desc limit 5 words(> ) y; string_agg А Н Т Щ П (1 row)
STRING_AGG and GROUP BY
Good afternoon,
I have prepared an SQL Fiddle for my question:
http://sqlfiddle.com/#!17/4ef8b/2
Here are my 4 test tables:
CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE
);
CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb NOT NULL
);
CREATE TABLE scores (
mid bigint NOT NULL REFERENCES moves ON DELETE CASCADE,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
wordtextNOT NULL CHECK(word ~ '^[A-Z]{2,}$'),
score integer NOT NULL CHECK(score >= 0)
);
Here they are filled with test data (two players Alice and Bob
interchangeably performing moves in game #1):
INSERT INTO players (name) VALUES ('Alice'), ('Bob');
INSERT INTO games (player1, player2) VALUES (1, 2);
INSERT INTO moves (uid, gid, played, tiles) VALUES
(1, 1, now() + interval '1 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "A"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '2 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "X"}, {"col": 8, "row": 12, "value": 10, "letter": "X"}, {"col":
9, "row": 12, "value": 1, "letter": "Z"}]
'::jsonb),
(1, 1, now() + interval '3 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "K"}, {"col": 8, "row": 12, "value": 10, "letter": "K"}, {"col":
9, "row": 12, "value": 1, "letter": "M"}, {"col": 10, "row": 12, "value":
2, "letter": "N"}]
'::jsonb),
(2, 1, now() + interval '4 min', '[]'::jsonb),
(1, 1, now() + interval '5 min', '[{"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"}]
'::jsonb),
(2, 1, now() + interval '6 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "P"}, {"col": 8, "row": 12, "value": 10, "letter": "P"}]
'::jsonb);
INSERT INTO scores (mid, uid, gid, word, score) VALUES
(1, 1, 1, 'AACD', 40),
(2, 2, 1, 'XXZ', 30),
(2, 2, 1, 'XAB', 30),
(3, 1, 1, 'KKMN', 40),
(3, 1, 1, 'KYZ', 30),
(5, 1, 1, 'ABCD', 40),
(6, 2, 1, 'PP', 20),
(6, 2, 1, 'PABCD', 50);
For a PHP-script which would display all moves+words+scores played in a
certain game I am trying:
SELECT
mid,
STRING_AGG(x->>'letter', '') AS tiles,
STRING_AGG(DISTINCT y, ', ') AS words
FROM (
SELECT
mid,
JSONB_ARRAY_ELEMENTS(m.tiles) AS x,
FORMAT('%s (%s)', s.word, s.score) AS y
FROM moves m
LEFT JOIN scores s
USING (mid)
WHERE m.gid = 1
) AS z
GROUP BY mid;
Which produces a slightly wrong result (the played letters are duplicated):
mid tileswords
1 AACD AACD (40)
2 XXZXXZ XAB (30), XXZ (30)
3 KKMNKKMN KKMN (40), KYZ (30)
5 ABCD ABCD (40)
6 PABCD (50), PP (20)
My expected result would actually be:
mid tileswords
1 AACD AACD (40)
2 XXZXAB (30), XXZ (30)
3 KKMNKKMN (40), KYZ (30)
5 ABCD ABCD (40)
6 PP PABCD (50), PP (20)
Is that actually possible by the means of SQL or should I do it in the PHP
script?
And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '') AS
tiles,
Because for example in the last move with mid=6 the player Bob had played 2
tiles, both with letter-value "P" and has formed 2 words (PP and PABCD),
but adding distinct would suggest he played a single tile "P".
Thank you
Alex
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 have two columns that both are aggregates but > that are otherwise independent of each other. Since they are independent > they cannot be aggregated at the same time. You need to write a two > subqueries, either in the target list or as separate from/join items, and > then join the already aggregated queries together on their common group by > column. > > The presence of DISTINCT here (and, IMO, generally), even if it worked, > would be an indicator that something is not quite right. > > thank you for confirming my feeling that DISTINCT is a bad indicator here... But you say that "tiles" and "word (score)" are unrelated and this does not seem true to me: For each move id aka "mid" there is a JSON value, describing how the player played the letter tiles. And for the same "mid" there is a list of one or more "word (score)"s achieved... Maybe it is possible to GROUP BY tiles somehow or some kind of special SQL JOIN? Regards Alex
Re: STRING_AGG and GROUP BY
Thank you, David -
On Fri, Mar 16, 2018 at 5:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> SELECT mid,
>>
> (SELECT string_agg(tiles, '; ') FROM tiles WHERE ta.mid = moves.mid) AS
> mid_tiles,
> (SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS
> mid_words
> FROM moves
>
> There are other ways to write that that could perform better but the idea
> holds.
>
>
I've come up with the following query, wonder if you meant something
similar -
http://sqlfiddle.com/#!17/4ef8b/48
WITH cte1 AS (
SELECT
mid,
STRING_AGG(x->>'letter', '') AS tiles
FROM (
SELECT
mid,
JSONB_ARRAY_ELEMENTS(m.tiles) AS x
FROM moves m
WHERE m.gid = 1
) AS z
GROUP BY mid),
cte2 AS (
SELECT
mid,
STRING_AGG(y, ', ') AS words
FROM (
SELECT
mid,
FORMAT('%s (%s)', s.word, s.score) AS y
FROM scores s
WHERE s.gid = 1
) AS z
GROUP BY mid)
SELECT mid, tiles, words
FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;
Regards
Alex
P.S. Below is the complete test data in case SQL Fiddle link stops working:
CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE
);
CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb NOT NULL
);
CREATE TABLE scores (
mid bigint NOT NULL REFERENCES moves ON DELETE CASCADE,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
wordtextNOT NULL CHECK(word ~ '^[A-Z]{2,}$'),
score integer NOT NULL CHECK(score >= 0)
);
INSERT INTO players (name) VALUES ('Alice'), ('Bob');
INSERT INTO games (player1, player2) VALUES (1, 2);
INSERT INTO moves (uid, gid, played, tiles) VALUES
(1, 1, now() + interval '1 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "A"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '2 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "X"}, {"col": 8, "row": 12, "value": 10, "letter": "X"}, {"col":
9, "row": 12, "value": 1, "letter": "Z"}]
'::jsonb),
(1, 1, now() + interval '3 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "K"}, {"col": 8, "row": 12, "value": 10, "letter": "K"}, {"col":
9, "row": 12, "value": 1, "letter": "M"}, {"col": 10, "row": 12, "value":
2, "letter": "N"}]
'::jsonb),
(2, 1, now() + interval '4 min', '[]'::jsonb),
(1, 1, now() + interval '5 min', '[{"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"}]
'::jsonb),
(2, 1, now() + interval '6 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "P"}, {"col": 8, "row": 12, "value": 10, "letter": "Q"}]
'::jsonb);
INSERT INTO scores (mid, uid, gid, word, score) VALUES
(1, 1, 1, 'AACD', 40),
(2, 2, 1, 'XXZ', 30),
(2, 2, 1, 'XAB', 30),
(3, 1, 1, 'KKMN', 40),
(3, 1, 1, 'KYZ', 30),
(5, 1, 1, 'ABCD', 40),
(6, 2, 1, 'PQ', 20),
(6, 2, 1, 'PABCD', 50);
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(
in_gidinteger
) RETURNS TABLE (
out_action text,
out_letters text,
out_words text
) AS
$func$
WITH cte1 AS (
SELECT
mid,
action,
STRING_AGG(x->>'letter', '') AS tiles
FROM (
SELECT
mid,
action,
CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN
JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x
--JSONB_ARRAY_ELEMENTS(tiles) AS x
FROM words_moves
WHERE gid = in_gid
--AND JSONB_TYPEOF(tiles) = 'array'
) AS p
GROUP BY mid, action),
cte2 AS (
SELECT
mid,
STRING_AGG(y, ', ') AS words
FROM (
SELECT
mid,
FORMAT('%s (%s)', word, score) AS y
FROM words_scores
WHERE gid = in_gid
) AS q
GROUP BY mid)
SELECT
action,
tiles,
words
FROM cte1
LEFT JOIN cte2 using (mid)
ORDER BY mid ASC;
$func$ LANGUAGE sql;
However calling this stored function gives the error:
ERROR: 0A000: set-returning functions are not allowed in CASE
LINE 18: ... CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRA...
^
HINT: You might be able to move the set-returning function into a LATERAL
FROM item.
I have read that PostgreSQL 10 handles SRF more strictly, but what does it
want me to do here, to add 1 more table to the LEFT JOIN?
Thank you
Alex
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) = 'array' then > return query select jsonb_array_elements(j); > end if; > end$$ > strict immutable language plpgsql; > > Note that this gives *no* elements, rather than a single NULL value, > if the input isn't an array --- but that seems to me to make more sense > than your existing code anyhow. If you disagree, add "else return next > null::jsonb". > I think I will just introduce a separate column (until now I was trying to squeeze 2 different kinds of data - JSON array of objects and a string - into the one column)... I believe Adrian had suggested it before :-)
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_gidinteger, out_reason text, out_state1 text, out_score1 integer, out_score2 integer ) AS $func$ SELECT g.gid, g.reason, CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END, CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END, CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END FROMwords_games g JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) WHERE g.finished IS NOT NULL ORDER BY g.finished DESC LIMIT 10; $func$ LANGUAGE sql STABLE; Unfortunately, it returns multiple records and with wrong values too: # select * from words_stat_games(1, '10999844041575271'); out_gid | out_reason | out_state1 | out_score1 | out_score2 -++++ 1978 | resigned | lost | 0 | 0 1978 | resigned | won| 0 | 0 1847 | resigned | lost |234 |441 1847 | resigned | won|441 |234 1847 | resigned | won|441 |234 1800 | expired| won| 41 | 0 1798 | expired| lost | 8 | 28 1798 | expired| won| 28 | 8 1800 | expired| lost | 0 | 41 1926 | expired| won| 35 | 13 (10 rows) Why does it return the game 1978 twice and also the out_state1 changes between 'lost' and 'won' values? I hoped to handle that with my "CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END", but it obviously fails Below are my 2 table definitions, thank you for any hints. CREATE TABLE words_social ( sid text NOT NULL, social integer NOT NULL CHECK (0 < social AND social <= 64), uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, PRIMARY KEY(sid, social) ) 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 CHECK (player1 <> player2), player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, reason text, -- regular, resigned, expired, banned state1 text, -- tie, winning, losing, draw, won, lost state2 text, -- tie, winning, losing, draw, won, lost score1 integer NOT NULL CHECK (score1 >= 0), score2 integer NOT NULL CHECK (score2 >= 0) ); CREATE INDEX words_games_state1_index ON words_games(state1); CREATE INDEX words_games_state2_index ON words_games(state2); CREATE INDEX words_games_reason_index ON words_games(reason);
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, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> Unfortunately, it returns multiple records and with wrong values too: >> >> # select * from words_stat_games(1, '10999844041575271'); >> out_gid | out_reason | out_state1 | out_score1 | out_score2 >> -++++ >> 1978 | resigned | lost | 0 | 0 >> 1978 | resigned | won| 0 | 0 >> 1847 | resigned | lost |234 |441 >> 1847 | resigned | won|441 |234 >> 1847 | resigned | won|441 |234 >> 1800 | expired| won| 41 | 0 >> 1798 | expired| lost | 8 | 28 >> 1798 | expired| won| 28 | 8 >> 1800 | expired| lost | 0 | 41 >> 1926 | expired| won| 35 | 13 >> (10 rows) >> >> Why does it return the game 1978 twice and also the out_state1 changes >> between 'lost' and 'won' values? >> >> > JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) > > I'm more surprised by the single and triple than the doubles. Your join > against social, which has a record for each user, and games which has two > users, should result in an output with two rows, one for each of the users > in the games row. One of those users wins, and one of them loses. How you > have 2 winners in 1847 I cannot tell without seeing data. Why there is no > loser for 1926 is likewise a mystery. > > I don't know why this triple is there, but it really is :-) Regards Alex
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
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, out_reason text, out_state1 text, out_score1 integer, out_score2 integer ) AS $func$ SELECT g.gid, g.reason, CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END, CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END, CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END FROMwords_games g JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid) WHERE g.finished IS NOT NULL AND s.social = in_social -- MISSED CONDITION AND s.sid = in_sid -- MISSED CONDITION ORDER BY g.finished DESC LIMIT 10; $func$ LANGUAGE sql STABLE;
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 (storing user details from
social networks)
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
AND action IN ('play', 'skip', 'swap', 'resign'))
ORDER BY u.elo DESC
LIMIT 10;
elo | uid | given |
photo
--+--+--+
2078 | 1201 | Roman|
https://lh6.googleusercontent.com/-kNp75NGW6wo/AAI/ABs/QN7rEc17JNc/photo.jpg
1952 | 2846 | дана |
https://avt-30.foto.mail.ru/mail/dance1011/_avatarbig?1523746018
1923 | 2808 | Ириша|
https://avt-24.foto.mail.ru/mail/irusy2277/_avatarbig?1518190793
1788 | 3479 | наталья | https://avt-5.foto.mail.ru/mail/leutan/_avatarbig
1749 | 3404 | ♕ OLGA ♕ |
https://avt-30.foto.mail.ru/mail/olgapinsk2/_avatarbig?1484081891
1733 | 3336 | Надежда |
https://avt-14.foto.mail.ru/mail/katerenyuk78/_avatarbig?1520366579
1724 | 1765 | ЕЛЕНА|
https://i.mycdn.me/image?id=805029440389&t=0&plc=API&ts=00&aid=1158060544&tkn=*looACpPtImwclHOmPBfnpuashFk
1717 | 3091 | андрей |
https://avt-11.foto.mail.ru/yandex.ru/maimun-11/_avatarbig?1453033064
1711 | 3000 | Алекс|
https://avt-20.foto.mail.ru/mail/taa113/_avatarbig?1495430756
1708 | 3991 | Кузнецов |
https://avt-10.foto.mail.ru/inbox/sobaka.58/_avatarbig?1353528572
(10 rows)
The above query works well and quick, but I need to add the average score
per move information to it.
Here is such a query for the best player
# SELECT AVG(score) FROM words_moves WHERE uid = 1201;
avg
-
18.4803525523319868
However I am not sure, how to "marry" the 2 queries?
I have tried to add words_moves through another JOIN, but that does not
work:
# SELECT
u.elo,
u.uid,
AVG(m.score),-- how to add the player average score?
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN words_moves m USING (uid)
WHERE u.elo > 1500
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
AND action IN ('play', 'skip', 'swap', 'resign'))
ORDER BY u.elo DESC
LIMIT 10
;
ERROR: 42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: u.elo,
^
Please give me some hints, how to approach this.
Thank you!
Alex
P.S: Below are the 3 tables referenced above:
# \d words_users
Table "public.words_users"
Column | Type | Collation | Nullable |
Default
---+--+---+--+--
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip| inet | | not null |
fcm | text | | |
apns | text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
banned_until | timestamp with time zone | | |
banned_reason | text | | |
elo | integer | | not null |
medals| integer | | not null |
coins | integer | | not null |
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_banned_reason_check" CHECK (length(banned_reason) > 0)
"words_users_elo_check" CHECK (elo >= 0)
"words_users_medals_check" CHECK (medals >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "
Re: Adding AVG to a JOIN
Thank you, Daniel -
On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite
wrote:
>
> You may use a correlated subquery in the SELECT clause,
> like this:
>
> SELECT
>u.elo,
>u.uid,
>(SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
>s.given,
>s.photo
>
this has worked great for me:
SELECT
u.elo,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS score,
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
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
week'
AND action IN ('play', 'skip', 'swap',
'resign'))
ORDER BY u.elo DESC
LIMIT 10
Is that what is called LATERAL JOIN?
Regards
Alex
Re: Adding AVG to a JOIN
Good evening,
On Mon, Apr 23, 2018 at 12:56 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite
> wrote:
>
>>
>> You may use a correlated subquery in the SELECT clause,
>>
>
>SELECT
> u.elo,
> (SELECT ROUND(AVG(score), 1) FROM words_moves
> WHERE uid = u.uid) AS score,
> 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
> AND NOT EXISTS (SELECT 1
> FROM words_social x
> WHERE s.uid = x.uid
> AND x.stamp > s.stamp)
> -- only show players who where active in the last week
> AND EXISTS (SELECT 1
> FROM words_moves
> WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
> week'
> AND action IN ('play', 'skip', 'swap',
> 'resign'))
> ORDER BY u.elo DESC
> LIMIT 10
>
thank you all for helping me in adding an average score per move to my SQL
JOIN.
Of course I would like to add yet another statistic and now am trying to
add the average time per move to the same query, by prepending it a CTE
with LAG():
WITH cte AS (
SELECT
m.gid,
m.uid,
m.played,
LAG(m.played) OVER (PARTITION BY m.gid
ORDER BY played) AS prev_played
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid AND m.uid in
(g.player1, g.player2))
WHERE m.played > CURRENT_TIMESTAMP - interval '1
month'
)
SELECT
u.elo,
AVG(c.played - c.prev_played) AS avg_time_per_move,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS score,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN cte c USING (uid)
WHERE u.elo > 1500
-- take the most recent record from words_social
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
week'
AND action IN ('play', 'skip', 'swap',
'resign'))
ORDER BY u.elo DESC
LIMIT 10;
but this fails with
ERROR: 42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 15: u.elo,
^
And I don't understand why adding a CTE has caused it, because without the
CTE the GROUP BY u.elo was not required...
Regards
Alex
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...@gmail.com> wrote:
>
>> SELECT
>> u.elo,
>> AVG(c.played - c.prev_played) AS
>> avg_time_per_move,
>> (SELECT ROUND(AVG(score), 1) FROM words_moves
>> WHERE uid = u.uid) AS score,
>>
>> And I don't understand why adding a CTE has caused it, because without
>> the CTE the GROUP BY u.elo was not required...
>>
>>
> Adding "AVG(c.played - c.prev_played)" directly to the top-level select
> statement column list is what turned it into a "GROUP BY" query. When you
> embedded the "AVG(score)" in a subquery the GROUP BY was limited to just
> that subquery, and it had no other columns besides the aggregate and so
> didn't require a GROUP BY clause.
>
>
WITH cte AS (
SELECT
m.gid,
m.uid,
m.played,
LAG(m.played) OVER (PARTITION BY m.gid
ORDER BY played) AS prev_played
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid AND m.uid in
(g.player1, g.player2))
WHERE m.played > CURRENT_TIMESTAMP - interval '1
month'
)
SELECT
u.elo,
(SELECT TO_CHAR(AVG(played - prev_played),
'HH24:MI') FROM cte WHERE uid = u.uid) AS avg_time,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS avg_score,
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
AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)
-- only show players who where active in the last week
AND EXISTS (SELECT 1
FROM words_moves
WHERE played > CURRENT_TIMESTAMP - INTERVAL '1
week'
AND action IN ('play', 'skip', 'swap',
'resign'))
ORDER BY u.elo DESC
LIMIT 10;
Best regards
Alex
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, '199928440415755383271');
out_day | out_diff | out_score
+--+---
26.03.2018 | 75 | 10.5
27.03.2018 |3 | 10.2
28.03.2018 | 324 | 17.8
29.03.2018 | 801 | 10.0
30.03.2018 | 12 | 19.5
31.03.2018 | 64 | 20.8
01.04.2018 | 48 | 12.3
02.04.2018 | 342 | 11.0
03.04.2018 | 12 | 14.5
04.04.2018 | 44 | 15.0
05.04.2018 | 116 | 13.6
06.04.2018 | 102 | 19.7
07.04.2018 | 54 | 14.8
08.04.2018 | 252 | 19.0
09.04.2018 | 272 | 10.4
10.04.2018 | 140 | 18.2
11.04.2018 | 41 | 11.4
12.04.2018 | 61 | 13.3
13.04.2018 | 182 | 15.3
14.04.2018 | 76 | 13.7
15.04.2018 | 199 | 20.1
16.04.2018 | 116 | 19.1
17.04.2018 | 390 | 20.1
18.04.2018 | 150 | 16.6
19.04.2018 | 448 | 15.9
20.04.2018 | 163 | 14.6
(26 rows)
And here is the function source code:
CREATE OR REPLACE FUNCTION words_stat_scores(
in_social integer,
in_sidtext
) RETURNS TABLE (
out_day text,
out_diff numeric,
out_score numeric
) AS
$func$
WITH cte AS (
SELECT
DATE_TRUNC('day', m.played) AS day,
m.mid,
EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
(PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
FROMwords_moves m
JOINwords_games g ON (m.gid = g.gid)
JOINwords_social s ON (s.uid IN (g.player1, g.player2))
WHERE s.social = in_social --
CAN THIS BE REFERRED TO FROM BELOW?
AND s.sid = in_sid
AND m.played > CURRENT_TIMESTAMP - interval '1 month'
)
SELECT
TO_CHAR(c.day, 'DD.MM.'),
ROUND(AVG(c.diff)),
ROUND(AVG(m.score), 1)
FROMwords_moves m
JOINcte c using(mid)
JOINwords_social s USING(uid)
WHERE s.social = in_social
AND s.sid = in_sid
AND m.action = 'play'
GROUP BY c.day
ORDER BY c.day;
$func$ LANGUAGE sql STABLE;
By looking at the above source code, do you think, that the condition being
used twice (the s.social = in_social AND s.sid = in_sid) is "too much" and
can be optimized? :-)
Thank you for any hints, I apologize if my question is too specific and
difficult to answer...
Regards
Alex
P.S. My 3 tables are below -
CREATE TABLE words_social (
sid text NOT NULL,
social integer NOT NULL CHECK (0 < social AND social <= 64),
given text NOT NULL CHECK (given ~ '\S'),
family text,
photo text CHECK (photo ~* '^https?://...'),
lat float,
lng float,
stamp integer NOT NULL,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action text NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
letters text,
score integer CHECK(score >= 0)
);
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 CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
reason text, -- regular, resigned, expired, banned
state1 text, -- tie, winning, losing, draw, won, lost
state2 text, -- tie, winning, losing, draw, won, lost
hint1 text,
hint2 text,
chat1 integer NOT NULL CHECK (chat1 >= 0),
chat2 integer NOT NULL CHECK (chat2 >= 0),
score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0),
hand1 char[7] NOT NULL,
hand2 char[7] NOT NULL,
pilechar[116] NOT NULL,
letters char[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE,
friendly boolean NOT NULL
);
CREATE INDEX words_games_state1_index ON
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 (
out_day text,
out_diff numeric,
out_score numeric
) AS
$func$
WITH filtered_moves AS (
SELECT
m.uid,
s.uid AS web_script_viewer,
DATE_TRUNC('day', m.played) AS day,
m.mid,
EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
(PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
FROMwords_moves m
JOINwords_games g ON (m.gid = g.gid)
JOINwords_social s ON (s.uid IN (g.player1, g.player2))
WHERE s.social = in_social
AND s.sid = in_sid
AND m.played > CURRENT_TIMESTAMP - interval '2 month'
)
SELECT
TO_CHAR(f.day, 'DD.MM.'),
ROUND(AVG(f.diff)),
ROUND(AVG(m.score), 1)
FROMwords_moves m
JOINfiltered_moves f using(mid)
WHERE f.uid = f.web_script_viewer -- INSTEAD OF DOING JOIN
ON words_social AGAIN
AND m.action = 'play'
GROUP BY f.day
ORDER BY f.day;
$func$ LANGUAGE sql STABLE;
The "big picture" of my database is that every player data can be referred
by the numeric "uid" (user id).
But when a user comes though a web script, then he must first authenticate
through words_social table, I can trust him just giving me some "uid".
(I suppose many databases have similar "authentication" table, storing
usernames/passwords)
And thus my question is about how to only authenticate once - and then
carry this result through several CTEs.
Regards
Alex
Re: Same condition in the CTE and in the subsequent JOIN using it
s/ I can trust / I can't trust /
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 TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE,
hand1 char[7] NOT NULL,
hand2 char[7] NOT NULL
);
INSERT INTO players (name) VALUES ('Alice'), ('Bob'), ('Carol');
INSERT INTO games (player1, player2, hand1, hand2) VALUES
(1, 2, '{A,B,C,D,E,F,G}', '{A,B,C,D,E,F,G}'),
(1, 3, '{}', '{Q}'),
(3, 2, '{A,Q}', '{A,B,C}'),
(1, 2, '{Q}', '{A,B,C,D,E,F,G}'),
(2, 3, '{Q}', '{A,B,C,D,E,F,G}'),
(2, 3, '{Q}', '{X,Y,Z}'),
(1, 2, '{Q}', '{A,B,C,D,E,F,G}');
I am trying to set up a daily cronjob, which would calculate player
statistics and store them into a table for faster access from web scripts:
CREATE TABLE stats (
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
single_q_left INTEGER NOT NULL DEFAULT 0
);
Here I have just one statistic: when a player has only the "difficult"
letter "Q" left in her hand.
Below I am trying to calculate such situations per user and store them into
the stats table:
INSERT INTO stats(uid, single_q_left)
SELECT player1, COUNT(*)
FROM games
WHERE hand1 = '{Q}'
GROUP BY player1
ON CONFLICT(uid) DO UPDATE SET
single_q_left = EXCLUDED.single_q_left;
Unfortunately, this gives me the error
"here is no unique or exclusion constraint matching the ON CONFLICT
specification"
and I can not understand it despite rereading
https://www.postgresql.org/docs/9.5/static/sql-insert.html
And my second problem is: the above query only calculates "half the
picture", when a player is stored in the player1 column.
How to add "the second half", when the player had a single Q left, while
she was player2?
Should I use SELECT UNION or maybe CASE WHEN ... END?
Thank you
Alex
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 wrote:
> http://sqlfiddle.com/#!17/7e929/13
>
>
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 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 ( >> uid SERIAL PRIMARY KEY, >> name text NOT NULL >> ); >> >> CREATE TABLE games ( >> gid SERIAL PRIMARY KEY, >> player1 integer NOT NULL REFERENCES players ON DELETE CASCADE, >> player2 integer NOT NULL REFERENCES players ON DELETE CASCADE, >> hand1 char[7] NOT NULL, >> hand2 char[7] NOT NULL >> ); >> >> CREATE TABLE stats ( >> uid integer NOT NULL REFERENCES players ON DELETE CASCADE, >> single_q_left INTEGER NOT NULL DEFAULT 0 >> ); >> >> >> The uid column in the stats table has neither a unique or exclusion > constraint on it. > > The uid column in stats refers to uid column in players, where it is PRIMARY KEY, why doesn't PostgreSQL "see" that? :-) Regards Alex
Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Thanks, now I see the difference
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 FROM ( SELECT mid, JSONB_ARRAY_ELEMENTS(tiles) AS x FROM words_moves WHERE action='play' ) z GROUP BY mid) SELECT * from extract_letters; mid | letters +- 12 | АКЖОЛ 15 | ДМО 16 | ТО 20 | ШГА 21 | КТИ 22 | ВОЗ 24 | АКПОНК But UPDATEing from the CTE does not - WITH extract_letters AS ( SELECT mid, STRING_AGG(x->>'letter', '') AS letters FROM ( SELECT mid, JSONB_ARRAY_ELEMENTS(tiles) AS x FROM words_moves WHERE action='play' ) z GROUP BY mid) UPDATE words_moves m SET m.letters = el.letters FROM extract_letters el WHERE m.mid = el.mid; ERROR: 42703: column "m" of relation "words_moves" does not exist LINE 14: SET m.letters = el.letters ^ Regards Alex
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 subscript, if needed. Do not > include the table's name in the specification of a target column — for > example, UPDATE table_name SET table_name.col = 1 is invalid. > " > > So it should be: > > SET letters = el.letters > > Thank you Adrian, this has worked
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 words_verbs
WHERE added > TO_TIMESTAMP(1539100913);
array_agg
-
{noun1,noun2,noun3}
{verb1,verb2}
And thus I can not assign it to the _added variable in my custom function:
CREATE OR REPLACE FUNCTION words_get_added(
in_visited integer,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_added text[];
BEGIN
-- create array with words added to dictionary since in_visited
timestamp
IF in_visited > 0 THEN
_added := (
SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE added > TO_TIMESTAMP(in_visited)
UNION
SELECT ARRAY_AGG(hashed)
FROM words_verbs
WHERE added > TO_TIMESTAMP(in_visited)
);
IF CARDINALITY(_added) > 0 THEN
out_json := jsonb_build_object('added', _added);
END IF;
END IF;
END
And the assignment results in the error message:
www java[1082]: org.postgresql.util.PSQLException: ERROR: more than one
row returned by a subquery used as an expression| Where: SQL statement
"SELECT (|SELECT ARRAY_AGG(hashed) |
FROM words_nouns |WHERE added >
TO_TIMESTAMP(in_visited)|UNION|
SELECT ARRAY_AGG(hashed) |FROM words_verbs |
WHERE added > TO_TIMESTAMP(in_visited)|
)"|PL/pgSQL function words_get_added(integer) line 7 at assignment
Please help me to modify my SELECT UNION so that I get just 1 row as result:
{noun1,noun2,noun3,verb1,verb2}
Regards
Alex
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 REFERENCES words_games ON DELETE CASCADE, uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, played timestamptz NOT NULL, tiles jsonb, letters text, handtext, score integer CHECK(score >= 0), puzzle boolean NOT NULL DEFAULT false ); I am trying to construct a query, which would draw a game board when given a move id (aka mid): SELECT hand, JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col, JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row, JSONB_ARRAY_ELEMENTS(tiles)->'letter' AS letter, JSONB_ARRAY_ELEMENTS(tiles)->'value' AS value FROM words_moves WHERE action = 'play' AND gid = (SELECT gid FROM words_moves WHERE mid = 391416) AND played <= (SELECT played FROM words_moves WHERE WHERE mid = 391416) ORDER BY played DESC The above query works for me and fetches all moves performed in a game id (aka gid) up to the move id 391416. In my Java program I then just draw the tiles at the board, one by one (here a picture: https://slova.de/game-62662/ ) I have however 3 questions please: 1. Is it okay to call JSONB_ARRAY_ELEMENTS four times in the query, will PostgreSQL optimize that to a single call? 2. Do you think if it is okay to sort by played timestamp or should I better sort by mid? 3. Performancewise is it okay to use the 2 subqueries for finding gid and played when given a mid? Thank you Alex
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): > > > > SELECT > > hand, > > JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col, > > JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row, > > JSONB_ARRAY_ELEMENTS(tiles)->'letter' AS letter, > > JSONB_ARRAY_ELEMENTS(tiles)->'value' AS value > > FROM words_moves > > WHERE action = 'play' AND > > gid = (SELECT gid FROM words_moves WHERE mid = 391416) > > AND played <= (SELECT played FROM words_moves WHERE WHERE mid = > 391416) > > ORDER BY played DESC > > > > The above query works for me and fetches all moves performed in a game > id (aka gid) up to the move id 391416. > > > > 1. Is it okay to call JSONB_ARRAY_ELEMENTS four times in the query, will > PostgreSQL optimize that to a single call? > > Typically set returning functions should be used in the FROM clause, not > the SELECT list: > > SELECT > hand, > t.tile -> 'col' AS col, > t.tile -> 'row' AS row, > t.tile -> 'letter' AS letter, > t.tile -> 'value' AS value > FROM words_moves > cross join jsonb_array_elements(tiles) as t(tile) > WHERE action = 'play' > AND gid = (SELECT gid FROM words_moves WHERE mid = 391416) > AND played <= (SELECT played FROM words_moves WHERE WHERE mid = > 391416) > ORDER BY played DESC > > I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that I also need the board id aka bid from another table, words_games), but hit the next problem: CREATE OR REPLACE FUNCTION words_get_move( in_mid integer ) RETURNS TABLE ( out_bidinteger, out_midbigint, out_hand text, out_colinteger, out_rowinteger, out_letter text, out_value integer ) AS $func$ SELECT g.bid, m.mid, m.hand, (t->'col')::int AS col, (t->'row')::int AS row, (t->'letter')::text AS letter, (t->'value')::int AS value FROM words_moves m CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile) LEFT JOIN words_games g USING(gid) WHERE m.action = 'play' AND m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid) AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid) ORDER BY m.played DESC; $func$ LANGUAGE sql; words_ru=> \i src/slova/dict/words_get_move.sql psql:src/slova/dict/words_get_move.sql:28: ERROR: cannot cast type jsonb to integer LINE 17: (t->'col')::int AS col, ^ How to cast the col to integer here? Thanks Alex
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 JSON-array of JSON-objects with properties col, row, value
(integers) and letter (text):
words_ru=> SELECT * FROM words_moves LIMIT 5;
mid | action | gid | uid |played
|
tiles
| score | letters | hand | puzzle
++---+--+---++---+-+-+
385903 | play | 17042 | 5476 | 2018-06-20 04:46:13.864758+02 | [{"col":
7, "row": 6, "value": 1, "letter": "А"}, {"col": 7, "row": 5, "value": 2,
"letter": "Р"}, {"col": 7, "row": 11, "value": 2, "letter": "В"}, {"col":
7, "row": 10, "value": 1, "letter": "А"}, {"col": 7, "row": 9, "value": 2,
"letter": "Л"}, {"col": 7, "row": 8, "value": 2, "letter": "П"}, {"col": 7,
"row": 7, "value": 2, "letter": "С"}] |29 | АРВАЛПС | ВРЛПААС | f
391416 | play | 17055 | 5476 | 2018-06-21 00:36:36.690012+02 | [{"col":
4, "row": 11, "value": 1, "letter": "А"}, {"col": 4, "row": 10, "value": 2,
"letter": "К"}, {"col": 4, "row": 9, "value": 0, "letter": "Л"}, {"col": 4,
"row": 8, "value": 1, "letter": "Е"}, {"col": 4, "row": 7, "value": 2,
"letter": "Д"}, {"col": 4, "row": 5, "value": 2, "letter": "Р"}, {"col": 4,
"row": 4, "value": 2, "letter": "П"}] |34 | АКЛЕДРП | РКП*АДЕ | f
394056 | play | 17264 | 7873 | 2018-06-21 13:39:27.026943+02 | [{"col":
9, "row": 7, "value": 0, "letter": "Р"}, {"col": 8, "row": 7, "value": 0,
"letter": "Е"}, {"col": 7, "row": 7, "value": 1, "letter": "Н"}, {"col": 6,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "Р"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 3,
"row": 7, "value": 2, "letter": "К"}]|24 | РЕНОРОК | ОК**ОНР | f
131 | play | 206 | 404 | 2018-02-20 09:26:05.234006+01 | [{"col":
9, "row": 7, "value": 5, "letter": "Ь"}, {"col": 8, "row": 7, "value": 2,
"letter": "Д"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}, {"col": 6,
"row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 1,
"letter": "О"}, {"col": 4, "row": 7, "value": 2, "letter": "Р"}, {"col": 3,
"row": 7, "value": 2, "letter": "П"}]|32 | ЬДЕСОРП | | f
15676 | play | 2785 | 2997 | 2018-04-18 16:56:58.368445+02 | [{"col":
12, "row": 7, "value": 5, "letter": "Ь"}, {"col": 11, "row": 7, "value": 1,
"letter": "Н"}, {"col": 10, "row": 7, "value": 1, "letter": "Е"}, {"col":
8, "row": 7, "value": 0, "letter": "Г"}, {"col": 9, "row": 7, "value": 2,
"letter": "Р"}, {"col": 7, "row": 7, "value": 1, "letter": "И"}, {"col": 6,
"row": 7, "value": 2, "letter": "М"}] |28 | МИЬРНГЕ | | f
(5 rows)
This stored function -
CREATE OR REPLACE FUNCTION words_get_move(
in_mid integer
) RETURNS TABLE (
out_bidinteger,
out_midbigint,
out_hand text,
out_colinteger,
out_rowinteger,
out_letter text,
out_value integer
) AS
$func$
SELECT
g.bid,
m.mid,
m.hand,
(t.tile->'col')::int AS col,
(t.tile->'row')::int AS row,
(t.tile->'letter')::text AS letter,
(t.tile->'value')::int AS value
FROM words_moves m
CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile)
LEFT JOIN words_games g USING(gid)
WHERE m.action = 'play' AND
m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid)
AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid)
ORDER BY m.played DESC;
$func$ LANGUAGE sql;
gives me same error (why does it think it is JSONB and not integer?)
words_ru=> \i src/slova/dict/words_get_move.sql
psql:src/slova/dict/words_get_move.sql:28: ERROR: cannot cast type jsonb
to integer
LINE 17: (t.tile->'col')::int AS col,
^
And I would prefer not to use ->> because I want col, row, value as
integers and not text
Regards
Alex
P.S. Below is the table definition:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable
| Default
-+--+---+--+--
mid | bigint | | not null |
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?
> ---+--
> jsonb | 1
> (1 row)
>
> test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
> ->> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
> pg_typeof | ?column?
> ---+--
> text | 1
>
I have ended up with the stored function using ->> and casting:
CREATE OR REPLACE FUNCTION words_get_move(
in_mid integer
) RETURNS TABLE (
out_bidinteger,
out_midbigint,
out_hand text,
out_colinteger,
out_rowinteger,
out_letter text,
out_value integer
) AS
$func$
SELECT
g.bid,
m.mid,
m.hand,
(j.tile->>'col')::int AS col,
(j.tile->>'row')::int AS row,
j.tile->>'letter' AS letter,
(j.tile->>'value')::int AS value
FROM words_moves m
CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS j(tile)
LEFT JOIN words_games g USING(gid)
LEFT JOIN LATERAL (SELECT gid, played FROM words_moves WHERE mid =
in_mid) AS m2 ON TRUE
WHERE m.action = 'play'
AND m.gid = m2.gid
AND m.played <= m2.played
ORDER BY m.played ASC;
$func$ LANGUAGE sql;
It gives me the desired output:
out_bid | out_mid | out_hand | out_col | out_row | out_letter | out_value
-+-+--+-+-++---
1 | 385934 | РТМРЕКО | 7 | 7 | О | 1
1 | 385934 | РТМРЕКО | 7 | 3 | М | 2
1 | 385934 | РТМРЕКО | 7 | 4 | Е | 1
1 | 385934 | РТМРЕКО | 7 | 5 | Т | 2
1 | 385934 | РТМРЕКО | 7 | 6 | Р | 2
1 | 386610 | МИЛСЯРО | 5 | 6 | Л | 2
1 | 386610 | МИЛСЯРО | 6 | 6 | Я | 3
1 | 386610 | МИЛСЯРО | 4 | 6 | О | 1
1 | 386610 | МИЛСЯРО | 3 | 6 | М | 2
1 | 391416 | РКП*АДЕ | 4 | 9 | Л | 0
1 | 391416 | РКП*АДЕ | 4 | 10 | К | 2
1 | 391416 | РКП*АДЕ | 4 | 5 | Р | 2
1 | 391416 | РКП*АДЕ | 4 | 7 | Д | 2
1 | 391416 | РКП*АДЕ | 4 | 4 | П | 2
1 | 391416 | РКП*АДЕ | 4 | 8 | Е | 1
1 | 391416 | РКП*АДЕ | 4 | 11 | А | 1
(16 rows)
Regards
Alex
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
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
So I try to find 5 most old interesting moves and it works:
words_ru=> select
m.mid, -- interesting move id
m.gid,
s.word,
m.tiles
from words_moves m
left join words_scores s using (mid)
where m.action='play'
and length(m.letters)=7
and length(m.hand)=7
and length(s.word)=7
order by m.played asc
limit 5;
-[ RECORD 1
]---
mid | 77461
gid | 1048
word | СОПЕНИЕ
tiles | [{"col": 7, "row": 1, "value": 2, "letter": "С"}, {"col": 7, "row":
2, "value": 1, "letter": "О"}, {"col": 7, "row": 3, "value": 2, "letter":
"П"}, {"col": 7, "row": 4, "value": 1, "letter": "Е"}, {"col": 7, "row": 5,
"value": 1, "letter": "Н"}, {"col": 7, "row": 6, "value": 1, "letter":
"И"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}]
-[ RECORD 2
]---
mid | 78406
gid | 5702
word | СОПИЛКА
tiles | [{"col": 5, "row": 14, "value": 2, "letter": "С"}, {"col": 6,
"row": 14, "value": 1, "letter": "О"}, {"col": 7, "row": 14, "value": 2,
"letter": "П"}, {"col": 8, "row": 14, "value": 1, "letter": "И"}, {"col":
9, "row": 14, "value": 2, "letter": "Л"}, {"col": 10, "row": 14, "value":
2, "letter": "К"}, {"col": 11, "row": 14, "value": 1, "letter": "А"}]
However there is one problem: I only want to fetch those "interesting"
moves which are preceded by at least another one move in the same game -
because otherwise the game board is empty and the puzzle is boring.
So I have tried to add a JOIN LATERAL:
select
m.mid, -- interesting move id
m2.mid, -- preceding move id in the same game id
m.gid,
s.word,
m.tiles
from words_moves m
left join words_scores s using (mid)
join lateral (SELECT * FROM words_moves WHERE gid=m.gid and mid < m.mid) AS
m2 ON TRUE
where m.action='play'
and length(m.letters)=7
and length(m.hand)=7
and length(s.word)=7
order by m.played asc
limit 5;
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
Do you please have any ideas here? (I hope my context is not too confusing
:-)
Regards
Alex
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 move that > condition to EXISTS condition inside WHERE clause instead. Just a style > thing as far as I know. > How would you do that with EXISTS? I tried, but my query failed to compile Regards Alex
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,
out_count bigint,
out_puzzle boolean,
out_midbigint,
out_secret text,
out_gidinteger,
out_score integer
) AS
$func$
SELECT
TO_CHAR(played, 'Mon ') AS label,
COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
puzzle,
mid,
MD5(mid || ‘my little secret’) AS secret,
gid,
score
FROM words_moves
WHERE action = 'play'
AND LENGTH(hand) = 7
AND (LENGTH(letters) = 7 OR score > 90)
AND played > CURRENT_TIMESTAMP - in_start
AND played < CURRENT_TIMESTAMP - in_end
GROUP BY label, puzzle, mid, secret, gid, score
ORDER BY played DESC
$func$ LANGUAGE sql STABLE;
But when I run it, I only get 0 or 1 in the out_count column:
words_ru=> select * from words_list_puzzles(interval '2 year', interval '1
year');
out_label | out_count | out_puzzle | out_mid |out_secret
| out_gid | out_score
---+---++-+--+-+---
Nov 2018 | 0 | f | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe | 46007 |28
Nov 2018 | 0 | f | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 | 45922 |26
Nov 2018 | 0 | f | 1322050 |
b67b091d383678de392bf7370c735cab | 45877 |34
Nov 2018 | 0 | f | 1320017 |
35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
Nov 2018 | 0 | f | 1319160 |
83df42f7ad398bbb060fc02ddfdc62c0 | 45031 |95
.
May 2018 | 0 | f | 264251 |
2fff1154962966b16a2996387e30ae7f | 10946 |99
May 2018 | 1 | t | 257620 |
645613db6ea40695dc967d8090ab3246 | 12713 |93
May 2018 | 0 | f | 245792 |
bb75bfd9cb443ff541b199d893c68117 | 12359 |24
May 2018 | 1 | t | 243265 |
d899a5d642ccd96d931194f48ef56d53 | 11950 | 123
May 2018 | 0 | f | 231953 |
ad53b5b2c0d4cced3d50e8b44ad53e55 | 11910 |32
- while I was hoping to get 2 for the "May 2018" section.
What am I doing wrong please, why don't the values add up? Below is the
table desc:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Thank you
Alex
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
> >
> > ) RETURNS TABLE (
> > out_label text,
> > out_count bigint,
> > out_puzzle boolean,
> > out_midbigint,
> > out_secret text,
> > out_gidinteger,
> > out_score integer
> > ) AS
> > $func$
> >
> > SELECT
> > TO_CHAR(played, 'Mon ') AS label,
> > COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
>
> If I am following it is because you have mid in GROUP BY and mid is a
> PK. Since mid will always be unique you will have at most on row per group.
>
> > puzzle,
> > mid,
> > MD5(mid || ‘my little secret’) AS secret,
> > gid,
> > score
> >
> > FROM words_moves
> > WHERE action = 'play'
> > AND LENGTH(hand) = 7
> > AND (LENGTH(letters) = 7 OR score > 90)
> > AND played > CURRENT_TIMESTAMP - in_start
> > AND played < CURRENT_TIMESTAMP - in_end
> > GROUP BY label, puzzle, mid, secret, gid, score
> > ORDER BY played DESC
> >
> > $func$ LANGUAGE sql STABLE;
> >
> > But when I run it, I only get 0 or 1 in the out_count column:
> >
> > words_ru=> select * from words_list_puzzles(interval '2 year', interval
> > '1 year');
> > out_label | out_count | out_puzzle | out_mid |out_secret
> > | out_gid | out_score
> >
> ---+---++-+--+-+---
> > Nov 2018 | 0 | f | 1326876 |
> > e4928d3c34f50b8e6eabf7bad5b932fe | 46007 |28
> > Nov 2018 | 0 | f | 1324466 |
> > 6228ba509a7124f485feb5c1acbb6b68 | 45922 |26
> > Nov 2018 | 0 | f | 1322050 |
> > b67b091d383678de392bf7370c735cab | 45877 |34
> > Nov 2018 | 0 | f | 1320017 |
> > 35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
> > Nov 2018 | 0 | f | 1319160 |
> > 83df42f7ad398bbb060fc02ddfdc62c0 | 45031 |95
> > .
> > May 2018 | 0 | f | 264251 |
> > 2fff1154962966b16a2996387e30ae7f | 10946 |99
> > May 2018 | 1 | t | 257620 |
> > 645613db6ea40695dc967d8090ab3246 | 12713 |93
> > May 2018 | 0 | f | 245792 |
> > bb75bfd9cb443ff541b199d893c68117 | 12359 |24
> > May 2018 | 1 | t | 243265 |
> > d899a5d642ccd96d931194f48ef56d53 | 11950 | 123
> > May 2018 | 0 | f | 231953 |
> > ad53b5b2c0d4cced3d50e8b44ad53e55 | 11910 |32
> >
> > - while I was hoping to get 2 for the "May 2018" section.
> >
> > What am I doing wrong please, why don't the values add up? Below is the
> > table desc:
> >
> > words_ru=> \d words_moves
> >Table "public.words_moves"
> > Column | Type | Collation | Nullable |
> >Default
> >
> -+--+---+--+--
> > mid | bigint | | not null |
> > nextval('words_moves_mid_seq'::regclass)
> > action | text | | not null |
> > gid | integer | | not null |
> > uid | integer | | not null |
> > played | timestamp with time zone | | not null |
> > tiles | jsonb| | |
> > score | integer | | |
> > letters | text | | |
> > hand| text | | |
> > puzzle | boolean | | not null | false
> > Indexes:
> > "words_moves_pkey" PRIMARY KEY, btree (mid)
> > "words_moves_gid_played_idx" btree (gid, played DESC)
> > "words_moves_uid_action_played_idx" btree (uid, action, played)
> >
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 display headers: a "Mon " plus
the number of true puzzles per section.
Thanks to David's hint the following seems to work even though I wonder if
it is the most optimal way to call TO_CHAR twice:
CREATE OR REPLACE FUNCTION words_list_puzzles(
in_start interval,
in_end interval
) RETURNS TABLE (
out_label text,
out_count bigint,
out_puzzle boolean,
out_midbigint,
out_secret text,
out_gidinteger,
out_score integer
) AS
$func$
SELECT
TO_CHAR(played, 'Mon ') AS label,
-- used for header
COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played,
'Mon ')), --used for header
puzzle,
mid,
MD5(mid || 'my little secret') AS secret,
gid,
score
FROM words_moves
WHERE action = 'play'
AND LENGTH(hand) = 7
AND (LENGTH(letters) = 7 OR score > 90)
AND played > CURRENT_TIMESTAMP - in_start
AND played < CURRENT_TIMESTAMP - in_end
ORDER BY played DESC
$func$ LANGUAGE sql STABLE;
Regards
Alex
P.S: Below is my table description again and the output of the above
function:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
words_ru=> select * from words_list_puzzles(interval '2 year', interval '1
year');
out_label | out_count | out_puzzle | out_mid |out_secret
| out_gid | out_score
---+---++-+--+-+---
Nov 2018 | 1 | f | 1331343 |
78467b5f3bde3d3f2291cf539c949f79 | 46134 |28
Nov 2018 | 1 | f | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe | 46007 |28
Nov 2018 | 1 | f | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 | 45922 |26
Nov 2018 | 1 | f | 1322050 |
b67b091d383678de392bf7370c735cab | 45877 |34
Nov 2018 | 1 | f | 1320017 |
35f03b0c7159cec070c00aa80359fd42 | 44255 | 120
.
May 2018 | 3 | f | 95114 |
e7e8bab64fab20f6fec229319e2bab40 |7056 |28
May 2018 | 3 | f | 88304 |
161c0638dede80f830a36efa6f428dee |6767 |40
May 2018 | 3 | f | 86180 |
4d47a65263331cf4e2d2956886b6a72f |6706 |26
May 2018 | 3 | f | 85736 |
debb1efd673c91947a8aa7f38be4217c |6680 |28
May 2018 | 3 | f | 82522 |
e55ec68a5a5dacc2bc463e397198cb1c |6550 |27
Apr 2018 | 0 | f | 78406 |
f5d264ccfe94aaccd90ce6c019716d4d |5702 |58
Apr 2018 | 0 | f | 77461 |
404886e913b698596f9cf3648ddf6fa4 |1048 |26
(415 rows)
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
WHERE mid = in_mid
RETURNING puzzle
INTO STRICT out_puzzle;
$func$ LANGUAGE sql;
and fails with -
ERROR: 42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
^
LOCATION: scanner_yyerror, scan.l:1128
Thank you
Alex
P.S: Here the table description, with mid being the PK:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable
| Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
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 REPLACE FUNCTION words_toggle_puzzle( > in_mid bigint > ) RETURNS boolean > AS > $func$ > UPDATE words_moves > SET puzzle = NOT puzzle > WHERE mid = in_mid > RETURNING puzzle; > $func$ LANGUAGE sql; > your suggestion works well, thank you. I wanted to use strict, because the mid is a PK - so there should always be an exactly one record that has been updated (or otherwise, in very strange cases - the SQL would fail and my java-servlet would throw SQLException) Regards Alex
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
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
letters | text | | |
hand| text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
When I search for "interesting moves" with score higher than 90 or all 7
tiles played, then the query takes a bit longer:
EXPLAIN ANALYZE
SELECT
TO_CHAR(played, 'Mon '),
COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played, 'Mon
')),
puzzle,
mid,
MD5(mid || 'cookie'),
gid,
score
FROM words_moves
WHERE action = 'play'
AND LENGTH(hand) = 7
AND (LENGTH(letters) = 7 OR score > 90)
AND played > CURRENT_TIMESTAMP - interval '1 year'
AND played < CURRENT_TIMESTAMP - interval '3 day'
ORDER BY played DESC;
QUERY PLAN
Sort (cost=168533.19..168533.30 rows=44 width=97) (actual
time=2126.433..2126.541 rows=1036 loops=1)
Sort Key: played DESC
Sort Method: quicksort Memory: 194kB
-> WindowAgg (cost=168530.56..168531.99 rows=44 width=97) (actual
time=2122.991..2125.593 rows=1036 loops=1)
-> Sort (cost=168530.56..168530.67 rows=44 width=57) (actual
time=2122.934..2123.049 rows=1036 loops=1)
Sort Key: (to_char(played, 'Mon '::text))
Sort Method: quicksort Memory: 129kB
-> Gather (cost=1000.00..168529.36 rows=44 width=57)
(actual time=287.461..2121.445 rows=1036 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on words_moves
(cost=0.00..167524.96 rows=18 width=57) (actual time=207.234..2115.686
rows=345 loops=3)
Filter: ((action = 'play'::text) AND
(length(hand) = 7) AND ((length(letters) = 7) OR (score > 90)) AND (played
> (CURRENT_TIMESTAMP - '1 year'::interval)) AND (played <
(CURRENT_TIMESTAMP - '3 days'::interval)))
Rows Removed by Filter: 1088073
Planning Time: 0.383 ms
Execution Time: 2126.922 ms
(15 rows)
Here the link: https://explain.depesz.com/s/s3HF
So I add an index with -
CREATE INDEX ON words_moves(length(letters), score, action);
That is because I have several such queries looking for interesting 'play'
moves with high score or all 7 tiles played.
This helps a bit:
QUERY PLAN
Sort (cost=126487.34..126487.45 rows=44 width=97) (actual
time=219.649..219.744 rows=1036 loops=1)
Sort Key: played DESC
Sort Method: quicksort Memory: 194kB
-> WindowAgg (cost=126484.71..126486.14 rows=44 width=97) (actual
time=216.180..218.680 rows=1036 loops=1)
-> Sort (cost=126484.71..126484.82 rows=44 width=57) (actual
time=216.125..216.226 rows=1036 loops=1)
Sort Key: (to_char(played, 'Mon '::text))
Sort Method: quicksort Memory: 129kB
-> Bitmap Heap Scan on words_moves
(cost=83892.69..126483.50 rows=44 width=57) (actual time=209.447..214.879
rows=1036 loops=1)
Recheck Cond: (((length(letters) = 7) AND (action =
'play'::text)) OR ((score > 90) AND (action = 'play'::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$
DECLARE
_user jsonb;
_uidinteger;
_banned boolean;
_removedboolean;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;
-- ensure that every record has a valid auth
FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP
IF NOT words_valid_user((_user->>'social')::int,
_user->>'sid',
_user->>'auth') THEN
RAISE EXCEPTION 'Invalid user = %', _user;
END IF;
IF out_user IS NULL THEN
SELECT
uid,
u.banned_until > CURRENT_TIMESTAMP,
u.removed
INTO STRICT
_uid,
_banned,
_removed
FROM words_social s
LEFT JOIN words_users u USING(uid)
WHERE s.social = (_user->>'social')::int
AND s.sid = _user->>'sid';
IF _banned THEN
RAISE EXCEPTION 'Banned user = %', _user;
END IF;
IF _removed THEN
RAISE EXCEPTION 'Removed user = %', _user;
END IF;
out_user := JSONB_INSERT(_user, '{uid}', _uid);
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Unfortunately, when I run my stored function it fails:
words_en=> select out_user from
words_get_user('[{"given":"Abcde1","social":1,"auth":"xxx","stamp":1480237061,"sid":"a","photo":"
https://vk.com/images/camera_200.png
"},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"a"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"a"}]'::jsonb);
ERROR: function jsonb_insert(jsonb, unknown, integer) does not exist
LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT JSONB_INSERT(_user, '{uid}', _uid)
CONTEXT: PL/pgSQL function words_get_user(jsonb) line 44 at assignment
What is missing here please?
Thank you
Alex
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
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
puzzle | boolean | | not null | false
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
My word game is published since beginning of 2018 and I have that many
entries there:
words_ru=> select coun
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 wonder if this is the best way with PL/pgSQL - IF EXISTS (SELECT 1 FROM words_users WHERE uid = _uid AND muted) THEN RAISE EXCEPTION 'User % is muted', _uid; END IF; Or can this be done in a simpler way? Thanks Alex P.S. Here my entire stored function: CREATE OR REPLACE FUNCTION words_set_motto( in_social integer, in_sidtext, in_auth text, in_motto text ) RETURNS integer AS $func$ DECLARE _uid integer; BEGIN IF NOT words_valid_user(in_social, in_sid, in_auth) THEN RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid; END IF; _uid := (SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid); IF LENGTH(in_motto) > 250 THEN RAISE EXCEPTION 'Invalid motto by user %', _uid; END IF; IF EXISTS (SELECT 1 FROM words_users WHERE uid = _uid AND muted) THEN RAISE EXCEPTION 'User % is muted', _uid; END IF; IF (SELECT COUNT(NULLIF(nice, 0)) - COUNT(NULLIF(nice, 1)) FROM words_reviews WHERE uid = _uid) < -20 THEN RAISE EXCEPTION 'User % can not change motto', _uid; END IF; UPDATE words_users SET motto = in_motto WHERE uid = _uid; RETURN _uid; END $func$ LANGUAGE plpgsql;
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!
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
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Some of the moves can be "interesting" in the sense that the player have
used all 7 letter tiles or achieved a high score over 90 points,
I want to display those moves as "puzzles" and have prepared a table to
store, per-user, who has solved them:
# \d words_puzzles
Table "public.words_puzzles"
Column | Type | Collation | Nullable | Default
+--+---+--+-
mid| bigint | | not null |
uid| integer | | not null |
solved | timestamp with time zone | | not null |
Foreign-key constraints:
"words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE
Now I am trying to create a custom stored function which would return just
one mid (move id) which is not too new (1 year old) and the user has not
tackled it yet:
CREATE OR REPLACE FUNCTION words_daily_puzzle(
in_uid int
) RETURNS table (
out_mid bigint,
out_secret text
) AS
$func$
SELECT
mid,
MD5(mid ||'my little secret')
FROM words_moves
WHERE action = 'play'
AND (LENGTH(str) = 7 OR score > 90)
AND played BETWEEN CURRENT_TIMESTAMP - INTERVAL '51 week' AND
CURRENT_TIMESTAMP - INTERVAL '50 week'
-- the user has not solved this puzzle yet
AND NOT EXISTS (SELECT 1 FROM words_puzzles WHERE mid =
the_outer_mid AND uid = in_uid)
ORDER BY PLAYED ASC
LIMIT 1;
$func$ LANGUAGE sql;
As you can see I am missing 1 piece - how do I address the outer SELECT mid
from the EXISTS-SELECT?
I have written "the_outer_mid" there.
Should I use LEFT JOIN LATERAL here? I have difficulties wrapping my head
around this.
TLDR: how to return 1 mid from 1 year ago, which is not solved by the user
in_uid yet?
Thank you
Alex
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 for me!
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&secret=c6f469786df7e8d44461381b62b2ce7d
I am trying to improve a stored function -
CREATE OR REPLACE FUNCTION words_solve_puzzle(
in_mid bigint,
in_uid int,
in_answertext,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_tilejsonb;
_letter char;
_value integer;
_answer text;
BEGIN
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM
words_moves WHERE mid = in_mid)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
RAISE NOTICE 'Tile % letter % value', _tile, _letter,
_value;
END LOOP;
However this results in the error message -
ERROR: 42601: syntax error at or near "SELECT"
LINE 24: ... FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT til...
^
LOCATION: scanner_yyerror, scan.l:1180
Could you please help me, how to combine SELECT query and the LOOP
through JSONB_ARRAY_ELEMENTS here?
Thank you
Alex
P.S: Here the table:
words_de=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
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 bigint, in_uid int, in_guess text, OUT out_json jsonb ) RETURNS jsonb AS $func$ DECLARE _tilejsonb; _letter char; _value integer; BEGIN in_guess := UPPER(in_guess); FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS((SELECT tiles FROM words_moves WHERE mid = in_mid)) LOOP _letter := _tile->>'letter'; _value := (_tile->>'value')::int; -- verify that all played tiles except wildcard are found in the suggested answer IF _value > 0 AND POSITION(_letter IN in_guess) = 0 THEN out_json := json_build_object( 'label','👎 Keep guessing!' ); RETURN; END IF; END LOOP; -- check if the in_guess is one of the played words in that move IF NOT EXISTS(SELECT 1 FROM words_scores WHERE mid = in_mid AND word = in_guess) THEN out_json := json_build_object( 'label','👎 Wrong!' ); RETURN; END IF; -- the solution already submitted, just ack, but do not award coins IF EXISTS (SELECT 1 FROM words_puzzles WHERE mid = in_mid AND uid = in_uid) THEN out_json := json_build_object( 'label','👍 Correct!', 'url', '/ws/puzzle2?mid=' || in_mid || '&secret=' || MD5(in_mid || 'my secret') ); RETURN; END IF; -- save the puzzle solution and award coins to the user INSERT INTO words_puzzles (mid, uid, solved) VALUES (in_mid, in_uid, CURRENT_TIMESTAMP); UPDATE words_users SET coins = coins + 1 WHERE uid = in_uid; out_json := json_build_object( 'label','👍 Correct, +1 coin!', 'url', '/ws/puzzle2?mid=' || in_mid || '&secret=' || MD5(in_mid || 'my secret') ); END $func$ LANGUAGE plpgsql; P.S. 'my secret' is not my real secret passphrase :-)
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 to 1 function, in SQL or if not possible, then PL/pgSQL. The new function should return a JSONB list containing 3 other lists, i.e. something like: [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] I think I should use the aggregate function jsonb_agg(). But I can't figure out how to apply it to the 3 queries below, could you please help me? CREATE OR REPLACE FUNCTION words_stat_charts( in_uid integer, in_opponent integer ) RETURNS jsonb AS $func$ -- how to return [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] ? SELECT 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); SELECT 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer FROM words_games WHERE finished IS NOT NULL AND ( (player1 = in_uid AND player2 = in_opponent) OR (player2 = in_uid AND player1 = in_opponent) ); SELECT SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer FROM words_scores WHERE uid = in_uid; $func$ LANGUAGE sql STABLE; When I try simply wrapping the jsonb_agg() around the 3 columns in the first query I get the syntax error: SELECT JSONB_AGG( 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer ) FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); ERROR: function jsonb_agg(integer, integer, integer) does not exist LINE 8: JSONB_AGG( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Thank you for any hints Alex
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, Michael, thanks - that is what I realised just after sending the mail. I don't have rows here, but a single row with several columns.
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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer ) FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); But is it possible in SQL to combine all 3 queries, so that a JSONB list of lists is returned? I cannot use a UNION, because the first two queries return 3 columns, but the last query returns 7 columns. So I have to use PL/PgSQL, correct? Best regards Alex
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(
in_uid integer,
in_opponent integer, -- optional parameter, can be NULL
OUT out_data jsonb
) RETURNS jsonb AS
$func$
BEGIN
out_data := JSONB_BUILD_OBJECT();
-- add a JSON list with 7 integers
out_data := JSONB_INSERT(out_data, '{length}', JSONB_BUILD_ARRAY(
SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer
))
-- add a JSON list with 3 integers
FROM words_scores WHERE uid = in_uid;
out_data := JSONB_INSERT(out_data, '{results}', 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 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);
-- add a JSON list with 3 integers, but only if in_opponent param
is supplied
IF in_opponent > 0 AND in_opponent <> in_uid THEN
out_data := JSONB_INSERT(out_data, '{versus}',
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 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
END IF;
END
$func$ LANGUAGE plpgsql;
The function works well:
# select * from words_stat_charts(5, 6);
out_data
---
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [6, 3, 0],
"results": [298, 151, 0]}
(1 row)
Except when 2 players never played with each other - then I get [ null,
null, null ]:
# select * from words_stat_charts(5, 1);
out_data
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [null, null,
null], "results": [298, 151, 0]}
(1 row)
Is there maybe a nice trick to completely omit "versus" from the returned
JSONB map of lists when its [ null, null, null ]?
Thank you
Alex
Re: How to return a jsonb list of lists (with integers)
I have tried the following, but IF FOUND is always false for some reason:
_versus := 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 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
IF FOUND THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Re: How to return a jsonb list of lists (with integers)
I have ended up with the following (to avoid returning [null, null, null]
for players who never played with each other):
_versus := 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 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
IF _versus <> '[null, null, null]'::jsonb THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Re: How to return a jsonb list of lists (with integers)
Hi Pavel, why would SELECT INTO be better here? Thanks Alex
Re: How to return a jsonb list of lists (with integers)
I have tried switching to SELECT INTO, but IF FOUND is still always true,
which gives me [ null, null, null ] for some users:
SELECT JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'won') OR (player2 = in_viewer AND state2 = 'won') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND in_viewer IN (player1, player2)
AND in_uid IN (player1, player2)
INTO _versus;
IF FOUND THEN -- for some reason this is always
true
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
What works for me is:
IF _versus <> '[null,null,null]'::jsonb THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Greetings
Alex
Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
Good evening,
I have a word game which uses PostgreSQL 13.2 and 80% of the code is
written as stored functions in PL/PgSQL or SQL.
Recently I have purchased some traffic and the number of daily games
increased from 100 to 700.
In the PostgreSQL log I have noticed that the duration for 2 particular
queries have increased, especially in the evenings:
2021-02-19 17:51:19.104 CET [68932] LOG: duration: 2356.723 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 17:55:23.290 CET [68602] LOG: duration: 2326.507 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 17:57:57.057 CET [68932] LOG: duration: 1257.773 ms execute
: SELECT out_uidAS uid, out_fcmAS fcm,out_apns AS
apns, out_admAS adm,out_hmsAS hms,out_social AS social,
out_sidAS sid,out_body AS bodyFROM words_play_game($1::int,
$2::int, $3::jsonb)
2021-02-19 18:02:51.359 CET [68603] LOG: duration: 2305.950 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 18:08:26.130 CET [68932] LOG: duration: 2375.713 ms execute
: SELECT words_stat_longest($1::int)
One of the queries is actually a simple SELECT ... LIMIT 15 to find the 15
longest words played by a user (they are displayed at the player profile
page).
I have run the EXPLAIN ANALYZE here: https://explain.depesz.com/s/IcqN
It is difficult for me to understand, what could be missing there, does
anybody please have an idea?
Thank you for any input
Alex
P.S. Here are the tables involved in the SELECT query, with the
words_moves and words_geoip holding the most records:
# \d words_scores
Table "public.words_scores"
Column | Type | Collation | Nullable | Default
+-+---+--+-
mid| bigint | | not null |
gid| integer | | not null |
uid| integer | | not null |
word | text| | not null |
score | integer | | not null |
Indexes:
"words_scores_gid_idx" btree (gid)
"words_scores_length_mid_idx" btree (length(word) DESC, mid DESC)
"words_scores_uid_idx" btree (uid)
Check constraints:
"words_scores_score_check" CHECK (score >= 0)
"words_scores_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text)
Foreign-key constraints:
"words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
ON DELETE CASCADE
"words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE
# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
# \d words_geoip
Table "public.words_geoip"
Column | Type | Collation | Nullable | Default
+--+---+--+-
block | inet | | not null |
lat| double precision | | |
lng| double precision | | |
Indexes:
"words_geoip_pkey" PRIMARY KEY, btree (block)
# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
--+--+---+--+--
gid | integer | | not
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
Yes, Michael, that I have noticed too, but should have written more in my original mail. The query when I try it does run in 15ms, but evening logs show the query (I think only small percentage of it) running 1-3s. At the same time my CentOS 8 server with 64 GB RAM is never loaded, the load average show by top is 0.35-0.45. What could be the reason, does PostgreSQL 13.2 has some scheduled task maybe? I have only changed few lines in the postgresql.conf: # diff postgresql.conf.OLD postgresql.conf 64c64 < max_connections = 100 # (change requires restart) --- > max_connections = 120 # (change requires restart) 130c130 < #work_mem = 4MB # min 64kB --- > work_mem = 8MB# min 64kB 132c132 < #maintenance_work_mem = 64MB # min 1MB --- > maintenance_work_mem = 128MB # min 1MB 393c393 < #effective_cache_size = 4GB --- > effective_cache_size = 8GB 410,411c410,411 < #from_collapse_limit = 8 < #join_collapse_limit = 8 # 1 disables collapsing of explicit --- > from_collapse_limit = 24 > join_collapse_limit = 24 # 1 disables collapsing of explicit 469c469 < #log_min_messages = warning # values in order of decreasing detail: --- > log_min_messages = notice # values in order of decreasing detail: 497c497 < #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements --- > log_min_duration_statement = 1000 # -1 is disabled, 0 logs all statements And I have pgbouncer in front of the PostgreSQL: diff pgbouncer.ini.OLD pgbouncer.ini 12a13,15 > words_de = host=/tmp user=xxx password=xxx dbname=words_de > words_en = host=/tmp user=xxx password=xxx dbname=words_en > words_ru = host=/tmp user=xxx password=xxx dbname=words_ru 115a119 > ;; auth_type = md5 148c152 < ;server_reset_query = DISCARD ALL --- > server_reset_query = DISCARD ALL 156c160 < ;ignore_startup_parameters = extra_float_digits --- > ignore_startup_parameters = extra_float_digits 180c184 < ;max_client_conn = 100 --- > max_client_conn = 1000 185c189 < ;default_pool_size = 20 --- > default_pool_size = 100 Or is maybe everything ok...? The daily gzipped dump grows big: 939343358 Dec 31 01:33 words_ru-Dec.sql.gz 1221682336 Jan 31 01:33 words_ru-Jan.sql.gz 1423324283 Feb 20 01:34 words_ru-Feb.sql.gz and being an inexperienced pg admin I am a bit worried. Thank you Alex >
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
Thank you all, I will try at first shared_buffers = 16GBand index on words_scores(uid, length(word) desc)
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
With shared_buffers=16GB, pg_top shows: last pid: 2613; load avg: 0.49, 0.45, 0.37; up 0+00:19:21 16:41:16 16 processes: 16 sleeping CPU states: 9.2% user, 0.0% nice, 0.2% system, 90.4% idle, 0.1% iowait Memory: 21G used, 42G free, 29M buffers, 18G cached DB activity: 39 tps, 0 rollbs/s, 0 buffer r/s, 100 hit%, 3625 row r/s, 11 row w/s DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s DB disk: 0.0 GB total, 0.0 GB free (100% used) Swap: 32G free PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 2114 postgres 200 17G 6378M sleep 2:11 0.77% 44.80% postgres: words words_ru [local] idle 2012 postgres 200 17G 6005M sleep 0:55 0.45% 26.30% postgres: words words_ru [local] idle 2107 postgres 200 17G 6175M sleep 1:16 0.02% 0.97% postgres: words words_ru [local] idle 1184 postgres 200 17G 22M sleep 0:00 0.02% 0.97% postgres: walwriter 2030 postgres 200 17G 5948M sleep 1:18 0.00% 0.00% postgres: words words_ru [local] idle 2013 postgres 200 17G 5724M sleep 0:57 0.00% 0.00% postgres: words words_ru [local] idle 2103 postgres 200 17G 5823M sleep 0:52 0.00% 0.00% postgres: words words_ru [local] idle 2031 postgres 200 17G 5619M sleep 0:41 0.00% 0.00% postgres: words words_ru [local] idle 2029 postgres 200 17G 1128M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 2104 postgres 200 17G 948M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 2106 postgres 200 17G 1257M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 1182 postgres 200 17G 231M sleep 0:00 0.00% 0.00% postgres: checkpointer 1183 postgres 200 17G 135M sleep 0:00 0.00% 0.00% postgres: background writer 1185 postgres 200 17G 8696K sleep 0:00 0.00% 0.00% postgres: autovacuum launcher 2614 postgres 200 17G 22M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 1187 postgres 200 17G 6764K sleep 0:00 0.00% 0.00% postgres: logical replication launcher On Sat, Feb 20, 2021 at 4:15 PM Alexander Farber wrote: > Thank you all, I will try at first > > shared_buffers = 16GBand > > index on words_scores(uid, length(word) desc) > > >
JSONB_AGG: aggregate function calls cannot be nested
Good evening, I have the following query in 13.2: # SELECT TO_CHAR(finished, '-MM-DD') AS day, SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed, SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int AS expired FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; day | completed | expired +---+- 2021-02-06 | 167 | 71 2021-02-07 | 821 | 189 2021-02-08 | 816 | 323 2021-02-09 | 770 | 263 2021-02-10 | 864 | 230 2021-02-11 | 792 | 184 2021-02-12 | 838 | 231 2021-02-13 | 853 | 293 2021-02-14 | 843 | 231 2021-02-15 | 767 | 203 2021-02-16 | 744 | 237 2021-02-17 | 837 | 206 2021-02-18 | 751 | 196 2021-02-19 | 745 | 257 2021-02-20 | 654 | 135 (15 rows) It works well, but I would like to transform it into a JSONB map with 3 arrays. So I am trying: # SELECT JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day, JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed, JSONB_AGG(SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int) AS expired FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; ERROR: aggregate function calls cannot be nested LINE 3: JSONB_AGG(SUM(CASE WHEN reason='regular' or ... ^ Shouldn't I use JSONB_AGG here, to build the 3 JSON arrays? Or is the syntax error about being able to use JSONB_AGG only once per SELECT query? Greetings Alex
Re: JSONB_AGG: aggregate function calls cannot be nested
Then I have to split the query in 3 similar ones (with same condition)? I try: SELECT JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; ERROR: aggregate functions are not allowed in GROUP BY LINE 2: JSONB_AGG(TO_CHAR(finished, '-MM-DD')) A... ^
Re: JSONB_AGG: aggregate function calls cannot be nested
Ah, thank you... JSON support in PostgreSQL is cool and seems to be extended with each release. But standard tasks of returning a JSON map of lists or JSON list of list seem to be difficult to use. Greetings Alex
Re: JSONB_AGG: aggregate function calls cannot be nested
Thank you Thomas, this results in
select
day AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, '-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day;
day | completed | expired
+---+-
2021-02-08 | [481] | [155]
2021-02-09 | [770] | [263]
2021-02-10 | [864] | [230]
2021-02-11 | [792] | [184]
2021-02-12 | [838] | [231]
2021-02-13 | [853] | [293]
2021-02-14 | [843] | [231]
2021-02-15 | [767] | [203]
2021-02-16 | [744] | [237]
2021-02-17 | [837] | [206]
2021-02-18 | [751] | [196]
2021-02-19 | [745] | [257]
2021-02-20 | [802] | [168]
2021-02-21 | [808] | [380]
2021-02-22 | [402] | [255]
(15 rows)
but how to get a JSON map of lists here? I am trying:
select row_to_json (x) FROM (SELECT
day AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, '-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day) x;
row_to_json
{"day":"2021-02-08","completed":[475],"expired":[155]}
{"day":"2021-02-09","completed":[770],"expired":[263]}
{"day":"2021-02-10","completed":[864],"expired":[230]}
{"day":"2021-02-11","completed":[792],"expired":[184]}
{"day":"2021-02-12","completed":[838],"expired":[231]}
{"day":"2021-02-13","completed":[853],"expired":[293]}
{"day":"2021-02-14","completed":[843],"expired":[231]}
{"day":"2021-02-15","completed":[767],"expired":[203]}
{"day":"2021-02-16","completed":[744],"expired":[237]}
{"day":"2021-02-17","completed":[837],"expired":[206]}
{"day":"2021-02-18","completed":[751],"expired":[196]}
{"day":"2021-02-19","completed":[745],"expired":[257]}
{"day":"2021-02-20","completed":[802],"expired":[168]}
{"day":"2021-02-21","completed":[808],"expired":[380]}
{"day":"2021-02-22","completed":[410],"expired":[255]}
(15 rows)
While I would actually need:
{
"day": [ "2021-02-08", "2021-02-09", ... ],
"completed": [ 475, 770, ...],
"expired": [ 155, 263 , ...]
}
And then I could feed the data into the Chart.js shown at the bottom of my
web page https://slova.de/top
Currently I do a simple SELECT query and construct the JSON map of list in
the Java code of my servlet
Thank you
Alex
Re: JSONB_AGG: aggregate function calls cannot be nested
Ahh, thank you all -
select row_to_json (x) FROM( SELECT
jsonb_agg(day) AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, '-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
ORDER BY day) x;
row_to_json
--
--
{"day":["2021-02-16", "2021-02-20", "2021-02-10", "2021-02-09",
"2021-02-15", "2021-02-19", "2021-02-17", "2021-02-11", "2021-02-22",
"2021-02-08", "2021-02-
14", "2021-02-21", "2021-02-12", "2021-02-13",
"2021-02-18"],"completed":[744, 802, 864, 770, 767, 745, 837, 792, 751, 32,
843, 808, 838, 853, 751],"expired":
[237, 168, 230, 263, 203, 257, 206, 184, 337, 11, 231, 380, 231, 293, 196]}
(1 row)
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, Alexander Farber wrote:
>>
>> > Referenced by:
>> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
>> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>>
>> Make sure you have indexes on the gid columns of these tables. Delete
>> needs to scan them in order to find the rows that are cascaded to.
>>
>>
> An index on words_games(finished) and words_moves(played) would help too.
>
>
and have now the following indices in my database:
CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(created),
CREATE INDEX ON words_chat(created),
CREATE INDEX ON words_moves(uid, action, played);
CREATE INDEX ON words_moves(gid, played);
CREATE INDEX ON words_moves(played);
CREATE INDEX ON words_moves(uid);
CREATE INDEX ON words_moves(gid);
CREATE INDEX ON words_social(uid, stamp);
CREATE INDEX ON words_geoip USING SPGIST (block);
CREATE INDEX ON words_scores(LENGTH(word), mid);
-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
CREATE INDEX ON words_scores(gid);
CREATE INDEX ON words_scores(uid);
CREATE INDEX ON words_chat(gid);
However the deletion still takes forever and I have to ctrl-c it:
# delete from words_games where created < now() - interval '12 month';
Do you please have any further suggestions?
When I try to prepend "explain analyze" to the above query, then in the
production database it also lasts forever.
In an empty dev database the output does not help much -
# explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN
Delete on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.132..0.132 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.131..0.131 rows=0 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 137
Planning Time: 0.150 ms
Execution Time: 0.143 ms
(6 rows)
Below are the words_games and the "referenced by" tables -
# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
open1| boolean | | not null | false
open2| boolean | | not null | false
Indexes:
&
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 |
Default
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
open1| boolean | | not null | false
open2| boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE
You are probably talking about the section:
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
The first table words_boards only has 4 records, so I ignore it.
The second table words_users already has an index on the uid, because that
column is the primary key:
# \d words_users
Table "public.words_users"
Column| Type | Collation | Nullable |
Default
-+--+---+--+--
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip | inet | | not null |
fcm | text | | |
apns| text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
elo | integer | | not null |
medals | integer | | not null |
coins
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 month';
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: >> >>> The question is why does the command take days (when I tried last time): >>> delete from words_games where created < now() - interval '12 month'; >>> >>> >> postgres=# explain analyze delete from words_games where gid = 44877; >> >> create index on words_scores(mid); >> > I have also added: create index on words_puzzles(mid); and then the result if finally good enough for my nightly cronjob: explain analyze delete from words_games where created < now() - interval '12 month'; QUERY PLAN -- Delete on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual time=2121.475..2121.476 rows=0 loops=1) -> Seq Scan on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual time=0.006..85.908 rows=103166 loops=1) Filter: (created < (now() - '1 year'::interval)) Rows Removed by Filter: 126452 Planning Time: 0.035 ms Trigger for constraint words_chat_gid_fkey on words_games: time=598.444 calls=103166 Trigger for constraint words_moves_gid_fkey on words_games: time=83745.244 calls=103166 Trigger for constraint words_scores_gid_fkey on words_games: time=30638.420 calls=103166 Trigger for constraint words_puzzles_mid_fkey on words_moves: time=15426.679 calls=3544242 Trigger for constraint words_scores_mid_fkey on words_moves: time=18546.115 calls=3544242 Execution Time: 151427.183 ms (11 rows) There is one detail I don't understand in the output of "explain analyze" - why do the lines "Trigger for constraint words_scores_mid_fkey on words_moves: time=1885.372 calls=4" completely disappear after adding the index? Are those the "ON DELETE CASCADE" triggers? Aren't they called after the index has been added? Best regards Alex
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 > referencing tables and see what references those and index the > column(s) which are referencing. > >
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 languages. This is not very comfortable and I would like to switch to using same source code (regardless of the human language) for all stored functions. And after deploying a database, just run few commands to replace placeholders in the stored functions. So I am trying: CREATE OR REPLACE FUNCTION localize_hello() RETURNS text AS $func$ SELECT '$(hello)'; $func$ LANGUAGE sql IMMUTABLE; And then: update pg_proc set prosrc = regexp_replace(prosrc, '$\(\w+\)','Hi english','g') where proname='localize_hello'; But the error is: ERROR: permission denied for table pg_proc So I connect as user "postgres" and then the command seemingly succeeds, but when I call it, the delivered string is still old: select * from localize_hello(); localize_hello $(hello) (1 row) Is this a right approach? Do you please have any advice here? Thanks Alex
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 words_get_notification.sql \i ../words_common.sql \i words_valid_tile.sql \i words_get_moves.sql \i words_answer_puzzle.sql \i words_rare_letter_1.sql \i words_rare_letter_2.sql And then the ../words_common.sql creates tables and has 40 more "\i" calls. So I was hoping to have some SQL command to localize my stored functions. Best regards Alex
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
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 '$(placeholder)'; $func$ LANGUAGE sql IMMUTABLE; $localize$, '\$\(\w+\)', 'English word', 'g'); Unfortunately, I get the error: ERROR: prepared statement "regexp_replace" does not exist
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: 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: 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 bodys of my stored functions. After some more thinking yesterday I have decided to embed the SQL files with stored functions declarations in my servlet JAR file - that is where the rest and most of my translations are. I will just load them from servlet resources when the servlet start, replace the placeholder by Java and the execute them with CREATE OR REPLACE FUNCTION ...
Displaying chat by punished users only to themselves (db fiddle attached)
Hello,
I have developed a complete SQL fiddle for my question:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dcf063ba1615b392cc3cfa347a32c97b
The context is that I run an online game for two players using a PostgreSQL
14.2 backend.
I would like to make my game more friendly by hiding chat messages of
misbehaving users.
However, to prevent the punished users from noticing it and registering new
game accounts, I would like to still show them all messages :->
So here are the 4 tables used in my reduced test case:
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
muted BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE words_social (
sid text NOT NULL CHECK (sid ~ '\S'),
social integer NOT NULL CHECK (0 < social AND social <= 256),
given text NOT NULL CHECK (given ~ '\S'),
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL
CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE
);
CREATE TABLE words_chat (
cid BIGSERIAL PRIMARY KEY,
created timestamptz NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg textNOT NULL
);
Then I put 2 users into the game #10 and they start chatting:
-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100,
'Nice user', 1), ('def', 200, 'Bad user', 2);
-- put these 2 users into a game #10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);
-- both users in the game #10 start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');
Here is my custom stored function (in SQL, I would prefer not to switch to
PL/pgSQL):
CREATE OR REPLACE FUNCTION words_get_chat(
in_gidinteger,
in_social integer,
in_sidtext
) RETURNS TABLE (
out_mine integer,
out_msg text
) AS
$func$
SELECT
CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END,
c.msg
FROMwords_chat c
JOINwords_games g USING (gid)
JOINwords_users u ON (u.uid IN (g.player1, g.player2)
-- The condition below is broken if both users are not muted
AND (u.muted OR (c.uid = u.uid AND NOT u.muted)))
JOINwords_social s ON (s.uid = u.uid)
WHERE c.gid= in_gid
AND s.social = in_social
AND s.sid= in_sid
ORDER BY c.CREATED ASC;
$func$ LANGUAGE sql;
For a chat of a bad and a nice user it seemingly works:
SELECT words_get_chat(10, 100, 'abc') AS nice_user;
SELECT words_get_chat(10, 200, 'def') AS muted_user;
But if you change both users to be not muted - it will break and they only
will see their own messages.
I have tinkered a lot with my db fiddle... but still cannot figure it out
Thank you!
Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats by other users, by just replacing the numeric "uid"... So I try your suggestion with: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=48d4bef569d966021e94c72f86d9fce5 CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users u1 ON (u1.uid = g.player1) JOINwords_users u2 ON (u2.uid = g.player2) JOINwords_social s ON (s.uid IN (u1.uid, u2.uid)) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid ORDER BY c.CREATED ASC; $func$ LANGUAGE sql; ...but how to bring the u1.muted or u2.muted there? Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Hi Ron, On Wed, May 4, 2022 at 4:56 PM Ron wrote: > > How do other web sites know to present only "my" data, even though they > don't encode "my" user id in the URL? > > that is the usual pattern with OAuth provided by: Facebook, Google, Amazon, Huawei, etc... After you auth with them in a game like mine, they give you a social network id, which is a string. Noone else gets that str. And then I (as game dev) use that str to id the user and when the user is visiting my for the 1st time, I give him a numeric id in my game. And an "auth" str generated by my game. Etc... it works ok. Regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
My real SQL function has one more param, an "auth" string generated by my game, which complements the social network id "sid". I have just omitted it in my test case. >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, I am trying your suggestion: On Wed, May 4, 2022 at 4:27 PM David G. Johnston wrote: > Assuming the base query is capable of returning all related chat messages > for both users (I'd probably place that portion into a CTE) the rows you > want to filter out are those whose c.uid is not my own, but only if their > muted property is true. It makes it easier to understand if you join > words_users twice, defining one as "them" and one as "me". Then you can > say something like: WHERE (c.uid = me.uid) OR NOT(them.muted) > > like this: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=4ab6a09cddae26a11140202fdc41cf5c CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2)) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2)) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; $func$ LANGUAGE sql;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, I try then the following - On Wed, May 4, 2022 at 5:28 PM David G. Johnston wrote: > You missed quoting the part where I describe the on clauses you need to > distinguish between "them" and "me" > > Me: u.uid in (player...) and (s.uid = u.uid) > Them: u.uid in (player...) and (s.uid <> u.uid) > > https://dbfiddle.uk/?rdbms=postgres_14&fiddle=048b9b9c7c55256c1a478d7c90cd2667 SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> s.uid) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; And get the syntax error which don't quite understand: ERROR: missing FROM-clause entry for table "s" LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) ^ Probably because "myself" needs "s" and vice versa?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I am probably needing LEFT JOIN LATERAL here (and am completely lost)? Or to switch to CTE as you suggest
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I try with a CTE but cannot figure the syntax: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=acd6d06a7ea2efc73a0771530832d77e WITH cte AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = cte.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> cte.uid) JOINcte WHERE c.gid= in_gid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; ERROR: syntax error at or near "WHERE" LINE 67: WHERE c.gid= in_gid ^ And if I remove the "JOIN cte" line, then the error is: ERROR: missing FROM-clause entry for table "cte" LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) ^ >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43a33374d15a9330145007702138822c WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ), opponent AS ( SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE player1 END FROM words_games WHERE gid = in_gid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROM myself CROSS JOIN opponent WHERE (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; but the error is: ERROR: missing FROM-clause entry for table "myself" LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
I think I am very close with the following CTE, but do not understand how to bring it into the main SELECT query: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ee264dc98b44dee75aa4523164a327b3 WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.CREATED ASC; The error message is: ERROR: missing FROM-clause entry for table "myself" LINE 64: ...uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Is that the right way to do it? https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7bd74243397da61ddc4c216ad919c7cc WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMmyself JOINwords_chat c ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
David, thanks but what do you mean by the last comment - On Wed, May 4, 2022 at 7:44 PM David G. Johnston wrote: > Using (SELECT uid FROM myself) provides the same result without the > from/join reference; the usage in the case and the where clause could be > rewritten to use opponent.uid so myself.uid only appears once. > > I have applied your first 2 comments in https://dbfiddle.uk/?rdbms=postgres_14&fiddle=c3982c2b5e71369f3c92ee0c06dc29bf WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINmyself ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; but where to put the (SELECT uid FROM myself), I do not understand?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good morning, this is a very insightful comment (among many) by you, David - On Wed, May 4, 2022 at 5:40 PM David G. Johnston wrote: > Well, that is basically why I was going on about the oddity of having > social be a part of the main query. Personally I would write it as > "myself.uid = in_uid", but you don't have an in_uid to reference. Decide > how you want to do something equivalent. > > so I will rewrite the stored functions in my game to be like that, to separate auth from functionality - https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dbdf1a6b82f7232be45e23b8139a8f0e CREATE OR REPLACE FUNCTION words_get_uid( in_social integer, in_sidtext ) RETURNS integer AS $func$ SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid; $func$ LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user; SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user; Thanks Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Good evening, I still have a problem with my JOIN expression - when I add more games, then messages from other games are displayed: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e2ff211f59090d1eeab879498148f907 CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_game text, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, 'game #' || c.gid, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; I have tried making the JOIN words_users opponent even more restrictive with: JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) but still messages from the game #20 are displayed, even though I pass in_gid = 10 Best regards Alex
