Questions tagged [postgresql-14]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
18 views

Why does the EnterpriseDB installer for PostgreSQL on Windows and Mac default to "listening to all"?

PostgreSQL is a free and open source database software. For various reasons which I don't understand, they have "outsourced" the Windows and Mac installers onto a company called "...
user291937's user avatar
0 votes
0 answers
7 views

Postgres 14 upsert column where nulls are unique

I have a table with a column where only 1 null is allowed (nulls should be considered unique). I can a create partial index on this column to enforce this rule. However I can't find a way to use that ...
Jake's user avatar
  • 101
0 votes
0 answers
31 views

Why does postgresql have so many replication slots open?

I use logical replication to move changes from my primary to a replica. Today, to update the replica, I: disabled the subscription on the replica (ALTER SUBSCRIPTION foo DISABLE;) upgraded it to ...
mlissner's user avatar
  • 433
0 votes
0 answers
54 views

Postgresql logical replication suddenly stop (wal status is extended, An existing connection was forcibly closed by the remote host)

I checked the replication status of our production server when i noticed that one of the replicated DB is missing (using pg_stat_replication command). I also check the replication slots and found out ...
chris_dev's user avatar
1 vote
1 answer
27 views

NpgsqlParameter Array of smallints is receiving 0 instead of null value from postgres

