[jetty-users] How to include another web page in a page served by Jetty?

Hello, I am using Jetty 9.4.27.v20200227 - 1) in production server to server Wordpress/PHP and a Java servlet 2) in test server started locally at my Macbook to serve the Java servlet I wonder if there is some kind of configuration statement for Jetty which would allow me to include other web

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

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 >

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

Syntax error for UPDATE ... RETURNING INTO STRICT

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

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

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 >

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,

[jetty-users] How to upload a BufferedImage using Jetty HTTP client and MultiPartContentProvider?

Good afternoon, With Jetty 9.4.21.v20190926 I run a custom servlet (a WAR-file), which is able to generate images like this one: https://slova.de/ws/puzzle2?mid=669600 by the following code: @Override protected void doGet(HttpServletRequest httpReq, HttpServletResponse httpResp) throws

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

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

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

Re: 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? >

Re: Calling jsonb_array_elements 4 times in the same query

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

Re: Calling jsonb_array_elements 4 times in the same query

Thank you 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): > > > >

Calling jsonb_array_elements 4 times in the same query

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

Re: [jetty-users] ImageIO in servlet: should I close the httpResp.getOutputStream() and how to set Content-Length?

Thank you, Joakim! ___ jetty-users mailing list jetty-users@eclipse.org To change your delivery options, retrieve your password, or unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users

Re: [jetty-users] ImageIO in servlet: should I close the httpResp.getOutputStream() and how to set Content-Length?

