Questions tagged [postgresql-11]
PostgreSQL version 11
170
questions
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 ...
