Questions tagged [postgresql-11]
PostgreSQL version 11
77
questions
0
votes
0answers
6 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....
0
votes
0answers
7 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 ...
4
votes
1answer
158 views
Postgresql numeric and decimal is automatically rounding off
CREATE TABLE IF NOT EXISTS ttable (
tcol decimal(9,7)
);
insert into ttable(tcol) values(17.4604786);
the value is getting stored as 17.46
Happens the same if I use decimal/numeric type
I am ...
-1
votes
0answers
7 views
Suitable driver not found despite providing correct Database path with JSF and PostgreSQL using Eclipse [migrated]
I tried to follow the tutorial as provided here to connect to a PostgreSQL database and display its contents on a Webpage usinf JSF. There are no visible errors on any of the code but the following ...
0
votes
1answer
42 views
`cube` query performance issues with ordering
I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the
But ...
1
vote
1answer
19 views
Use jsonb array inside where IN or ANY in PostgreSQL
I want to select all products that have active shipments (those have one or more shipment_ids in shipments table).
My product table has a jsonb column called data like this:
{
"name": "foo",
...
0
votes
1answer
19 views
CTE vs Function/trigger performance in Postgresql
I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the ...
1
vote
1answer
13 views
Getting 2 columns from sets of 2 rows in Postgresql
So I have my data organized as:
id(bigint) | report_item (varchar) | report_value (numeric) | stock_id (uuid) | date
1 | netIncome | 100 | stockid1 | 2019-05-01
2 | totalRevenue |1000 | stockid1 | ...
0
votes
1answer
14 views
Posgresql - deadlock on non related queries
I am using Postgresql 11.
I have found an error similar to following in my logs:
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 332091 waits for ShareLock on ...
0
votes
1answer
26 views
PostgreSQL >= 10 does not generate WAL if there are no transactions
I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions.
In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty ...
0
votes
1answer
27 views
json_object_agg errors on null in field name
PostgreSQL versions: my local install 11.3 and the below fiddle is on 10.0. Both behave the same.
I have a schema of pages, each page has sections and each section can have different kind of ...
0
votes
1answer
30 views
PostgreSQL logical replication slave - shouldn't it be read-only and available only for subscription updates?
I have database and table collection_city on one server. It has 21 rows. I have database and table collection_city on a second server. It also has 21 rows.
They both have this row:
tinker=# table ...
1
vote
1answer
30 views
PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?
I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data.
I am beginner ...
0
votes
1answer
22 views
PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?
I have database and table collection_city on one server. It has 21 rows.
I have database and table collection_city on second server. It also has 21 rows.
They both have this row:
tinker=# table ...
0
votes
1answer
24 views
PostgreSQL - how to list all UNIQUE indexes for specific database?
I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this.
I found these two queries.
...
0
votes
1answer
22 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
1answer
43 views
Postgres Logical Replication - “replication slot ”mysub“ was not created in this database”
I have two database servers 192.168.150.210 and 192.168.150.220
I created publication and subscription successfully for logical replication.
postgresql.conf file is modified on both servers.
...
-1
votes
2answers
26 views
index for ILIKE on postgresql, inconsistent performance
I have a table with about 64 Million rows, I have created the following index on the address_url column
CREATE INDEX trgm_idx_address_link
ON public.address_link USING gin
(address_url ...
0
votes
0answers
17 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 ...
1
vote
2answers
55 views
Postgres: why did this trigram index slow down regexp queries?
I have a TEXT keyvalues column in Postgres:
select * from test5 limit 5;
id | keyvalues
----+------------------------------------------------------
1 | ^ first 1 | second 3
...
0
votes
1answer
32 views
index does not get used when there is “OR” in the query
I have an index on the check_timestamp column, the index gets used by the following query
... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY check_timestamp NULLS FIRST LIMIT 10
...
0
votes
0answers
34 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 "...
9
votes
2answers
442 views
Does SELECT remove dead rows like VACUUM does?
I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards.
Test Data
Note: autovacuum is disabled
CREATE ...
1
vote
1answer
49 views
Postgres Column Level Security
I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are ...
0
votes
1answer
31 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 ...
3
votes
2answers
53 views
Breaking out of a recursive query in Postgres 11
I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query.
I have this table of domains:
...
0
votes
0answers
4 views
Reconfigure PostgreSQL Installation
On Windows (10), is there any way to reconfigure a PostgreSQL installation, specifically when the --with-openssl option was not used? I'm hoping to avoid having to backup, reinstall, and restore ...
0
votes
0answers
44 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" ...
1
vote
1answer
34 views
In PostgresQL, how do I make a query match exactly one row or fail?
I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when ...
0
votes
0answers
29 views
PostgreSQL GIN pg_trgm default operator class - Does Not accept data Type uuid
I was trying to create GIN Indexes for an Array that stores uuid data type.
CREATE INDEX IX_Gin_Column1 ON table_name USING gin (column1 gin_trgm_ops);
PostgreSQL raised an error:
ERROR: ...
1
vote
0answers
51 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 ...
0
votes
0answers
98 views
psycopg2.pool.PoolError: trying to put unkeyed connection
I'm trying to get connection pooling to work in my Python Flask web application. The code I am using is as follows:
from contextlib import contextmanager
import logging
from psycopg2 import pool, ...
0
votes
0answers
22 views
index by UDF not used when filtering by subquery column
I have created an index with an expression that calls a user defined function.
I expected that when a subquery selects this expression as a column I could filter by this column and have my index used. ...
0
votes
1answer
28 views
Java driver dont fire query-start probe in postgres 11
While using SystemTap to trace postgres session on PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit.
global livequeries
probe process("/usr/...
1
vote
2answers
236 views
Returning multiple result sets from server-side procedures in Postgres 11
This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets.
➥ Did this feature indeed appear in ...
2
votes
0answers
38 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 ...
4
votes
1answer
65 views
postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client
We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
1
vote
1answer
23 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 (...
0
votes
0answers
49 views
ERROR: cannot update table “pg_enum” because it does not have a replica identity and publishes updates
I was trying to update the enum label using the following SQL query
UPDATE pg_enum SET enumlabel = 'Cash Recivied' WHERE enumtypid = '1203702' AND enumlabel = 'Cash';
but it throws an error
ERROR: ...
1
vote
1answer
36 views
PostgreSQL Index Only Scan can NOT return ctid
I have this table in PostgreSQL 11:
CREATE TABLE A (id bigint PRIMARY KEY, text text)
Now I want to fetch the ctid of rows meeting some condition like id = 123.
However, even using pg_hint_plan to ...
1
vote
1answer
97 views
Syntax error when using exception inside for loop [closed]
I am trying to create the following procedure on postgresql
CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM some_table ...
0
votes
1answer
35 views
Optimizing one-way syncing of large (wide) tree
I deal with several trees that can vary in size (they all have depths of ~8-10) but it's not unusual for them to consist of ~50-100k nodes.
At the moment, the trees are stored using simple parent_id ...
0
votes
0answers
36 views
Postgres replication delay with inserts into large table
I have a large table (over 35 million rows, most rows contain TOAST'd columns) that I've isolated as the source of growing replication delays in my Postgres instance. When I'm doing inserts into this ...
0
votes
0answers
27 views
cannot have more than 2^32-1 subtransactions in a transaction
I'm trying to run this but after 10 hours I get the error "cannot have more than 2^32-1 subtransactions in a transaction". What is your advice for getting around this issue? I'm hesitant to split up ...
1
vote
1answer
113 views
Index partitioned table to prevent Postgres from doing a sequential scan
I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.
I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
0
votes
1answer
39 views
How to eliminate for loop in a nested aggregate problem
I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be ...
3
votes
1answer
109 views
Fastest way to extract full table in Postgres
I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
0
votes
0answers
9 views
PostgreSQL streaming replication - secondary access
I have read that the streaming replication can be set up so the secondary receives the read only queries, but it is not clear to me how that is "turned on"? With Informix I used a connection manager ...
0
votes
1answer
86 views
Safe to use the same temporary table name in parallel transactons?
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
1
vote
1answer
34 views
How to concatenate arrays with user-defined types in pgsql?
Background
I am using pgsql to build database tables, and have historically defined the tables in static arrays (eg ARRAY[ROW(a,b,...)::user_defined_type,...]). With the upgrade to PostgreSQL 11.2, I’...
