Questions tagged [postgresql-11]

PostgreSQL version 11

55 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
89 views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
3
votes
1answer
1k views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
2
votes
0answers
323 views

PostgreSQL logical replication and partition table

We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11. But we have a big problem. We ...
2
votes
0answers
44 views

How do I prevent PostgresQL users/roles from having access to any administrator functions?

When I create a user with CREATE ROLE user123 LOGIN on my system it is able to login and immediately perform \du and interact with pg_stat_activity, and probably many other things. I have ...
1
vote
1answer
28 views

Speed up PostgreSQL logical decoding plugin

I'm writing a new logical decoding output plugin for PostgreSQL inspired by wal2json. But mine produces JSON by using SPI to repeatedly invoke to_json(...) on column values (instead of wal2json's ...
1
vote
0answers
65 views

How to get value by key from unstructured json column in Postgres 11

I have jsonb column with unstructured json. It can contain any combination of keys and arrays. I need to find if the value of key "Grade" contains the specified string. I managed to do that in ...
1
vote
1answer
50 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...
1
vote
1answer
102 views

PostgreSQL planner over estimates and self join query large table optimization

PostgreSQL 11.5 4 core, 32gigs 250gig SSD Not using default configuration, tuned to fit system memory and cores. Table+index size 14Gigs I'm working on a query that self joins to a table via a ...
1
vote
0answers
32 views

Brand new postgres setup - what to check first?

My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. ...
1
vote
1answer
233 views

Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements. SELECT u....
1
vote
1answer
99 views

PostgreSQL: Check the structure of foreign tables

