Use this tag to indicate that your question is about PostgreSQL version 14. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-14]
227 questions
1
vote
1 answer
Postgresql gin index miss when the table has three or more fields
the sql
CREATE TABLE user_test1 (
id bigserial PRIMARY KEY,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_user_name_test1 ON user_test1 using gin (name gin_trgm_ops);
explain analyse
select name…
acver
- 13
- 3
1
vote
1 answer
Allow a Postgres user to bypass RLS for all tables?
Is there a way to allow a user to bypass RLS for all tables is Postgres?
spierce7
- 14,797
- 13
- 65
- 106
1
vote
0 answers
Properly using gin index on a JSONB flat object
I'm wondering how to correctly create an index that would index the keys of a JSONB column that has the following shape:
{
"perm1": true,
"perm2": false,
"perm3": true
}
Basically I'd like the lookup of perm1, perm2 and perm3 to be the keys…
pilotguy
- 543
- 1
- 5
- 18
1
vote
2 answers
Low performance of Common Table Expressions in PostgreSQL V14
I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds.
The minimum query to reproduce this issue is the following :
WITH MyTable (id) AS (
SELECT 1
…
Drico
- 1,284
- 15
- 33
1
vote
1 answer
Using VIEW to fill missing data from a MATERIALIZED VIEW for append-only data?
I want to create a MATERIALIZED VIEW called ohlc that calculates the OHLC values of a stock's price. Stock prices are stored in an append-only price table.
The pricing data is updated regularly but ohlc is only refreshed once per hour. This got me…
Gili
- 86,244
- 97
- 390
- 689
1
vote
1 answer
Dynamic query that uses CTE gets "syntax error at end of input"
I have a table that looks like this:
CREATE TABLE label (
hid UUID PRIMARY KEY DEFAULT UUID_GENERATE_V4(),
name TEXT NOT NULL UNIQUE
);
I want to create a function that takes a list of names and inserts multiple rows into the table, ignoring…
David A
- 490
- 4
- 10
1
vote
2 answers
Transfer table ownership
In Postgres, when a table owner, say "xxx" tries to transfer the table ownership to user "yyy", he is getting error
ERROR: must be member of role "yyy"
When I searched further, it can be fixed in two ways.
As a "Super user", we can transfer…
user1102285
- 13
- 2
1
vote
1 answer
reorder fields in custom type inside array while copy from one table to another
This is my minimal example of my problem:
CREATE TYPE "MyTypeOld" AS (
a SMALLINT,
b BIGINT,
c SMALLINT
);
CREATE TYPE "MyType" AS (
a SMALLINT,
c SMALLINT,
b BIGINT
);
--
CREATE TABLE old (
items MyTypeOld[]
);
CREATE TABLE…
S.R
- 2,411
- 1
- 22
- 33
1
vote
1 answer
Insert nulls during bulk insert for Postgres
I am trying to convert this sqlfiddle http://www.sqlfiddle.com/#!3/fbaff/14 from MSSQL Server to Postgres but cannot get the sample data script to insert null values. My end goal is this convert this query to work on the same data and structure,…
sreeli
- 47
- 7
1
vote
0 answers
how to see pg_stat_statements specific to desired schema?
I have multi-tenant postgres DB. I can see details from pg_stat_statements table in the public schema with no issues, but as soon as I switch to a different schema - I get no visibility.
What could be done?
prod=> SET…
DmitrySemenov
- 9,204
- 15
- 76
- 121
1
vote
1 answer
Is it possible to accelerate this postgres / sql (read) query?
I have the following table:
CREATE TABLE mosmix_data (
id SERIAL PRIMARY KEY,
created_at TIMESTAMP without time zone default (now() at time zone 'utc'),
TimeStep timestamp,
name char(8),
description text,
PPPP float8,
E_PPP…
Andreas
- 397
- 4
- 18
- 37
1
vote
0 answers
Optimizing a postgres query with date range
Is there any way to optimize this query in postgres (version 14)? I have an index on entry_date and have tried to index entry_date::date but it did not seem to matter either.
SELECT
user_name,
extract(year from entry_date) as year,
…
Steve Lloyd
- 773
- 2
- 12
- 33
1
vote
2 answers
Delete query from pg_stat_statements in postgresql
I need to use pg_stat_statements, I have read about pg_stat_statements_reset() which can discards all statistics. But is there a function which can discards statisctic of one custom query? Can it be removed automatically?
yuoggy
- 97
- 1
- 10
1
vote
1 answer
Performance regression in PostgreSQL 14.3 compared to PostgreSQL 13.7 on the same query
I migrated a database from Postgresql 13.7 to Postgresql 14.3 recently, and I saw a x2 execution time in a specific case which I can't understand.
The issue has been seen first on Postgres on Linux, and I reproduced the issue using the following…
whisust
- 176
- 3
- 16
1
vote
0 answers
How to create a READ replica for postgreSql database on local machine?
I have a use case where I have keep sync two databases on my local windows machine.
For example DB_primary and DB_secondary both has one table employee.
How to create a read replica to keep both those DBs in sync?
Please note: I am not using any…
Chetan Hirapara
- 644
- 1
- 9
- 29