Thank you, that was it!
Good morning,
I am seeking for a general advice if it would be a good idea for a
PostgreSQL/PostGIS using application to add a third schema.
I am using postgis/postgis Docker image which comes with "public" schema.
Then I have loaded OpenStreetMap data into the database and have created
"osm_sch
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
postgresql-jd
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 getti
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, COALESCE(fin
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:
https://explain.depesz.com/s/
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)
>
> 0.
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 ma
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 NO
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 depend
Good evening,
I have prepared a simple test case for my question:
https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0
There I create 3 tables:
CREATE TABLE users (
uid SERIAL PRIMARY KEY,
avg_time TEXT
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 INTEGER NOT NULL REFERENCES
Unfortunately, I don't understand your advice, David -
On Tue, Jan 15, 2019 at 5:46 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber
> wrote:
> > When I am trying
> >
> > WITH diffs AS (
> &
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
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
>
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) OVER(PAR
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
e
Hello,
in PostgreSQL 10.8 the following works -
words_ru=> SELECT ARRAY[
words_ru-> '*', '*', 'А', 'А', 'А', 'А', 'А', 'А', 'А', 'А',
words_ru-> 'Б', 'Б', 'В', 'В', 'В', 'В', 'Г', 'Г', 'Д', 'Д',
words_ru-> 'Д', 'Д', 'Е', 'Е', 'Е', 'Е', 'Е',
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 are
Thank you -
On Tue, Jun 18, 2019 at 3:10 PM Tom Lane wrote:
> Alexander Farber writes:
> > And then I shuffle the letters by -
>
> > CREATE OR REPLACE FUNCTION words_shuffle(in_array text[])
> > RETURNS text[] AS
> > $func$
> > SELECT array_
Good morning,
in Microsoft Azure how to add pgbouncer or a similar software enabling
multiple simultaneous connections?
After reading https://docs.microsoft.com/en-us/azure/postgresql/overview I
could not find such options...
Thank you
Alex
Thank you Adrian -
On Sat, Jun 29, 2019 at 3:59 PM Adrian Klaver
wrote:
> On 6/29/19 12:34 AM, Alexander Farber wrote:
>
> Not from within. Supported extensions:
>
> https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions
>
> Using search term 'azure po
Good evening,
I use the following postgresql.conf in my Dockerfile
( the full version at https://stackoverflow.com/a/78243530/165071 ),
when loading a 28 GByte large europe-latest.osm.pbf
into PostgreSQL 16 with PostGIS extension:
echo "shared_buffers = 1GB">> $PGDATA/postgres
Thank you, Justin -
On Sat, Mar 30, 2024 at 4:33 AM Justin Clift wrote:
> On 2024-03-30 05:53, Alexander Farber wrote:
> > I use the following postgresql.conf in my Dockerfile
> > ( the full version at https://stackoverflow.com/a/78243530/165071 ),
> > when loading a
Turning fsync = off has resulted in no noticable build time reduction for
my Dockerfile with OSM Europe data, but still thank you for the suggestion!
>
Thank you for the insights
Good evening,
in PostgreSQL 13.2 I have a custom stored function:
CREATE OR REPLACE FUNCTION words_join_new_game(
in_uid integer,
in_bid integer
) RETURNS table (
-- the player to be notified (sometimes there is no such
user)
Tom, you are so eagle eyed -
On Mon, Mar 8, 2021 at 8:53 PM Tom Lane wrote:
> Alexander Farber writes:
> > However there are cases, when I only have the out_gid value, I do not
> want
> > to return any other values.
> > My question is: do I have to set the other OUT p
Hello,
when I search for a non existent word in the two tables hosted in
PostgreSQL 14.1 then I get zero records as expected:
words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1
FROM words_verbs WHERE word = 'ABCDE';
?column?
--
(0 rows)
But when I try to use the sa
Hi Laurenz, thanks for your reply, but I think it is wrong -
On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe
wrote:
> One reason could be index corruption. If one query uses an index and the
> other doesn't,
> that could lead to different results.
>
> The other option is of course a trivial error,
Ah, I have to do
RETURN '___WRONG___';
and not
out_text := '___WRONG___'
RETURN;
Ah, thank you Ralf! That has explained it (because I was still grumbling...)
On Wed, Feb 16, 2022 at 4:44 PM Ralf Schuchardt wrote:
> You must rather end the assignment with a semicolon:
>
> out_text := '___WRONG___';
> RETURN;
>
> Otherwise what really happens is:
>
> out_text := '___WRONG___'
s/grumbling/wondering/
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 timesta
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;
>
>
>
Also got this nice suggestion at https://stackoverflow.com/q/48050127/165071
-
SELECT COALESCE(
json_object_agg(
gid, array_to_json(y)
), '{}'::json)
FROM(
SELECT gid,
array_agg(
json_build_object(
'uid', uid,
'created',
Hello fellow PostgreSQL users,
there is currently a sale for books at
https://www.packtpub.com/tech/PostgreSQL
I am not affiliated in any way with them, it is just a "heads up".
For myself I have pre-ordered
https://www.packtpub.com/big-data-and-business-intelligence/mastering-postgresql-10
Reg
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 CHECK
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 in one index.
>
I hope to
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
--+--+---+-
Thank your for the comments
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 |
s/no it works/now it works/
Ahh, thank you Laurenz -
On Thu, Feb 22, 2018 at 10:18 AM, Laurenz Albe
wrote:
>
> You are only checking if there is a later timestamp *for the same "mid"*.
>
> Since the two rows have different "mid", they are not compared.
>
no it works -
# select m.played, m.mid, m.action, m.gid, m.uid from
Hi Ken -
On Thu, Feb 22, 2018 at 10:24 AM, Ken Tanzer 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.played, m.mid, m.action, m.gid, m.ui
Thank you Torsten,
On Mon, Dec 2, 2024 at 12:32 PM Torsten Krah wrote:
> Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber:
> > But I am yet not sure how to enable listening at localhost again
>
> Instead of using a TCP/IP connection, why not use the unix socket
Hi again, I would like to share a slightly better workaround
(does not restart PostgreSQL) for enabling localhost connections
(for example for servlets) in a postgres:17-alpine3.20 based Dockerfile:
RUN sed -i "s/listen_addresses=''/listen_addresses='localhost'/"
/usr/local/bin/docker-entrypoint.s
My problem is related to
https://github.com/docker-library/postgres/pull/440/files
But I am yet not sure how to enable listening at localhost again
Yes thank you, I have tried specifying the port at the CLI too... that did
not help.
But restarting PostgreSQL in my
custom /docker-entrypoint-initdb.d/run-after-initdb.sh
has helped, even though I am not sure if it is the best way:
#!/bin/sh
LANGUAGES="de en fr nl pl ru"
createuser --username=
Good evening,
I am trying to create a Dockerfile for development purposes, which
would run Jetty and PostgreSQL. The PostgreSQL related part is below:
FROM postgres:17-alpine3.20
RUN apk update && apk upgrade && apk add --no-cache curl wget openjdk21 tini
# Tell docker-entrypoint.sh to create su
Thank you, Tom -
On Sun, Dec 1, 2024 at 8:58 PM Tom Lane wrote:
> Alexander Farber writes:
> > TLDR "psql words_de" works, but "psql -h localhost words_de" does not
>
> I'd try connecting the first way and seeing what "show
> listen_addresses&
I have tried changing the line in the Dockerfile, but it has not helped yet:
ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set listen_addresses=localhost"
ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set
Hi fellow PostgreSQL users,
I have prepared a DB Fiddle https://dbfiddle.uk/BCXD_Bo2 for my question
and I will also show my complete SQL code below -
I am trying to store data in a vehicle_data table and each data record has
an expires_at column:
-- Create table for customer IDs
CREATE TABLE cu
Thank you Giovanni, I did not expect this from NOW() and that is why I was
stuck.
I have decided to keep NOW() in my stored function in the hope it has
better performance.
To fix the issue I have rewritten my smoke tests to be plain SQL without
any transaction:
CREATE OR REPLACE FUNCTION test_st
Hello dear PostgreSQL users
I have prepared a https://dbfiddle.uk/vOFXNgns for my question and also
list my SQL code below.
I have created a countires_boundaries table, which I intend to fill with
.poly files provided at Geofabrik:
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE I
Thank you all. I would like to use a geometry type, but will it work well
with the Npgsql C# packages?
>
That seems to be the reason, thank you Adrian.
At first connecting from the other pod in the AKS cluster does not work:
$ PGPASSWORD=timeshift_pass psql --host=timeshiftservice --port=5432
--dbname=timeshift_database --username=timeshift_user
psql: error: connection to server at "timeshiftservice
Good evening,
I am building the following Dockerfile by the command:
# docker build -f ./Dockerfile --build-arg PGPASSWORD=timeshift_pass .
FROM postgres:17-alpine3.21
RUN apk update && apk upgrade && apk add --no-cache pg_top
ARG PGPASSWORD
# Tell docker-entrypoint.sh to create superuser "pos
Sorry, correction for the openssl command output:
$ openssl s_client -connect 172.21.32.4:5432 -starttls postgres
Connecting to 172.21.32.4
CONNECTED(0003)
Can't use SSL_get_servername
depth=2 C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root
G2
verify return:1
depth=1 C=US, O
Good afternoon,
I am using an Azure Database for PostgreSQL flexible server with no public
ip.
It is connected to a private VNet and I try to access it from an Alpine
Linux container with openssl and postgresql-client installed.
In Azure portal I have clicked the "Reset password" button, entered
Thank you for the comments, I must think some time how to handle this.
My intention has been to generate 2 random strings in an Azure pipeline,
save them as secrets in an Azure Key Vault (so that our C# ASP.Net app can
fetch them when starting up in Kubernetes) and also pass them to the
pipeline t
Ah my team mate has spotted the error for me.
And it paid off, that he has no experience with PostgreSQL yet :-)
Because the admin user for Azure Database for PostgreSQL flexible server is
not "postgres" but "postgresql"
Now the connection from a container in AKS works for me:
$ psql "postgresq
Good evening,
I am trying to convince my team (a small team at a big company) to move
from Azure Redis E10 to PostgreSQL, because I am tired of seeing how we
(poorly) try to re-invent an SQL database using Redis.
My problem is that the team does not want and I am the only one liking SQL
databases
Thank you for your comments.
I have installed "Azure Database for PostgreSQL flexible server"
Standard_D4ds_v5 (4 vCores, USD 50.30 per vCore)
128 GiB (USD 0.09 per GiB)
High availabilty: Not enabled
Resulting in USD 200 / month
101 - 163 of 163 matches
Mail list logo