How can I check if the structure of a foreign table was changed in the source database in order to know when to re import it. Foreign table was imported by foreign-data wrapper postgres_fdw (...
1
vote
0answers
39 views

Order of logical decoding output on Postgres 11

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows? CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ,name TEXT ) ; CREATE TABLE t2 ( t2_id INTEGER ...
1
vote
0answers
35 views

Hosting postgres wal logs on a separate dedicated server with failover setup

a postgres novice here, so sorry if my questions don't sound meaningful. My postgres server setup goes as follows: Master(on server A), Upstream(on B, receiving wal from A), Downstream(on C, ...
1
vote
3answers
60 views

PostgreSQL (big data): search by tags and order by timestamp

We need to add a search feature on a large table (200M+ rows): item_id | tags | created_at | ... ------------------------------------------------------------------- ...
0
votes
0answers
7 views

archive command in postgresql-11 does not work

I am new in postgresql and using verson 11. I am trying to do point in time recovery and my archive command does not transfer pg_wal files. I have setup in my postgresql.conf file archive_command = '...
0
votes
0answers
8 views

Query fails unless used with an WITH statement: ERROR: type of parameter 2 (changed) does not match that when preparing the plan (record)

I've defined a function taking two RECORDs (which I pass to to_jsonb() and then diff). Passing a row works if I beforehand wrap the tables in a WITH x (SELECT * from table) but not if used directly. ...
0
votes
0answers
11 views

PostgreSQL: Disable foreign constraints in order to speed up INSERT operation

I'm testing different approaches in order to speed up the INSERT operation and found that I have false assumptions about DEFERRABLE constraints. Suppose we have a table a with 2000000 rows and a table ...
0
votes
0answers
9 views

Is there a way to recover a dropped database in postgresql-11

I am using postgresql-11, I have dropped by mistake the wrong database with dropdb command and I don't have any backup for it. Is it possible to recover the dropped database or the data inside it ...
0
votes
1answer
100 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
0
votes
0answers
18 views

Google CloudSQL - cannot delete created role

I created user observer using only postgres user on my PostgreSQL 11 instance and I don't need it anymore. But after all I can't delete it - pgAdmin showing me that my user have connect privileges ...
0
votes
1answer
43 views

Compress JSON String Stored in PostgreSQL, such as MessagePack?

JSON strings are currently being stored in a PostgreSQL 11 table in a text field. For example, a row can have the text field asks containing the string: {"0.000295":1544.2,"0.000324":1050,"0.000325":...
0
votes
0answers
39 views

adding case statement in where clause leads to poor plan choice

We have a large number of procedures that use case statements in where clauses--essentially, we want to run the same procedures at different times, and we set "flags" in the database to change the ...
0
votes
0answers
9 views

Function returning a table has open parenthesis and single quote mark in results

I have a function returning a table and in the results I have weird results. Result should be (A,somereturnedtextvalue), what I get is (A,"somreturnedtextvalue There is no closing quote or ...
0
votes
1answer
17 views

Need a function to run different queries based on parameters sent subquery must return one column error

I am new to advanced sql programming. I am storing constants. Constants have three possible scopes: local, user and global. Two tables: s0constants_user holds set values of constants s0constants ...
0
votes
0answers
13 views

Calculating Total Time Taken to Replicate

I wanted to know whether there are any open source tools or in built functions for generating replication metrics. Basically i wanted to calculate the time difference between changes in the Master vs ...
0
votes
0answers
36 views

Is it safe to manually rewrite a table to eliminate bloat with no downtime? (similar to pg_repack)

Our users table used to contain very large hstore columns which were updated very frequently. This was terrible design and led to dramatic bloat. These columns have now been moved to a separate table ...
0
votes
1answer
52 views

Postgres Minor Version Upgrade from 11.4 to 11.7

I am new to PostgreSQL database. I want to upgrade the PostgreSQL version from 11.4 to 11.7. what are the steps involved can anybody tell. It will be helpful if anyone can share SOP. Thanks
0
votes
0answers
17 views

Does PostgreSQL have a feature to update the “internal” constraint names and whatnot?

This always happens: I design (create) a series of tables with PKs, FKs, UNIQUEs and sometimes indexes. pgAdmin 4 seems to add/encourage indexes added when making FKs. I never pick a name myself, but ...
0
votes
0answers
14 views

How to include a common set of columns in postgresql ddl definition?

I have a lot of big complicated tables and am putting together VIEWs for REST access, many of which share a subset of common columns (maybe 50 or so.) I sooo desperately want to do this: db=# BEGIN; ...
0
votes
0answers
20 views

Filtering on JOIN condition before executing GROUP BY

Have the following function: CREATE FUNCTION Game.operator_quotes(i_operator_name TEXT, i_locale_id SMALLINT) RETURNS TABLE ( name TEXT, rarity SMALLINT, quotes JSONB )...
0
votes
0answers
23 views

How to create user(read-write & readonly) and admin roles for an existing postgres database?

We have an existing postgres database in production with a superuser adm that is being used to do everything. Our web application connects to the database using the same user and also the ...
0
votes
1answer
48 views

Can I change the wal_level from logical to replica? If so are any impact on the replication process?

Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible? For example if there is a already a logical ...
0
votes
0answers
14 views

Postgres procedures and transaction management

There is a new feature introduced in postgresql 11: creating procedures. There is some info out there about transaction management inside them, but what leaves me blank is whether a transaction starts ...
0
votes
1answer
26 views

Can we allocate 10 gb RAM to postgres out of 30gb

Team, Can we allocate 10 gb RAM to postgres out of 30gb on Linux machine. If yes can someone share me the steps
0
votes
1answer
58 views

Why does a CTE cause a full table scan?

UPDATE I've posted a newer more focused question How to use index for simple time range join? I'll keep this for reference. ORIGINAL I have a large (~100 million rows) timeseries table t_16 where ...
0
votes
0answers
27 views

Postgresql strange backup message with docker

I'm trying to backing up my remote PostgreSql database into the local one. The backup command is the following: docker run --rm -e PGUSER=user -e PGPASSWORD=password -e PGDATABASE=db -it postgres:11....
0
votes
0answers
56 views

Postgres - Partition pruning and query performance

I created a fact table with range partition on a date column and when a SQL statement is executed like this: Select * from my_table Where dt_col between to_date('01/01/2017','mm/dd/yyyy') ...
0
votes
0answers
22 views

Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?

I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users The API json is 4 layers deep before I get to the items ...
0
votes
0answers
85 views

Barman + repmgr failover process

I am about to build a PostgreSQL 11 cluster with Repmgr and Pgpool II, and I want to use Barman for backups. The result will be a PostgreSQL cluster with one master node and two standby nodes: ...
0
votes
1answer
30 views

Converting ticks into range bars

You have a tick table with 3 columns CREATE TABLE tick ( symbol character(9) NOT NULL, price real NOT NULL, "time" timestamp with time zone NOT NULL ); INSERT INTO ...
0
votes
0answers
265 views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
0
votes
1answer
62 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
0
votes
0answers
97 views

Postgres uses sequential scan with OR condition, but index scan with AND condition

We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
0
votes
1answer
366 views

Postgresql - how to list indexes on a table that is partitioned?

This command works for non-partitioned tables: select * from pg_indexes But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition. ...
0
votes
0answers
20 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
1answer
40 views

How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

I have created a group (role) called "employees" and I've created some users that are its member and that inherit its rights. I have a database owned by the group "employees". The goal: To setup ...
0
votes
0answers
98 views

Does Postgresql logical replication support hot standby?

Postgresql has two ways of replication Physical replication Logical replication with physical replication everything is send over and the servers must have the same major release. Which makes ...
0
votes
0answers
41 views

Postgresql: how to create a View combining regular Tables A, B, C and pivoted table D with a json field?

I have created a standard view, VIEW, that includes different fields from different tables: SELECT e.id AS "Employee Id", concat(e.first_name,' ',e.additional_family_name,' ',e.family_name) AS "...
0
votes
1answer
35 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
0
votes
0answers
101 views

Select JSON elements from nested documnet

I have a JSON document such as: { "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } } Here is the table definition: CREATE TABLE "v22"."assets_exp" ( "_id" ...