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
1
vote
0 answers

.NET Core application starts in docker container before postgres starts in another

I've tried with both ENTRYPOINT and CMD to force my ASP.NET core application to wait for my postgres container to start up the database, but I'm still met with this exception when migration runs pgsql.NpgsqlException (0x80004005): Exception while…
orpheus
  • 99
  • 12
1
vote
1 answer

Postgres weird query plan when the number of records less than "limit"

I have a query that is very fast for large date filter EXPLAIN ANALYZE SELECT "advertisings"."id", "advertisings"."page_id", "advertisings"."page_name", "advertisings"."created_at", "posts"."image_url", …
heyxh
  • 571
  • 7
  • 11
1
vote
1 answer

Is there any way to write this code avoiding intermediate steps/views in PostgreSQL coming from different tables?

I am working in a large query I would like to eliminate intermediate steps, so I am trying to write the two queries below in just one. The first query (QUERY 1) select the grid id from a table called tiles, from here i obtained an UUID who…
gcj
  • 280
  • 2
  • 12
1
vote
2 answers

Matching a small table (<1,000 rows) to a large table (>100m rows) using pg_trgm—most efficient method?

This is a problem that comes up often in my work with various different data sets, so please excuse me presenting it in general terms rather than using a specific example. I often need to get records from a large table (generally 100s of millions of…
Louis Goddard
  • 45
  • 1
  • 5
1
vote
0 answers

How to view if a table has been read recently in postgres 12

How can I manage to view if a table has been read recently in postgresql 12 ? I will need something like "select lastdateread from stat_table"
Gabriele D'Onufrio
  • 405
  • 1
  • 5
  • 17
1
vote
0 answers

Get user privileges per table

When I run query: SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = (select user) This shows only those tables (with all privileges), which I (current user) created on this…
oh no
  • 49
  • 1
  • 9
1
vote
2 answers

PostgreSQL table --data-only dump taking more than double the size of table

I have a Postgresql DB with a total size of 1.7TB. Out of which 1.6TB is occupied by one table. The total disk space was 1.7TB so I am now out of Disk space. I cannot run Vacuum on it, I tried truncating few tables and then used the available space…
Fahad Nisar
  • 1,723
  • 12
  • 18
1
vote
0 answers

Is it possible to have a complete parallel plan for a query which includes window functions using order clauses (e.g. cumulative sum with order by)?

So, I have a query which starts off with joining one large table (5Mio records) with several others. This part of the query parallellises perfectly by specifying this first: set max_parallel_workers_per_gather = 12; set parallel_setup_cost =…
Mathias Versichele
  • 337
  • 1
  • 11
1
vote
1 answer

CREATE TABLE LIKE with different primary key for partitioning

I've got an existing table of dogs which I would like to partition by list using the colour column: CREATE TABLE dogs ( id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY, colour text, name text ) ; Because it's not possible to partition an…
LondonRob
  • 73,083
  • 37
  • 144
  • 201
1
vote
1 answer

Speed Up Query with Multiple Inner and Outer Joins

I'm having trouble with a slow Postgresql query. I've gone through the standard postgresql.conf changes and verified the referenced columns are indexed. Other than that, I'm not sure what the next step would be. The query below takes just under 3…
billflu
  • 45
  • 4
1
vote
1 answer

How to update another table based on foreign table

I have two databases "Sales" and "Service" then have created a foreign Data wrapper on service for two tables of "Sales" DB ( accounts and orders) and created two foreign tables on service named accounts_sales and orders_sales. Now on the…
Rahul Gour
  • 487
  • 2
  • 7
  • 21
1
vote
1 answer

Error Installing PostgreSQL - Copying compressed file NodeIndexOnlyScan.sh

When I try to install postgresql I receive this error message: I thought there was an error with installer, so I tried a PostgreSQL 12 installer, but same error in a different location: Even, I downloaded a new installer but nothing seems to work.…
Diego
  • 151
  • 1
  • 6
1
vote
1 answer

In PostgreSQL 12, Does creating partitioning via inheritance improve query performance if queries are contained with a child table?

Using PostgreSQL 12, I'd like to take advantage of partitioning to 1: Aid in query performance, 2: Allow removing historic data more easily to keep mitigate database growth. Unfortunately, declarative partitioning requires the key to be part of the…
SteveJ
  • 3,034
  • 2
  • 27
  • 47
1
vote
1 answer

Connect to Azure PostgreSQL using Azure Data Studio with SSL enabled

When i try to connect to Azure PostgreSQL with SSL enabled using Azure Data Studio , with SSL Mode=Verfiy-full, i get below error root certificate file "C:\Users\mstechie\AppData\Roaming/postgresql/root.crt" does not exist Either provide the file or…
MSTechnie
  • 175
  • 10
1
vote
1 answer

PostgreSQL 12.4 query planner ignores sub-partition constraint, resulting in table scan

I have a table T (A int, B int, C long, D varchar) partitioned by each A and sub-partitioned by each B (i.e. list partitions with a single value each). A has cardinality of <10 and B has cardinality of <100. T has about 6 billion rows. When I run…
Niels Voorhoeve
  • 13
  • 2
1 2 3
…
36 37