Questions tagged [postgresql-14]
The postgresql-14 tag has no usage guidance.
28
questions
-3
votes
0
answers
10
views
How to create package in postgresql
`CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER DEFAULT 10) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE ...
1
vote
0
answers
95
views
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 ...
- 113
2
votes
2
answers
67
views
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 ...
- 1,427
-2
votes
1
answer
70
views
Distribute values
I would like to distribute for each unique value in col1 an unique value of col2 in an ascendant order.
DATA
This will be always n^n, where n is the number of values assignable to an unique value of ...
- 3
0
votes
0
answers
23
views
How to handle tables with lots of deletes and updates in PostgreSQL
I have tables in PostgreSQL 14 database that suffer a lot of deletes and updates every minute, in the following cases:
A table with many rows that every minute is updated in a specific column;
A ...
- 295
0
votes
0
answers
17
views
upgrading postgres 9.2 cluster
I am looking to upgrade a running CentOS based postgres 9.2 cluster to version 14. One database is relatively small with ~400k records, however the second is quite a bit larger and holds several ...
- 13
2
votes
0
answers
72
views
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 ...
- 21
0
votes
0
answers
53
views
Foreign Key to Partitioned Table
I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country.
CREATE TABLE transaction (...
- 113
1
vote
1
answer
37
views
How to construct a jsonb containing a number?
Is there a way to construct a jsonb with only a numeric value in it?
For example,
SELECT pg_typeof(('{"a":1}'::jsonb) -> 'a');
indicates that ('{"a":1}'::jsonb) -> 'a' has ...
- 2,824
0
votes
1
answer
64
views
Table with smaller data types seems to take more space on disk?
I have these two identical tables:
Table "public.region"
Column | Type | Collation | Nullable | Default
-------------+----------+-----------+----------+---------
...
- 95
0
votes
1
answer
20
views
pg_class returns 0 as relpages
I have this table:
Table "public.region"
Column | Type | Collation | Nullable | Default
-------------+----------+-----------+----------+---------
r_regionkey | ...
- 95
1
vote
1
answer
39
views
Why is the optimizer not using the clustering index on my table?
I have this table
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+----...
- 95
0
votes
1
answer
31
views
PostgreSQL function call error: structure of query does not match function result type
i have function
CREATE FUNCTION create_post(
txt text,
created BIGINT,
photourl text,
user_id bigint
) RETURNS TABLE(
id bigint,
"text" text,
created_at timestamp,...
1
vote
1
answer
39
views
Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?
I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an
I have this timestamp with timezone column ...
- 111
0
votes
0
answers
63
views
Unique query to multiple tables with array. Is it possible?
I'm using PostgreSQL 14.
Today, I have the following tables, with more than 4 million lines each table:
tbx_00, tbx_01, tbx_02, tbx_03, tbx_04
and
tby_00, tby_01, tby_02, tby_03, tby_04
About the ...
- 1
0
votes
0
answers
37
views
Migrate PostgreSQL between different releases (9.4 to 14)
I have a Debian 8 with Postgresql 9.4 server. I would like to upgrade it to the last version (Debian 11 with Postgresql 14). What would be the best practice? I was thinking of creating a new server ...
0
votes
0
answers
19
views
Postgres execution times increase exponentially with data volume
I have a table that increases by several million record per day. Initially it's partitioned by day, but a couple of days after creation
each daily partition is merged into a monthly partition.
The ...
- 841
1
vote
1
answer
74
views
Postgres psql command intermittently 'stalls' in powershell
I'm in the process of moving a postgres database to a new server.
I have number of large tables, partitioned by month, each contains several 10's of millions of rows.
I have a powershell script to do ...
- 841
0
votes
0
answers
18
views
How do I change to the new server version?
I have versions 13 and 14 of postgres installed on my system. When I start the server using systemctl start postgresql it always starts version 13.
The client application shows version 14.
# psql -V
...
- 181
0
votes
1
answer
56
views
Which Index Works Better with pg_trgm Extension?
I'm currently building indexing for pattern matching in PostgreSQL database. After reading some articles, I decided to use pg_trgm extension. But according to this documentation we can use both GIN or ...
- 103
0
votes
2
answers
81
views
How to drop all indexes not used for primary keys or constraints
I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added.
Is there a way to drop all indexes except the ...
- 199
2
votes
1
answer
180
views
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": &...
- 121
0
votes
1
answer
95
views
Group by id and get avg time delta between grouped rows
I have a table with ids and timestamps:
postgres=> select id_user, date_seconds from orders.orders order by id_user limit 10;
id_user | date_seconds
------------------------...
- 103
2
votes
1
answer
37
views
Is PostgreSQL (14.1) able to tell me how much RAM it is currently using?
I'm trying to keep track of how much RAM PostgreSQL is using. I need to do this with a SQL query -- not relying on the always incompatible and finicky OS tools.
Is there a way to get the currently ...
- 21
1
vote
1
answer
263
views
Upgrading Postgres major version using logical replication
We have considered some options to upgrade our Postgres servers from the current (10) version to the latest (14). We decided no to do on the primary - since we need to do with the link option (so in ...
- 131
0
votes
0
answers
1k
views
Setting binary path in pgAdmin
I am trying to import data for a table from a csv file. Whenever I click on "Import.." after right clicking on the table, I get the following error message:
" Please configure the ...
- 101
7
votes
1
answer
1k
views
What does BEGIN ATOMIC ... END 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
...
- 1,087
0
votes
1
answer
172
views
Why can't PostgreSQL identity columns be used in CREATE TYPE?
My impression is that each PostgreSQL table has a corresponding composite type associated with it. But it seems that the PostgreSQL identity column cannot be used in a CREATE TYPE statement:
=> ...
- 2,824