Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-14]

Use this tag if your question is about PostgreSQL version 14 specific feature or behaviour. Please also add the generic [postgres] tag.

Released on 2021-09-30. See https://www.postgresql.org/docs/release/14.0/ for the new features and enhancements introduced in PostgreSQL 14.

111 questions
19
votes
1 answer

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement: RETURN expression or a block: BEGIN ATOMIC statement; statement; ... statement; END without giving…
ARX
  • 1,509
  • 3
  • 14
  • 15
6
votes
1 answer

How to get slow query on PostgreSQL?

Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below 300ms or above 300ms. I configured below…
Sheikh Wasiu Al Hasib
  • 273
  • 1
  • 2
  • 14
5
votes
1 answer

How to obtain the path to the match of a JSONPath query in PostgreSQL 14?

Here is a minimal working example that illustrates my point, the query finds all the values of keys named "$" at any level in the given JSON: select jsonb_path_query( $$ { "id": "test", "data": [ { "$": "ref1" }, { "$":…
ralien
  • 153
  • 5
4
votes
1 answer

Set PostgreSQL config from command line with ALTER SYSTEM

I need to write a shell/bash script that automatically changes some PostgreSQL configurations. Here's the command that I wrote: sudo -u postgres psql -U postgres -d postgres -c " ALTER SYSTEM SET listen_addresses = '127.0.0.1'; ALTER SYSTEM SET…
collimarco
  • 653
  • 2
  • 9
  • 20
4
votes
1 answer

PostgreSQL FATAL: no pg_hba.conf entry for host "[ipv6]", user "[user]", database "postgres", no encryption

Unable to connect to a postgreSQL database on a LAN. (No problems connecting to the db on the server locally) Error shown in pgAdmin.app: Unable to connect to server: connection to server at "xxx.local" ([ipv6]), port 5432 failed: could not imitiate…
WhatsYourFunction
  • 151
  • 1
  • 1
  • 3
4
votes
1 answer

How do I add a constraint on the UUID type of Postgres to only allow UUID v4?

I have a lot of tables in a Postgres 14 database that use the native UUID type. Is there a database-wide setting/constraint to limit this type to only allow UUID v4? A potential solution I've come across CREATE DOMAIN. I can extend the UUID type,…
Abhijit
  • 181
  • 3
3
votes
1 answer

How to re-TOAST specific data after changing compression?

After changing the default_toast_compression from pglz to lz4 I would like to update existing data to the new, faster, compression. The only way appears to be to dump the entire table and recreate it from scratch, which is not ideal for very large…
OrangeDog
  • 338
  • 1
  • 3
  • 13
3
votes
1 answer

"duplicate key value violates unique constraint" in upsert in Postgres 14

I have a query that I've been successfully running since Postgres added upsert support, I believe that was 9.6: INSERT INTO gitrefresh(projecttag, state, analysis_started, counter_requested, customer_id) VALUES('npm@randombytes', 'Q', NOW(), 1, 0)…
GDR
  • 141
  • 1
  • 6
3
votes
1 answer

What are the usecases for stored procedures (`CREATE PROCEDURE`) as of Postgresql 14?

I've had an impression it was some go-to way of encapsulating complex SQL logic. But digging related responses, such as this one, it doesn't seem to be the case. So are CREATE PROCEDURE-related tweaks across versions just postgresql working towards…
Biller Builder
  • 288
  • 1
  • 12
3
votes
0 answers

How to install pg_amcheck in Ubuntu 20.04 LTS

In 2022-06-16 Postgres released version 14.4, which fixes possible corruption on indexes created with the concurrently option. The release notes suggest using pg_amcheck to check if we have corrupt indexes. However, when running pg_amcheck…
sandre89
  • 133
  • 1
  • 5
3
votes
2 answers

UPDATE FROM with a large table is slow and uses Seq Scans

I have a large table (ultimately maybe a billion rows but currently ~26 million) for which I want to set a flag on the highest PK for a given grouping, in a one-off batch. I chose to create a temporary table that stores the PKs that should be set…
Joe
  • 1,655
  • 2
  • 11
  • 14
3
votes
1 answer

Postgresql 14 Performance regression versus PG13

I migrated a database from Postgresql 13 to Postgresql 14 recently, and I saw a x2 execution time in a specific case which I can't understand. The issue has been seen on a Postgres on Linux, and I reproduced the following example on macOS with…
whisust
  • 31
  • 5
2
votes
1 answer

UNNEST vs ANY()

When selecting a number of records from a Postgres table based on a list of ids passed as a parameter, what are the benefits and drawbacks of SELECT id, FROM tbl INNER JOIN UNNEST($1) AS id USING (id) versus SELECT id, FROM…
Alex O
  • 123
  • 3
2
votes
1 answer

Postgres planning time is very high

I have a table named, and it has enough CPU, memory, and shared buffers. But when I run a simple query: Explain analyse SELECT * FROM assets WHERE leased_to = 'org_name' or owned_by = 'org_name' ; planning takes more than 6 seconds for new session…
sam
  • 21
  • 2
2
votes
0 answers

like_regex vs jsonb_build_object performance

I'm working with Postges 14.8 and using JSONB to store data. The queries that I'm working with perform filtering and JOIN'ing based on stuff in this JSONB column named data. We need to perform JOIN's to get the data we need. The setup is simple. We…
MRzeczkowski
  • 21
  • 3
1
2 3 4 5 6 7 8