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
0
votes
0 answers
PHP is connecting to AWS PostgreSQL 14.6 from local machine but not from web server
The PHP is connecting to an AWS PostgreSQL 11.5 database from the web server. It has the same VPC's as the one I am trying to connect to.
Here is the PHP code that works when connecting to the database from local machine:
$db = pg_connect( "$host…
0
votes
0 answers
Is there a way to replicate/sync the postgresql views from a primary database to the table on secondary postgresql database?
I have a view on the primary database, now I want to have the data of this view to be on the secondary database but in a table. Is there a way or a third-party tool to keep the view data in sync with the table in postgres?

Sujeet Chaurasia
- 97
- 8
0
votes
1 answer
Unable to Connect to PostgreSQL Server hosted on a Virtual Machine from my local machine
I am trying to connect to a Remote PostgreSQL Server(WINDOWS 8 Server) from my local machine (WINDOWS 10 Machine) and i am getting connection timeout error.
I have tried following things before posting this question.
Checked and made sure that…

ManojRawat
- 61
- 1
- 8
0
votes
0 answers
I upgraded JDBC driver to postgresql, strange thing happened
I upgraded JDBC from postgresql-9.2-1000.jar(PostgreSQL 9.2.1) to postgresql-42.5.0.jar(PostgreSQL 14.6) and strange thing happened.
With the same query, It takes slow after fourth, or ninth execution.
(It doesn't happen with…

Thttp
- 1
0
votes
1 answer
PostgreSQL COALESCE with multiple jsonb_path_query_first returns null
For example I have the next query:
SELECT COALESCE(
jsonb_path_query_first('{"a": null, "b": "bb"}', '$.a'),
jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;
it return null although I use COALESCE.
How can I return in the…

Shay Zambrovski
- 401
- 5
- 21
0
votes
1 answer
ActiveRecord query jsonb with LIKE as though it were a string
I want to write a Rails ActiveRecord query that can match a jsonb database column with SQL LIKE wildcards, as though it were a string or text field. In other words, I am trying to match a substring within the jsonb, no matter what key or value that…

John Skiles Skinner
- 1,611
- 1
- 8
- 21
0
votes
1 answer
Primary key and FK constraints on partitioned tables
I have 2 tables with more than 10 million rows.
create table TBL1 (
GUID UUID not null default gen_random_uuid(),
DT DATE not null,
...
constraint tbl1_pk primary key (GUID, DT)
)
partition…

Milediira
- 11
- 2
0
votes
0 answers
Ecto ConnectionError timeout after Postgres DB Partitioning Implementation
Recently we updated the database structure of our multi-tenant Phoenix app to implement declarative partitioning for several tables (using PostgreSQL 14 on an AWS RDS instance). Following Ecto's guides on multi-tenancy, we were able to ensure that…
0
votes
0 answers
Postgresql 14 - comit in procedure raises error
What I've read, it should be possible in Postgresql 14 to add commit in a procedure, but in my case it raises an error.
For testing it, I wrote following:
CREATE OR REPLACE PROCEDURE public.committest()
LANGUAGE plpgsql
AS $procedure$
declare
…

tsschulz
- 23
- 4
0
votes
1 answer
autovacuum running for hours postgres
For few of the tables autovacuum is running running in parallel, one table process is locking other tables autovacuum process and performance of the other queries (select, insert, update) very slow or almost stopped.
in my postgressql.conf file, I…

Raju
- 27
- 5
0
votes
1 answer
Postgres upsert: name of the excluded column
I have two Postgres tables:
Table 1
CREATE TABLE tmp.pm_update_20230101 (
fid varchar NULL,
fecha date NULL,
p float4 NULL
);
Table 2
CREATE TABLE aemet.pmes (
indic varchar NOT NULL,
fecha date NOT NULL,
prec float4 NULL,
…

Luis
- 71
- 5
0
votes
0 answers
Optimizing a query with JSONB aggregations and CTE
I have an SQL query that I'm trying to speed up by adding an index or changing the query itself.
However, the execution plan says I have slow HashAggregate and Sort operations.
Here's the visualised execution plan:…

Denis Yakovenko
- 3,241
- 6
- 48
- 82
0
votes
0 answers
Intellij postgres can't create tables even though connected
everything is running without any problems and no problems. Also Database connected to the IntelliJ Idea but tables are not creating database
How can i create tables?
0
votes
0 answers
How to convert row to column without define the column name in postgresql
I have below table.
If a new indicator is added, it should be entered in the Auto Convert column
(id int,indicator text,value int
);
INSERT INTO _test (id,indicator,value) values
(1,'A1',10),(1,'A2',12),(2,'B1',20),(2,'B2',22);
--using the…

sahil
- 109
- 8
0
votes
1 answer
Optimize query to get distinct records
I have the following query to get product sold count.
Present Query: Which gives duplicate records.
--(322212 rows affected)
--00:00:08 Time Taken
SELECT pc.qty_typecode ,
pc.prdcost, AS prdcost ,
pc.d1cost, AS d1cost ,
…

MAK
- 6,824
- 25
- 74
- 131