Questions tagged [postgresql-11]

PostgreSQL version 11

Filter by
Sorted by
Tagged with
9
votes
2answers
440 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 ...
5
votes
1answer
126 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
4
votes
1answer
272 views

PostgreSQL How to DEFAULT Partitioned Identity Column?

PostgreSQL 11 What is the best way to generate default values for identity columns on partition tables. E.g CREATE TABLE data.log ( id BIGINT GENERATED ALWAYS AS IDENTITY ...
4
votes
1answer
63 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 (...
3
votes
1answer
150 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 ...
3
votes
2answers
382 views

How does the recovery strategy of PostgreSQL exactly work?

I'm testing and trying to figure out how the backup and recovery strategy in PostgreSQL 11 works but it does not work as expected. When I recover the backup, I don't get the correct state. I get any ...
3
votes
1answer
2k views

“column … does not exist” error when calling an SQL procedure (new in PostgreSQL 11)

Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema: DROP TABLE IF EXISTS config.trading_holiday; CREATE TABLE config.trading_holiday ( id smallint ...
3
votes
1answer
104 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 ...
3
votes
2answers
48 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: ...
2
votes
2answers
816 views

Get the max value from a slice of a JSON Array?

I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw. The limits Start & End of the slice are ...
2
votes
2answers
285 views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
2
votes
1answer
30 views

Is it possible to have unique together constraint on a SET of columns in PostgreSQL?

What I want to achieve is the following: CREATE TABLE foo ( bar1 int, bar2 int, type varchar(16), FOREIGN KEY bar1 REFERENCES bar(id), FOREIGN KEY bar2 REFERENCES bar(id) ); ...
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 ...
2
votes
1answer
132 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 ...
1
vote
2answers
54 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 ...
1
vote
2answers
215 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 ...
1
vote
1answer
191 views

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
1
vote
1answer
29 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 ...
1
vote
1answer
87 views

JSONB vs BRIN Index

The requirements go like this: Each user has a wallet that can contain tens to a hundred or so different currencies, and for each currency an amount Number of currency can grow > 1600 (make me ...
1
vote
1answer
925 views

Converting VARCHAR to TIMESTAMP with milleseconds yields “ERROR: date/time field value out of range”

I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command: select last_run, to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - ...
1
vote
1answer
18 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", ...
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 | ...
1
vote
1answer
35 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
104 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 ...
1
vote
1answer
204 views

Postgres: ERROR: operator does not exist: point <@> point

Using PostgreSQL 11 on Windows. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. ...
1
vote
2answers
598 views

Undefined symbol when migrating to Postgres 11 [closed]

After upgrading Postgres 10 to version 11, by using pg_upgrade, I get this error: could not load library "$libdir/postgis-2.4": ERROR: could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /...
1
vote
1answer
45 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 ...
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 ...
1
vote
0answers
50 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 ...
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 (...
1
vote
1answer
94 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 ...
1
vote
1answer
32 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’...
1
vote
1answer
149 views

Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old ...
1
vote
0answers
31 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
1answer
75 views

Efficient group by on sorted CSV using PostgreSQL FDW

I have a large CSV file sorted on the first column. Here is an extremely simplified version): a 7 a 4 b 6 c 3 c 9 c 2 I want to group by the first column and sum the second. How do I tell the ...
1
vote
0answers
28 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, ...
0
votes
1answer
22 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
5answers
199 views

Create Role or Create user

PostgreSQL version : 11.2 OS platform : RHEL 7.6 I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database. The Database name will be : crm_db The ...
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
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
26 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
29 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 ...
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
35 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. ...
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/...
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 ...
0
votes
2answers
167 views

Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux: "C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --...
0
votes
1answer
41 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 ...
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 ...