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.

409 questions
25
votes
1 answer

Postgresql query for objects in nested JSONB field

I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like: No integer, Content jsonb Say I put many records like: "No": 2, {"obj":"x","Item": {"Name": "BigDog", "Model": "NamedHusky", "Spec":"red dog"}} "No":…
ifdog
  • 291
  • 1
  • 3
  • 6
11
votes
3 answers

Postgresql partition and sqlalchemy

SQLAlchemy doc explain how to create a partitioned table. But it does not explains how to create partitions. So if I have this : #Skipping create_engine and metadata Base = declarative_base() class Measure(Base): __tablename__ = 'measures' …
Rémi Desgrange
  • 709
  • 4
  • 18
8
votes
2 answers

PostgreSQL 12 and PostGIS 3.0.1: ERROR: could not access file "$libdir/postgis-3": No such file or directory on Linux

I have Linux Mint 19.2 where I installed the newest PostgreSQL 12 using: sudo apt-get install postgresql Similarly, I installed the newest Postgis 3.0.1 using: sudo apt-get install postgis I setup postgres account and everything and wanted to create…
janchytry
  • 151
  • 1
  • 9
6
votes
1 answer

Don't round float input silently when inserting into integer column

I have a table like: CREATE TABLE foo(bar int) I have a script which inserts values into that table: INSERT INTO foo(bar) VALUES (1), (2), (3.2) The float value is silently rounded to fit the data type: > SELECT * FROM foo; bar ----- 1 2 …
LondonRob
  • 53,478
  • 30
  • 110
  • 152
5
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
  • 482
  • 3
  • 14
5
votes
0 answers

PostgreSQL on container throws an error chmod: /var/lib/postgresql/data: Operation not permitted

I have tried to run Postgres 12 on the docker and the following files I have created. I do not understand where do I made a mistake and what is an issue of PostgreSQL file permission. Dockerfile: FROM postgres:12.0-alpine USER root RUN chmod 0775…
Dipak
  • 2,086
  • 4
  • 17
  • 48
5
votes
1 answer

PostgreSQL Calculated Column with values of another table referenced by foreign key

I'm currently working on a simple dummy project to refresh my knowledge on SQL and to learn a few new things :) I have a table Article with the columns: aID, price I have another table Storage: sID, aID, count The Storage table references…
KilledByCheese
  • 570
  • 5
  • 23
4
votes
3 answers

Copying postgresql local to remote database (both with password) - ERROR: option "locale" not recognized

Working with Postgres 12 / Windows 10. Trying to copy a remote database to localhost with the following command: pg_dump -C -h remotehost -p 5432 -U postgres remotedb | psql -h localhost -p 5432 -U postgres localdb CMD requests for password…
rodolfo_r
  • 170
  • 8
4
votes
3 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
  • 43
  • 4
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
  • 413
  • 6
  • 12
3
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
  • 87
  • 1
  • 7
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,335
  • 1
  • 12
  • 26
3
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
  • 51
  • 3
3
votes
5 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,279
  • 3
  • 14
  • 28
3
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
  • 986
  • 12
  • 27
1
2 3
…
27 28