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
5
votes
2 answers

Are hash indexes the most optimal for exact text match queries as of Postgres 13?

Example (fake) case: indexing a column containing e-mails (text type) when the column will exclusively be queried for an exact string match, ex. SELECT * FROM mytable WHERE email = 'test@test.com' Do hash indexes provide advantages over B-TREEs…
Z. M.
  • 329
  • 5
  • 13
5
votes
0 answers

How to start pg_cron extension after shutdown

Had to terminate a process run by pg_cron. However, after doing that pg_cron extension stopped. Below is the log from the server: 2021-04-29 12:55:40.712 UTC [535529] LOG: pg_cron scheduler shutting down Now, no jobs are running. How to restart…
Nishant Soni
  • 61
  • 6
5
votes
0 answers

brew postgresql-upgrade-database fails on migrating and upgrading data

I've just sit down to my work, forgetting that I've upgraded brew in the 2020 – so of course I have a postgresql problem. The last version I had was 12.x. Now I have 13.1 installed: $ brew info postgres Warning:…
mdmb
  • 4,833
  • 7
  • 42
  • 90
5
votes
4 answers

Mikro-orm error: password authentication failed for user "postgres"

I'm trying to code along this React GraphQL TypeScript tutorial The project uses MikroOrm to communicate with a PostgreSQL database. I have PostgreSQL(12.4) installed on my Ubuntu 18.04, created a "postgres" user and I can log in to the user and run…
Laaden
  • 53
  • 1
  • 4
5
votes
1 answer

Postgresql 12 - Collation not working Windows / Linux

I've installed Postgresql 12 on both Wnindows and Linux CentOS 8. For my project, I needed to create my own ICU Collation and apply it to all character columns (either in column creation or order by requests = preferred). Before doing so, I tried to…
Arnaud Perrier
  • 81
  • 4
5
votes
6 answers

Knex: Timeout acquiring a connection

Since today, I get the following error when I try to locally connect to a postgres database (v 12) using knex.js. Unhandled rejection TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx)…
Flavio
  • 1,507
  • 5
  • 17
  • 30
5
votes
1 answer

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

EDIT: I have moved this question over to dba.stackexchange as I have received advice that that community can better address my question. I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which…
Zeruno
  • 1,391
  • 2
  • 20
  • 39
4
votes
1 answer

Circular delete cascade in Postgres

(For background I'm using Postgres 12.4) I'm unclear why deletes work when there are circular FKs between two tables and both FKs are set to ON DELETE CASCADE. CREATE TABLE a (id bigint PRIMARY KEY); CREATE TABLE b (id bigint PRIMARY KEY, aid bigint…
SDRay
  • 61
  • 1
  • 3
4
votes
1 answer

How to set PostgreSQL script parameters in DBeaver or PgAdmin?

PostgreSQL v12.6 DBEaver 7.1.0 How to set SQL script parameters in DBeaver or PgAdmin? (any working option is welcome) This script works perfectly when I run it as a script on DB initialization (i.e. from Docker's postgres:12.6-alpine in…
diziaq
  • 6,881
  • 16
  • 54
  • 96
4
votes
1 answer

Speed up autovacuum in Postgres

I have a question regarding Postgres autovacuum / vacuum settings. I have a table with 4.5 billion rows and there was a period of time with a lot of updates resulting in ~ 1.5 billion dead tuples. At this point autovacuum was taking a long time…
Rio
  • 107
  • 1
  • 9
4
votes
2 answers

Fetch all tables in a particuler Postgres database using node?

I need to fetch all tables in a particular Postgres database using node. But not finding any way to achieve that. Is there any way to get that? For example, suppose I have a database named 'TestDatabase' it contains 4 tables( just assume it can have…
Anuresh Verma
  • 818
  • 1
  • 13
  • 30
4
votes
1 answer

How to configure pg_config/pgxs/make to pick up CPPFLAGS and CFLAGS in the Makefile to build Postgres C/C++ extensions?

I would like to add compiler flags to build my Postgres C/C++ extension. I have tried standard Makefile practices but pg_config does not pick up any of the compiler flags I add. The Makefile is as follows: 1 # the extensions name 2 EXTENSION …
ReverseFlowControl
  • 766
  • 6
  • 30
4
votes
3 answers

PostgreSQL: more than one row returned by a subquery used as an expression

I have a main.comments table where I store users comments. I'm trying to add a comment to the database and get some data as a return. Here's my query INSERT INTO main.comments (text, post_id, user_id) VALUES('sample', 11, 1) RETURNING…
aingthed
  • 103
  • 2
  • 5
4
votes
1 answer

PostgreSQL json path expression to find first array element having a specific key

I need a jsonpath expression, which returns the first element of an array which has "key" property. I'm looking for the same result as this query: SELECT j FROM jsonb_array_elements( '[ {"key": "foo"}, {"other": "bar"}, …
nextstopsun
  • 443
  • 7
  • 14
4
votes
2 answers

PostgreSQL: Delete key/value pair from array with json objects

I have a table: CREATE TABLE movies( id text, data jsonb ); INSERT INTO movies(id, data) VALUES ( '1', { "actors": [ { "name": "actor1", "email": "actor1@somemail.com" }, { …
Eltjon Sulaj
  • 53
  • 6
1
2
3
…
36 37