Questions tagged [postgresql-11]

PostgreSQL version 11

Filter by
Sorted by
Tagged with
0
votes
0answers
9 views

How to move all schemas from postgres to userdb(user createdb database) in Postgres 11

End users are created all tables in PostgreSQL default database also in public schema. As per best practice, I want to move all tables from public schema of "postgres" database to user created ...
0
votes
1answer
10 views

pg_waldump: command not found

According PostgreSQL documentation I can use pg_waldump for display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster. I have clean installation of PostgreSQL-11 ...
1
vote
0answers
13 views

“Folder” structure of Postgres database missing in DBeaver

I guess I did a faulty database restoration or the dump did not have this data and the "folder like" structure was lost. This is a capture of Dbeaver showing what I mean with "foler like" structure. I ...
0
votes
0answers
26 views

Lost postgresql table after computer reboot [closed]

I am running postgres 11 on my dev machine. I created new table by rails migration, all was ok and I was running job writing to that table. Then suddenly my virtual machine froze completely, so I had ...
0
votes
0answers
8 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 = '...
1
vote
1answer
32 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 ...
0
votes
0answers
20 views

How to retrieve rows with max value in one column for each combination of other two columns? [duplicate]

I need to query an SQL database to find all records with max RATE for each combination of fields A and B. For example, consider the following table with 4 columns, A, B, RATE & VALUE: A B RATE ...
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 ...
2
votes
1answer
77 views

Query planner enerates different plans in psql and application for the same query

I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed. I want to identify ...
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
101 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 ...
1
vote
0answers
23 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 ...
2
votes
2answers
50 views

Increasing WAL Segment Size?

Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes? The reason for wanting to increase it from 16 MB ...
0
votes
1answer
18 views

creating new table partition with values already existing in the default partition

I have the following table: CREATE TABLE orders ( info_date date, country_code VARCHAR, order_total int, CONSTRAINT orders_pk PRIMARY KEY (info_date, country_code) ) PARTITION BY LIST (...
0
votes
1answer
49 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":...
5
votes
1answer
137 views

Include non-inserted rows as result of INSERT INTO … RETURNING

I'm using PostgreSQL 11. I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion. For ...
0
votes
0answers
40 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
1answer
47 views

How to “cascade delete” children without deleting parent?

I have a table, let's call it parent: CREATE TABLE parent ( id TEXT PRIMARY KEY, name TEXT, description TEXT, -- other parent attributes created_at TIMESTAMPTZ ...
0
votes
1answer
26 views

script to find all bigint that can be converted to int fields in postgresql performance

I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside. So, ...
0
votes
1answer
23 views

Is it possible to index or cache non-immutable function (based on date)

I have a function in my Postgres database where the calculation is based on how old a record is - so given the same parameters, it always outputs a different value. However, I'd like to index or ...
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
2answers
28 views

How to sort aggregate function call

If I have an expensive function call that takes a JSONB and returns an INT, e.g. CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS How do I go about ordering the aggregate result ...
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 ...
6
votes
2answers
396 views

How to swap primary key between records in postgres

I can't seem to find a way to achieve this transactionally (or not) What I need to achieve is non-standard, hence my difficulty finding a solution. I need to code a data migration tool to "swap" old ...
0
votes
0answers
38 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 ...
1
vote
1answer
36 views

Database Size is Different after pg_restore (389 GB vs 229 GB)

I created a backup of my database in a PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_dump: PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development ...
0
votes
1answer
93 views

PostgreSQL: How can I list the tables to which a sequence belongs?

I know I can list all sequences with this: SELECT * FROM information_schema.sequences; But I need to know by which table this sequence is used. The reason I want to do this is to find out which ...
0
votes
1answer
31 views

PostgreSQL vacuum starting too late (every 100k instead of 10k dead rows)

I have a large table (50M) and autovacuum seems to work properly in general. However I started monitoring it with Datadog and I have noticed something strange: select relname, reloptions from ...
3
votes
0answers
91 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 | ...
0
votes
1answer
41 views

PostgreSQL bad plan: the frequency of a tag depends on the project!

I try to simplify a problem that I've been investigating for weeks. Suppose that you have a large table like this: SUBSCRIBERS ---------------------------- id | project_id | tags | ... --------------...
1
vote
1answer
37 views

What is the alternative of the combination of two pattern matching conditions?

So what i want to do is to search in my table data for elements that in their name exists the word first and the word second. These two words can be seperated or attached to each others. A translation ...
0
votes
1answer
40 views

using SUM on foreign view's keys grouped on multiple key/values?

I had a question on executing SUM on rows from one table and inserting them in a column on another table where the calculated rows shared id with a row in the new table. The solution worked very well ...
0
votes
1answer
57 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

Postgres permission for new objects [duplicate]

Postgres version: 11 I've got 2 roles - one used for read and one for write on the same database. CREATE ROLE reader WITH login; CREATE ROLE writer WITH login; CREATE DATABASE thedatabase; GRANT ...
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; ...
1
vote
0answers
66 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 ...
0
votes
1answer
35 views

PostgreSQL Error: unexpected file found in temporary-files directory

PostgreSQL 11.2 has been running properly inside a Docker container until I did a docker-compose up to start a new container for another app, then accidentally press Ctrl+C, which killed all the ...
1
vote
2answers
35 views

How to maintain visibility of all new transactions in append-only PostgreSQL DB without scanning the whole table

Situation PostgreSQL v11 I have a database with a dozen tables. No rows are ever DELETEd or UPDATEd. A bulk of data is INSERTed to all the tables in a 'few' (up to 1,000) transactions every day. ...
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
1answer
105 views

SELECT query on unique number field returns no rows

This is a cross-post of an issue I field on the GORM Github repo. I am unsure if the issue is with Postgres 11 or with GORM. See: https://github.com/jinzhu/gorm/issues/2872 I have a table containing ...
-1
votes
1answer
40 views

Postgres: Filter aggregate groups by value of non-aggregate column

Say I have an event table with two columns: CREATE TABLE evt ( ts INT, evt INT, CONSTRAINT pk_evt PRIMARY KEY (ts, evt) ); The first column represents a timestamp and the second column ...
0
votes
0answers
29 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 ...
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 | ... ------------------------------------------------------------------- ...
1
vote
2answers
61 views

Determining Whether Query Load is Too High for PostgreSQL Server

I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second. ...
-2
votes
1answer
28 views

Modify table by squashing rows by truncated hour

I have a table ledger which represents the ledger book. date | user_id | usd_value | eur_value ----------------------------+---------+-----------+----------- 2020-01-13 19:00:...
2
votes
1answer
38 views

Ensuring no unwanted tables have changed after a risky operation is performed

Sometimes I want to make sure that after a risky operation such as database moving into another server or schema changes via migration scripts no data corruption or loss (due to human error and not ...
0
votes
1answer
50 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 ...