Questions tagged [postgresql-14]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
  • 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 -------------+----------+-----------+----------+--------- ...
user avatar
  • 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 | ...
user avatar
  • 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 -----------------+---------------+-----------+----------+----...
user avatar
  • 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,...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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": &...
user avatar
  • 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 ------------------------...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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: => ...
user avatar
  • 2,824