Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql-11]

PostgreSQL version 11

5
votes
1answer
67 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
109 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 ...
3
votes
2answers
175 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
952 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 ...
2
votes
2answers
211 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
145 views

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

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
2
votes
1answer
28 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
1answer
100 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
1answer
103 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
56 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
447 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
39 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
416 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
16 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
32 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
30 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
51 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
24 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
5answers
42 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
2answers
40 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
12 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. ...
0
votes
0answers
23 views

Adding LIMIT slows down query

SQL CREATE TABLE actions ( action_id bigint GENERATED ALWAYS AS IDENTITY, block_time timestamptz NOT NULL DEFAULT now(), block_num int NOT NULL, trx_id text NOT NULL, ...
0
votes
0answers
9 views

Postgresql 11 systemd file changed reload to kill; why?

I'm upgrading from Postgres 9.3 to 11, and I do not understand why the ExecReload default command went from ExecReload=/usr/pgsql-9.3/bin/pg_ctl reload -D ${PGDATA} -s to ExecReload=/bin/kill -...
0
votes
0answers
24 views

Sync Production Postgres Database to Laptop - One Way

I am running PostgreSQL 11 on my Windows Laptop and would like to sync with our production database on a (semi) regular basis and then test a number of queries and view creation without running the ...
0
votes
0answers
28 views

postgresql 11 & windows server 2008

I plan to upgrade a PostgreSQL/PostGIS server from 9.6 to 11. The server is running on windows server 2008 R2 64. According to the doc, PostgreSQL 11 and Windows Server 2008 has not be tested. So ...
0
votes
1answer
31 views

How to know or recover my pg_dump password? [closed]

I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password. But I have no idea what it is. Indeed, it is different from my admin account ...
0
votes
1answer
307 views

Installation of PostgreSQL-11: Depends: libllvm6.0 (>= 1:6.0~svn298832-1~) but it is not installable

I've added the official repository to my sources.list, deb http://apt.postgresql.org/pub/repos/apt/ cosmic-pgdg main 11 When I try to install PostgreSQL-11 on Ubuntu Cosmic (18.10), I get the ...
0
votes
0answers
872 views

What's the difference between stored procedure and function in PostgreSQL 11? [duplicate]

In PostgreSQL 11 beta1 announcement, it is stated that a major feature of the new version is SQL Stored Procedures PostgreSQL 11 introduces SQL stored procedures that allow users to use ...
-2
votes
1answer
37 views

What is TRANSFORM FOR TYPE?

PostgreSQL 11 introduces TRANSFORM FOR TYPE, from these docs by HP, PostgreSQL 11 New_Features postgres=# CREATE EXTENSION jsonb_plperl CASCADE ; NOTICE: installing required extension "plperl" CREATE ...