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.
80
questions
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 "...
- 21
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 ...
- 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 ...
- 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 ...
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::...
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),
...
- 125
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 ...
- 1,027
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 ...
- 113
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 ...
- 1,027
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 = ...
- 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....
- 161
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 ...
- 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 ...
- 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
...
- 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 ...
- 115
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
...
- 1,027
-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 > ...
- 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 "...
- 11
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 & ...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 123
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 ...
- 523
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 ...
-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 ...
- 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 ...
- 115
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"=&...
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 ...
- 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, ...
- 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()&...
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....
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
...
- 1
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 ...
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 ...
- 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 ...
- 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, ...
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'::...
- 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 ...
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 ...
- 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 ...
- 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, ...
- 11
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 ...
- 176
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 ...
- 827
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....
- 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 ...
- 123
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 ...
- 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 ...
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",&...
- 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 ...
- 121