Questions tagged [postgresql-11]
PostgreSQL version 11
2
votes
2answers
73 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 ...
0
votes
1answer
16 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 ...
2
votes
2answers
100 views
Containment check tstzrange @> timestamptz not using btree or gist index
Schema:
Column | Type
----------------------+--------------------------
id | integer
event_id | integer ...
3
votes
2answers
138 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 ...
4
votes
1answer
54 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
...
2
votes
1answer
86 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 ...
5
votes
1answer
49 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
...
1
vote
1answer
44 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 ...
3
votes
0answers
476 views
Not able to connect to Postgres database : no pg_hba.conf entry for host [closed]
I am trying to connect a Postgres database which resides in a docker installed on Ubuntu virtual machine. Following exception is being thrown on the console.
org.postgresql.util.PSQLException: FATAL: ...
1
vote
0answers
20 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, ...
1
vote
2answers
302 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": /...
0
votes
1answer
149 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 ...
1
vote
1answer
272 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') - ...
3
votes
1answer
516 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
1answer
36 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 ...
0
votes
0answers
495 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
...
