Stack Overflow
Stack Overflow

Questions tagged [postgresql-12]

For PostgreSQL questions specific to version 12.

PostgreSQL 12 is a major release of the PostgreSQL RDBMS.

Improvements include, but are not limited to:

  • performance improvements for partitioning and indexes
  • CTEs (WITH queries) are by default inlined for better query performance
  • JSON path queries per SQL/JSON specification
  • support for case- and accent-insensitive ICU collations
  • (stored) generated columns
  • extended statistics for “most common values”
  • page checksums can be enabled and disabled without initdb
  • authentication: support for encrypted GSSAPI authentication and LDAP server discovery
  • “pluggable storage” to add different table storage methods

More information regarding the release is available here.

554 questions
3
votes
0 answers

template database "template1" has a collation version, but no actual collation version could be determined

I just upgraded my Postgres from 12 to 15 and I keep getting this error: template database "template1" has a collation version, but no actual collation version could be determined Here's the template1 collation info in Postgres 15: Any ideas on how…
buddy2891
  • 157
  • 10
3
votes
0 answers

How do I reset database access privileges to initial state?

I noticed it on our PostgreSQL server (v12.7). There are two similar DBs with weirdly different access permissions: postgres=> \l+ List of databases Name | Owner…
satorg
  • 850
  • 6
  • 8
3
votes
1 answer

Why does QGIS creates multiple connections for PostgreSQL Server?

We have a PostgreSQL database with PostGIS running and today we ran into the issue that too less connections were available. Mostly we are using QGIS to access the database. We realized that issue because multiple users got the following…
JBecker
  • 124
  • 7
3
votes
2 answers

AWS RDS Postgres: WAL_LEVEL not set to 'logical' after changing rds.logical_replication = 1

I am in the process of setting up Debezium to work with AWS Aurora Postgres (postgres version 12.6). For Debezium to work, the WAL (Write-ahead-logging) must be set to 'logical' and not 'replica'. On AWS, this would require a DBA to set the…
rm12345
  • 1,089
  • 3
  • 18
  • 32
3
votes
1 answer

Why does a user receive a "permission denied" error when trying to update their user password in PostgreSQL?

I have several users of our PostgreSQL (12) database who are not superusers. From the docs, it would appear that any "ordinary" user should be able to set their password to something new, via: ALTER ROLE [user] WITH ENCRYPTED PASSWORD '[new…
M. Andersen
  • 107
  • 8
3
votes
2 answers

Postgresql ORDER BY not working as expected

Let's try this simple example to represent the problem I'm facing. Assume this table: CREATE TABLE testing1 ( id serial NOT NULL, word text, CONSTRAINT testing1_pkey PRIMARY KEY (id) ); and that data: insert into testing1 (word) values…
babis21
  • 1,515
  • 1
  • 16
  • 29
3
votes
1 answer

postgresql 12 jsonb_path_query how to select some keys to construct a small object from a big jsonb object?

i have a data JSONB column in Postgres 12, which saved a big object with a .interactions field which is an array of big objects, each object is pretty big, having too many fields which are not needed by app layer, wonder is there a way to query out…
TomasJ
  • 487
  • 1
  • 4
  • 12
3
votes
0 answers

postgres corrupted shared memory

i am trying to use postgresql to process queries on GPU using PG_strom. whenever i try to process queries i got issue: postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited…
Mohamed Ali
  • 103
  • 1
  • 2
  • 9
3
votes
2 answers

Query runs faster with EXPLAIN ANALYZE using parallel plan. Slows down and loses parallel workers without EXPLAIN ANALYZE. Postgres with DBeaver JDBC

I'm really puzzled by the following query: explain analyze (with z as ( select 1 as attraction ,1 as nd_attraction , n.fc from (select pe_id,zo_id,co_id,value_num as dt from rtpezocoav x --use view?? where…
Mathias Versichele
  • 337
  • 1
  • 11
3
votes
1 answer

Terminate all open connections using single CLI command on PostgreSQL 12

I have a CLI command like this: $ psql -U postgres < <(psql -U postgres -Atc "select 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = \''||datname||'\'; /* I WILL ADD MORE QUERIES HERE LATER. */ 'from pg_database…
Dennis
  • 1,805
  • 3
  • 22
  • 41
3
votes
1 answer

PostgreSQL 12.3: ERROR: out of memory for query result

I have an AWS RDS PostgreSQL 12.3 (t3.small, 2CPU 2GB RAM). I have this table: CREATE TABLE public.phones_infos ( phone_id integer NOT NULL DEFAULT nextval('phones_infos_phone_id_seq'::regclass), phone character varying(50) COLLATE…
IndiaSke
  • 348
  • 1
  • 2
  • 10
3
votes
0 answers

How do I install PostGIS on Ubuntu Desktop 20.04 LTS

I have just set up a brand new Ubuntu 20.04 LTS I have successfully installed Postgresql 12 I want to install postgis, but I cannot find any instructions on the internet for my version of Ubuntu. My aim is to set up a DHIS2 server to run a hospital…
ElectronicsNewbie
  • 141
  • 5
3
votes
1 answer

Inconsistencies Casting to Double-Quoted Types

Using postgres 12.2: select '46ee2794-ddd1-4c4b-be04-82908ff1885d'::"uuid" /*works, uuid is a built-in type, not an alias*/ select '1'::"int4" /*works ... int4 is alias for integer*/ select '1'::"integer" /*fails ... integer is built-in type*/ I…
Avocado
  • 871
  • 6
  • 23
3
votes
2 answers

To what degree does PostgreSQL support parallel DDL?

Looking here, it is clear that Oracle supports execution of DDL commands in parallel with scenarios clearly listed. I was wondering whether Postgres does indeed offer such functionality? I can find a lot of material on "parallel queries" for…
Zeruno
  • 1,391
  • 2
  • 20
  • 39
3
votes
1 answer

Failed to connect to PostgreSQLContainer: java.io.EOFException

Creating my container as: public static PostgreSQLContainer container = new PostgreSQLContainer<>("postgres:latest"); static { container.start(); System.setProperty("driver-class-name", container.getDriverClassName()); …
Tyulpan Tyulpan
  • 724
  • 1
  • 7
  • 17
1 2
3
…
36 37