Hi Bill, no - I have just listed only a part of my real program which scales, composes several PNGs to one and also adds shapes and texts to it. So I still wonder: 1. How to set Content-Length (despite gzip encoding)? 2. And if I should close the output stream or not (won't that break

[jetty-users] ImageIO in servlet: should I close the httpResp.getOutputStream() and how to set Content-Length?

Good morning, I am using Jetty 9.4.21.v20190926 - as a standalone server behind HAProxy and also I compile my custom WAR servlet against it. It serves Websockets, GET, POST requests and works very well, thank you! However now I would like to generate a PNG file using ImageIO and while the code

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

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

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

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

Re: How to return ARRAY from SQL function?

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

Re: How to return ARRAY from SQL function?

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

How to return ARRAY from SQL function?

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

[jetty-users] How to pass different values at runtime (like db credentials) to a WAR file?

Good evening, I am running Jetty on CentOS 7 Linux with the following command: /usr/bin/java -Djdbc.drivers=org.postgresql.Driver -jar /usr/share/java/jetty-distribution-9.4.18.v20190429/start.jar jetty.home=/usr/share/java/jetty-distribution-9.4.18.v20190429 jetty.base=/var/www/jetty-base-ru

Re: aggregate functions are not allowed in UPDATE

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

Re: aggregate functions are not allowed in UPDATE

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

Re: aggregate functions are not allowed in UPDATE

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

Re: aggregate functions are not allowed in UPDATE

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

Re: aggregate functions are not allowed in UPDATE

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

aggregate functions are not allowed in UPDATE

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

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

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

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

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

Re: Not sure which part of the query needs optimization

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

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

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

Not sure which part of the query needs optimization

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

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

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

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

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

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

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

Re: [jetty-users] After upgrading to 9.4.14.v20181114 getContentAsString() prepends previously fetched strings

Hi - On Mon, Dec 3, 2018 at 10:20 AM Simone Bordet wrote: > > You cannot reuse BufferingResponseListener, it was never intended to > work in this way. > It was just a coincidence that it was working before. > Allocate the BufferingResponseListener for each request/response. > > thank you, this

[jetty-users] After upgrading to 9.4.14.v20181114 getContentAsString() prepends previously fetched strings

Good afternoon, I run a custom WebSocketServlet for Jetty, which sends short text push notifications (for an async mobile and desktop word game) to many platforms (Facebook, Vk.com, Mail.ru, Ok.ru also Firebase and Amazon messaging) using a Jetty HttpClient instance: public class MyServlet

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

Re: [jetty-users] Background thread in Jetty

Hello, On Mon, Jul 2, 2018 at 3:41 PM, Simone Bordet wrote: > On Tue, Jun 19, 2018 at 4:35 PM Josh Spiegel wrote: > > > > I am using Jetty and I would like to create a background task that > periodically wakes up and checks on some cached state, possibly doing some > cleanup. I see several

Re: UPDATE from CTE syntax error

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

Re: UPDATE from CTE syntax error

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

UPDATE from CTE syntax error

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

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

Thanks, now I see the difference

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

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

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

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

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

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

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

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

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 (

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

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

Re: Adding AVG to a JOIN

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

Adding AVG to a JOIN

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

Re: 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,

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

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

Multiple records returned by a JOIN

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

Re: [jetty-users] How to escape Unicode with JSON.toString()?

Hello Greg, On Thu, Mar 29, 2018 at 9:47 AM, Greg Wilkins wrote: > Which characters do you want us to use \u encoding for? > US_ASCII? >>ISO_8859? or just chars that would encode to 3 byte utf8? > > Maybe we could... we'll discuss. > The other option is to do that

Re: [jetty-users] How to escape Unicode with JSON.toString()?

Hi Joakim, no, I am already sending "application/json; charset=utf-8" - so that is not a problem. And also ADM error talking about XML unfortunately means nothing, they mention XML in all their error messages I have seen sofar while developing (while their API expects JSON being POSTed). I

[jetty-users] How to escape Unicode with JSON.toString()?

Hello fellow Jetty users and developers, is it please possible to escape UTF-8 characters when using org.eclipse.jetty.util.ajax.JSON.toString() method? I understand that it might be an internal library, but until now it works well for me in a servlet which among other tasks sends push

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) =

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

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

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

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

Thank you, Ivan! I am trying to apply your suggestion to my table - On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko < i.panche...@postgrespro.ru> wrote: > Hi Alex, > > SELECT string_agg(x->>'letter','') FROM json_array_elements( > > '[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col":

Extract elements from JSON array and return them as concatenated string

Good afternoon, A PostgreSQL 10.3 table contains JSON data like: [{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}] Please suggest, how to

Re: [jetty-users] Escape HTML in Jetty

Thank you, I will switch to using OWASP ___ jetty-users mailing list jetty-users@eclipse.org To change your delivery options, retrieve your password, or unsubscribe from this list, visit https://dev.eclipse.org/mailman/listinfo/jetty-users

[jetty-users] Escape HTML in Jetty

Good evening, what would be a method in Jetty to escape HTML characters in a String? Is StringUtil.sanitizeXmlString() suitable for that? Thank you Alex ___ jetty-users mailing list jetty-users@eclipse.org To change your delivery options, retrieve

Re: [jetty-users] Upgrade 9.4.6.v20170531 -> 9.4.8.v20171121:

Nevermind, sorry - On Mon, Mar 5, 2018 at 10:57 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Here the full log: https://gist.github.com/afarber/e91c4670b491e15c6de007b7eb1e306d I have fixed my problem by adding the following to WAR pom.xml: java

[jetty-users] Upgrade 9.4.6.v20170531 -> 9.4.8.v20171121:

Hello, after upgrading from Jetty version 9.4.6.v20170531 to 9.4.8.v20171121 I unfortunately see the following errors when starting it on CentOS Linux 7 by the command /usr/bin/java -Djdbc.drivers=org.postgresql.Driver -jar /usr/share/java/jetty-distribution-9.4.8.v20171121/start.jar

[jetty-users] jetty-distribution-9.4.6.v20170531: java.lang.IllegalArgumentException: Cannot convert date

Hello and good morning, I have seen the following exception, but can not reproduce it yet. Could that be a minor bug in Jetty source code? (in the sense that possibly invalid date in HTTP header should not throw exception) 2018-03-05 09:06:01.739:INFO:slova:qtp1500056228-651: doGet:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: Selecting the most recent timestamptz

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

Re: Selecting the most recent timestamptz

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

Re: Selecting the most recent timestamptz

s/no it works/now it works/

Selecting the most recent timestamptz

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

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

Thank your for the comments

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

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

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

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

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

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

Re: [CentOS] /dev/md1 => 93% Used. Warning. Disk Filling up. - what would be safe to delete in /boot ?

Thank you Pete for the very insightful answer! This has worked like a charm - On Wed, Feb 7, 2018 at 10:16 AM, Pete Biggs wrote: > > In fact there are a number of tools to help you. By default yum keeps > 5 versions of old kernels (which is usually too many for the default

[CentOS] /dev/md1 => 93% Used. Warning. Disk Filling up. - what would be safe to delete in /boot ?

Hello CentOS users, in the recent time I keep getting the logwatch warnings from my 2 dedicated servers running CentOS 7.4.1708. I guess because of the numerous kernel updates (because of Spectre+Meltdown) in the near past? Could someone please suggest me, which files in my /boot partition

5 USD for PostgreSQL books at PacktPub

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

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

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

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

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

Selecting a JSON object of arrays from a PostgreSQL table

Hello and happy new year! I have prepared a simple SQL Fiddle demonstrating my problem: http://sqlfiddle.com/#!17/2c9c5/1 In a two-player game I store user chats in a table: CREATE TABLE chat( gid integer,/* game id */ uid integer,/* user id */ created

Re: [CentOS] File access in Apache 2.4

Hi David, On Tue, Nov 21, 2017 at 3:19 AM, david wrote: > I'm having file-access problems in Apache 2.4 under Centos 7. In > particular: > > - I have a file that's readable to every user and every application, > (writeable by only one user), but my CGI scripts cannot read it. >

Re: [GENERAL] Comparing epoch to timestamp

# EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE added > to_timestamp(0) UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE added > to_timestamp(0)

Re: [GENERAL] Comparing epoch to timestamp

Sorry, I probably had to call: # EXPLAIN SELECT ARRAY_AGG(hashed) FROM words_nouns WHERE EXTRACT(EPOCH FROM added) > 0 UNION SELECT ARRAY_AGG(hashed) FROM words_verbs WHERE EXTRACT(EPOCH FROM

[GENERAL] Comparing epoch to timestamp

Hello, in PostgreSQL 9.5 I have a table with 67000 records: # \d words_nouns Table "public.words_nouns" Column | Type | Modifiers -+--+--- word| text | not null hashed | text

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

Hello, I appreciate your comments, thank you

[GENERAL] Selecting a daily puzzle record - which type of column to add?

Good evening, In a word game I store all player moves in the table: CREATE TYPE words_action AS ENUM ( 'play', 'skip', 'swap', 'resign', 'ban', 'expire' ); CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY, action

[jetty-users] RewriteRegexRule /([0-9]+)$ to /player-$1 does not work, returns 404

Hello dear Jetty users, I would like to add a rewrite rule which would rewrite requests to numeric URLs like https://slova.de/5 to https://slova.de/player-5 i.e. insert the "player-" string before the matched number. After consulting

[jetty-users] LOG.info(), LOG.warn() not visible in log files

Good morning, on CentOS 7 Linux I have installed Jetty as: # java -jar /usr/share/java/jetty-distribution-9.4.6.v20170531/start.jar jetty.home=/usr/share/java/jetty-distribution-9.4.6.v20170531 jetty.base=/var/www/jetty-base --create-startd

<    1   2   3   4   5   6   7   8   9   10   >