I got strange result with NpgsqlParameter in C++/CLI project. NpgsqlParameter^ status = wCMD->Parameters->Add(gcnew NpgsqlParameter("param1", NpgsqlDbType::Array | NpgsqlDbType::...
Thanh Dong's user avatar
0 votes
2 answers
39 views

How to display name and type of table columns in creation order?

I have created this tbl_cities using the code below: CREATE TABLE IF NOT EXISTS public.tbl_Cities (City_ID SERIAL NOT NULL, City_Name VARCHAR(100) NOT NULL, City_State_Region VARCHAR(100), ...
michal roesler's user avatar
1 vote
0 answers
14 views

Postgres - now to optimise a table for high frequency INSERTs?

I need to load data into a table as fast as possible. So far I've done the following. Create the table UNLOGGED Removed all indexes Removed all triggers Created the table in a tablespace on a SSD ...
ConanTheGerbil's user avatar
1 vote
2 answers
73 views

Issue configuring Postgresql for remote connection: 'Job for postgresql.service failed'

I'm trying to configure Postgresql 14.7 for remote connections in a SLES15 environment. I've been following the instructions from Postgres for Zypper installation supplemented with these instructions ...
W. Kessler's user avatar
1 vote
0 answers
18 views

What circumstances will cause a postgres 'unlogged' table to lose data?

For performance reasons I have created a Postgres UNLOGGED table. Everything works fine, performance is blisteringly fast. But very occasionally I lose all the data in it. According to the ...
ConanTheGerbil's user avatar
1 vote
1 answer
66 views

Query with smaller subset to scan taking exponentially longer?

I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains. I have a query which takes almost 17 minutes to run: select * from public.extrinsics where chain_id = ...
Deekor's user avatar
  • 125
2 votes
1 answer
127 views

What permissions are needed to prevent ERROR: permission denied for pg_tablespace_size('pg_global') in RDS?

I'm looking for some help identifying a permission I need to grant to prevent an error in a proprietary app. The App is loading data into an AWS RDS postgresql database. Loading data is working fine....
Philip Couling's user avatar
1 vote
0 answers
21 views

Why group by using additional column is required when using a view [duplicate]

Here I have simplified tables and query, I don't get it why using select from view v_ticket requires grouping by column group_id also, but when selecting it directly from ticket table not? create ...
sh4rkyy's user avatar
  • 119
0 votes
0 answers
12 views

For storing logs that always are queried in batches could make sense to store the full batch as json?

I store each change to the tables in a log table: CREATE TABLE log ( log_id BIGSERIAL, source TEXT NOT NULL CHECK (not_empty(source)), -- The table source_key TEXT ...
mamcx's user avatar
  • 143
0 votes
1 answer
20 views

Convert byte to certain unit

What's the function to convert byte value to certain unit in PostgreSQL-14? for example: Byte Convert Converted Value Unit Value ----- ---- ----- 1 MB 0.000001 ...
DevDon's user avatar
  • 13
0 votes
1 answer
38 views

pg_rewind failing to detect different system in docker container

I have two docker containers running on two different docker hosts. docker host1: 192.168.10.3 docker host2: 192.168.10.4 Container 1 is running on host1 and container 2 is running on host2. Port 5432 ...
Niraj Nandane's user avatar
1 vote
0 answers
33 views

Moving records from one table to another - a few records at a time

I have been using this query (successfully) to move records from one table to another in a Postgres database. WITH moved_rows AS ( DELETE FROM tableB RETURNING * ) INSERT INTO tableA ...
ConanTheGerbil's user avatar
-1 votes
1 answer
39 views

Use pgdump sql file to upgrade version?

Can I use a pgdump .sql file generated by one version as a way to upgrade to another version? For example: If I created a backup on PostgreSQL 14 with: pg_dump --no-privileges --no-owner $DB_NAME > ...
Michael's user avatar
  • 151
1 vote
1 answer
203 views

Create user mapping without password how to configure authentication

I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says. local_db=> select * from employee; ERROR: could not connect to server "...
Aymen Rahal's user avatar
0 votes
2 answers
38 views

Conditionally execute DDL, short-circuiting syntax error

I'm trying to execute a different create or replace function signature according to version() - since anyarray & anyelement are removed in postgres 14 and replaced by anycompatiblearray & ...
OJFord's user avatar
  • 115
1 vote
1 answer
194 views

Unnest array of rows with a tagged column

I'm trying to create a query that will return data in this format: id time value 1 1 2.5 1 2 3.5 1 3 6.4 2 1 8.3 2 2 8.5 I'm using Timescaledb and wish to use one of their downsampling ...
dotconnor's user avatar
  • 135
0 votes
0 answers
57 views

Source a synonym dictionary from a table in PostgreSQL

I am keeping a table with numerous aliases for a given canonical product name. I would like to use that table as a synonym dictionary for full-text search. Unfortunately, PostgreSQL wants to load the ...
ennui's user avatar
  • 71
0 votes
0 answers
28 views

How to accomplish this kind of basic "local replication" in PostgreSQL 14 on Windows 10?

I start (and restart) PostgreSQL like this: pg_ctl restart -D "C:\Users\John Doe\Documents\PGDB" I am trying to also start three "replication PostgreSQL servers" on the same ...
user20986345's user avatar
2 votes
1 answer
207 views

Why changing limit triggers different query plans in postgresql?

Changing only limit from 40 to 50 in following query triggers different execution plans. And unfortunatly one I needed is much slower. So the question is: why this happening and how can I force ...
Gor Rustamyan's user avatar
3 votes
1 answer
402 views

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 ...
collimarco's user avatar
1 vote
0 answers
83 views

Resuming PostgreSQL logical replication (missing pg_stat_replication entry)

I implemented a logical replication on PostgreSQL 14 (from AWS to on-premise) via windows. A few days we experience internet lags. When i checked the pg_stat_replication in publication source, it was ...
starting dba jr's user avatar
-5 votes
1 answer
110 views

Why does PostgreSQL's SQL require this much unnecessary code for INTERVALs with placeholders?

If I try this from PHP: database_query('SELECT * FROM whatever WHERE timestamp < now() - INTERVAL $1', $_GET['number of years back'] . ' year'); ... I just get a total question mark back from ...
Homer's user avatar
  • 99
0 votes
1 answer
37 views

Preserve WALs on promoted node in PostgreSQL 14

I have PostgreSQL streaming replication setup using replication slot. Node1 is primary and Node2 is slave. Now, node1 is down and promoted node2. Node2 is running and in read-write mode. There is ...
Niraj Nandane's user avatar
0 votes
1 answer
27 views

How to append 2 default values to a JSONB column in Postgres SQL 14?

Previously I had 4 default values for a jsonb column in my table cities. create_table "cities", force: :cascade do |t| t.jsonb "config", default: {"A"=&...
Priyanshu0007's user avatar
2 votes
1 answer
59 views

Full-text search with hyphenated integers

Compare the following search queries: SELECT to_tsvector('yellow-green') @@ to_tsquery('yellow & green') as word_word, to_tsvector('Apollo-11') @@ to_tsquery('apollo & 11') as ...
ennui's user avatar
  • 71
2 votes
1 answer
1k views

"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, ...
GDR's user avatar
  • 131
0 votes
0 answers
139 views

pgbench : too many client connections for select()

I was working on benchmark testing for pg10 and pg14. I did the testing for 100 clients with 12 threads in pg14 I was getting following error, "pgbench:too many client connections for select()&...
mathin0496's user avatar
0 votes
1 answer
3k views

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....
WhatsYourFunction's user avatar
0 votes
2 answers
2k views

Repeating error message "password authentication failed for user "postgres"" for PostgreSQL in a simple container

I start PostgreSQL 14.4 in a simple container in an own network (no brigde mode): postgres: image: postgres:14.4 volumes: - /var/data_comm/postgresql:/var/lib/postgresql/data ...
pschuett's user avatar
1 vote
0 answers
175 views

Very slow HashAggregate in PostgreSQL

I'm running into an issue where I am trying to select a sizeable chunk of data, and HashAggregate is taking 15+ seconds to calculate my groups, there are roughly 32500 records being grouped into ...
Ashley Williams's user avatar
4 votes
0 answers
89 views

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 ...
Abhijit's user avatar
  • 181
0 votes
0 answers
206 views

How to export all tables in CSV specifying the column order

I followed the answer indicated below to export all tables in CSV. How can I dump all tables to CSV for a PostgreSQL schema? However, I also need to specify the column order. I am thinking about ...
Dev01's user avatar
  • 1
-1 votes
1 answer
893 views

PostgreSQL CREATE OR REPLACE FUNCTION does not update function body [closed]

I have a function that exists in a PostgreSQL instance. I can say that are several services running on an app server and pages served from a web server that both target the database. Additionally, ...
PhynyxRysyn's user avatar
0 votes
0 answers
205 views

postgresql sequence update

is there any difference in these two statements below to set the sequences after pg_restore? CREATE TABLE public.your_table ( id serial4 NOT NULL, ---default to nextval('your_table_id_seq'::...
Falcon's user avatar
  • 61
0 votes
0 answers
17 views

How can I trace currently running process and activity in PostgreSQL?

I am using 3 node async PostgreSQL cluster. Where sometimes I found that load average of primary node is crossed 100 where threshold is 16, where standby node load average is lower. I am too confused ...
Sheikh Wasiu Al Hasib's user avatar
0 votes
1 answer
48 views

Is there a way to change query / set the epoch portion of the txid_current?

The postgres documentation says that txid_current is the combination of the epoch and the current transaction id, done so that every transaction should be assigned a unique 64 bit number. Is there any ...
zelinka's user avatar
  • 101
1 vote
2 answers
460 views

In PostgreSQL how do I generate a script that will recreate the database and all of its contents?

I'm trying to generate a SQL script that will recreate a Database I have on my installation of PostgreSQL but I have not been successful yet. I'm able to create a backup file of my database using the ...
JTD2021's user avatar
  • 37
1 vote
0 answers
21 views

Wrong query plan for GIN index

I have the following repeatable case. I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats. Test table: CREATE TABLE test ( id serial not null, ...
Sober Proger's user avatar
3 votes
1 answer
81 views

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

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 ...
Biller Builder's user avatar
0 votes
1 answer
61 views

`.psqlrc` setting, but hide the debugging message that this setting has turned on

I have configured psql, the PostgreSQL command line client, with ~/.psqlrc file, and I have the line \timing, to show how long queries took. But psql will print a line telling me that it has applied ...
Amandasaurus's user avatar
2 votes
0 answers
133 views

Indexing 250M URLs for infix search in PostgreSQL

I have a very large Postgres table with about 250M rows, and a text column url. I want to be able to search against this in a performant way, eg. SELECT url_id FROM urls WHERE url ILIKE '%somewebsite....
Jason's user avatar
  • 173
2 votes
2 answers
497 views

Self-referential record, potentially referencing the same row, with a uuid pkey and a not null and foreign key constraint?

Scenario A table holds records that represent a chain (or, more accurate yet, any number of independent chains, respectively). In addition to referencing the direct ancestor in the chain, a record ...
Johannes Pille's user avatar
1 vote
0 answers
30 views

Data retrieval from WAL since cluster init

Postgres documentation is very clear that to utilize the WAL archive for replication or restore, a base backup must first be in place, upon which later WAL records can be applied. Firstly, would it be ...
PinnyM's user avatar
  • 111
0 votes
1 answer
4k views

PostgreSQL 14 server not running locally on Ubuntu 22.04

So yesterday I updated my Ubuntu OS from 20 LTS to 22.04 LTS and installed PostgreSQL from this source. Running, psql -V, I get this: (PostgreSQL) 14.5 (Ubuntu 14.5-1.pgdg22.04+1) Initialized the ...
Juzer Shakir's user avatar
2 votes
2 answers
258 views

How to remove only adjacent duplicates in an array?

While aggregating an array, I need to remove empty strings and then combine all adjacent identical values. E.g.: ["","product","product","","product",&...
AJ AJ's user avatar
  • 25
2 votes
0 answers
344 views

PostgreSQL unique constraint not being enforced

It seems like one particular unique constraint on one particular table is currently not being enforced on every insert. I'm stumped as to how these constraint violations keep happening, as I'm unable ...
xoudini's user avatar
  • 121