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

Improving performance of Postgres jsonb queries combined with relational queries

I have a single SELECT that queries both regular postgres tables and a jsonb column. When I SELECT the entire jsonb column, the query is fast (574 ms). However when I instead select a top-level path of the same jsonb column, the query slows down by…
simj
  • 183
  • 2
  • 10
0
votes
1 answer

Postgres EXPLAIN ANALYSE Planning Time slow for first query per connection

When running the query the first time in psql, it is a bit slow. The second time it's a lot faster since the Planning Time goes down substantially. > EXPLAIN ANALYSE SELECT * FROM public.my_custom_function(10, 10, 'Personal'); The first time: …
Werner Raath
  • 1,322
  • 3
  • 16
  • 34
0
votes
1 answer

Postgres "On conflict do nothing' still inserts new record

So I have the following PostgreSQL query: INSERT INTO "Teams" ("name","createdAt","updatedAt") VALUES ('SOMETHING','2021-01-19 12:33:20.323 +00:00','2021-01-19 12:33:20.323 +00:00') ON CONFLICT DO NOTHING RETURNING *; The table is as…
Kiwi
  • 2,713
  • 7
  • 44
  • 82
0
votes
0 answers

Even query used Index scan, It takes 40 seconds to get 1000 records

My table create script is below, My table is partitioned by month wise based on saletime. Average one day contains 3 Million records. Totally My table contains 1 Billion Records CREATE TABLE MyTable( Id bigint not null, Col1 text, Col2 text, Col3…
John
  • 25
  • 8
0
votes
1 answer

How do I "copy" enum values from one enum column to another enum column?

I'm trying to copy the enum values between two columns in a table. The the two enum types have the same enum values: UPDATE dogs SET breed = breed_old; ... ERROR: column "breed" is of type "breed" but expression is of type "breed_old" I've also…
tom_nb_ny
  • 110
  • 10
0
votes
0 answers

How to speed up select query in the Partitioned table?

I am using PostgreSQL 12. My Table contains 1 Billion Records. It was partitioned by every Month based on date range. Every day contains more than 3 Million Records. When I select some set of ids, it takes more time. I want to filter 20 days but it…
John
  • 25
  • 8
0
votes
2 answers

Postgres window functions: applying an aggregate function on a window frame's records that satisfy a condition based on the current row

This question is about SQL syntax for Postgres 12. Let's assume a stock_prices table with the following columns: ticker, date, price. I'm interested in performing calculation on the previous 10-day window, such as: SELECT ticker, date, …
AmitA
  • 3,239
  • 1
  • 22
  • 31
0
votes
2 answers

PostgreSQL: Select from table only if table exists

I am an SQL noob, and I have been trying to select data only if a table exists, and nothing if it doesn't. I've looked at various solutions, and this is the best I've been able to come up with: DO $$ BEGIN IF EXISTS …
nabelekt
  • 99
  • 3
  • 12
0
votes
1 answer

Postgres 12.4 gives function does not exists error

I am running the below query in postgres 12.4 : SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_size(table_name)) as table_size, pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size, …
darecoder
  • 1,478
  • 2
  • 14
  • 29
0
votes
1 answer

PostgreSQL FDW over pgBouncer drops connection to PostgreSQL

we have a PostgreSQL setup where we directed FDW (Forward Data Wrapper) connection to other databases over pgBouncer. Once we execute the query to FDW schema, connection from FDW to pgBouncer to another PostgreSQL server is opened correctly but once…
khorvat
  • 1,630
  • 2
  • 20
  • 31
0
votes
1 answer

Find the date range between the given year and month

I have the following table with date range: create table stf ( d1 date, d2 date ); insert into stf…
MAK
  • 6,824
  • 25
  • 74
  • 131
0
votes
0 answers

What will be the value for wal_recycle while using streaming replication in PostgreSQL 12?

wal_recycle is new setting in PostgreSQL 12. By default it is ON. What will be the value for wal_recycle while using streaming replication in PostgreSQL 12 ?
Darshan Shah
  • 157
  • 1
  • 1
  • 15
0
votes
1 answer

PostgreSQL: FATAL: password authentication failed for user "postgres"

I have a ThingsBoard Professional Edition setup using AWS EC2 instance. The database is PostgreSQL-12. I tend to get the following error: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user…
ta.ng
  • 55
  • 1
  • 8
0
votes
1 answer

syntax error at or near "VARCHAR" LINE 1: CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000),

please help me to solve this error following is my code and after below my code error also written CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000), adding INT, entry_date TIMESTAMP(0)) RETURN TIMESTAMP(0) as $body$ declare …
sanjeev kumar
  • 3
  • 2
0
votes
1 answer

Displaying data from an external relationship in PostgreSQL

I have a database that I'm having trouble filtering using values from a table that does not have a direct relationship with my main table. My database is roughly as follows table_main +----+------------+--------+-------+-------+ | id | date |…
evgeniyp92
  • 3
  • 1
1 2 3
…
36 37