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
48
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
  • 541
  • 1
  • 4
  • 6
20
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
  • 852
  • 10
  • 30
18
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
  • 481
  • 6
  • 14
17
votes
4 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
  • 868
  • 1
  • 6
  • 20
14
votes
3 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
  • 320
  • 2
  • 12
12
votes
1 answer

Error: connect ECONNREFUSED 127.0.0.1:5432 when connecting with nodejs program

I have a postgresql server set up on a CentOS 8 machine, and a js program running on the same machine, using the pg library. const { Pool } = require('pg') const pool = new Pool({ user: process.env.PG_USER, //postgres user host:…
K. Sutherland
  • 196
  • 1
  • 1
  • 9
10
votes
3 answers

PostgreSQL: Underperforming query on large table with composite key

We have a table of 180m rows, 20 GB in size. Table DDL is: create table app.table ( a_id integer not null, b_id integer not null, c_id integer not null, d_id integer not null, e_id integer not null, …
Robert Hargreaves
  • 152
  • 11
9
votes
3 answers

Get all sequences with current values

I have the following query that gets all sequences and their schemas: SELECT sequence_schema as schema, sequence_name as sequence FROM information_schema.sequences WHERE sequence_schema NOT IN ('topology', 'tiger') ORDER BY 1, 2 I would like to get…
GammaGames
  • 1,617
  • 1
  • 17
  • 32
9
votes
2 answers

Creating an in-memory table in PostgreSQL?

My understanding of an in-memory table is a table that will be created in memory and would resort to disk as little as possible, if at all. I am assuming that I have enough RAM to fit the table there, or at least most of it. I do not want to use an…
Zeruno
  • 1,391
  • 2
  • 20
  • 39
8
votes
1 answer

Postgres long-running transaction holding lock on parent partitioned table

TL;DR: we have long-running imports which seem to hold locks on the parent partitioned table even though nothing is directly referencing the parent table. Background In our system, we have inventories and inventory_items. Inventories tend to have…
Alexander Wallace Matchneer
  • 3,289
  • 1
  • 23
  • 26
6
votes
2 answers

How to list all indexes of a table with their corresponding size in PostgreSQL?

I can view the total size of all indexes in a table with SELECT pg_size_pretty (pg_indexes_size('table_name')); and the size of a specific index with: select pg_size_pretty(pg_relation_size('index_name'));, but I would like to retrieve a list with…
Z. M.
  • 329
  • 5
  • 13
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
  • 73,083
  • 37
  • 144
  • 201
6
votes
4 answers

Install older package version in Alpine

So recently (5th September) the Alpine Linux package repo was updated to postgresql-client 12.4 I'm referencing version 12.3 in my Dockerfile (apk add postgresql-client=~12.3). Is it not possible to install that version now? I'd like to update on…
Steve Folly
  • 8,327
  • 9
  • 52
  • 63
6
votes
2 answers

Does anyone have a default PostgreSQL pg_hba.conf File?

I've messed my pg_hba.conf file up, and I don't want to reinstall - I can't find a default one anywhere on the web. Does someone have one?
fcpihtr
  • 125
  • 1
  • 4
  • 11
6
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,248
  • 4
  • 22
  • 55
1
2 3
…
36 37