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
2
votes
1 answer
How to delete duplicate rows and keep just one row with multiple columns
I have duplicate data in a table called bank_currency that looks like this:
currencyid | bankid
--------------------
8 1
8 1
8 1
16 2
16 2
16 2
14 3
14 3
14 …
volume one
- 6,800
- 13
- 67
- 146
2
votes
1 answer
PostgreSQL: syntax error at or near "refcursor"
When I work with Microsoft SQL Server databases, I sometimes return multiple result sets from stored procedures. I often return so many that it becomes hard to follow which is which. To solve this problem, I follow a convention I learned from a…
kol
- 27,881
- 12
- 83
- 120
2
votes
2 answers
Does PostgreSQL support replicating only a subset of the publishing columns?
I've been reading about logical replication in PostgreSQL, which seems to be a very good solution for sharing a small number of tables among several databases. My case is even simpler, as my subscribers will only use source tables in a read-only…
coterobarros
- 941
- 1
- 16
- 25
1
vote
1 answer
PgAdmin 4 Unexpectedly Quits on Mac OS
I'm using the M2 Mac Pro 14 and I've reinstalled PostgreSQL through Brew and pgAdmin4 through the web-site, separately. However, I'm facing an issue where I can't open PgAdmin 4; it keeps showing an "pgAdmin has unexpectedly finished" error.…
yjoos
- 21
- 2
1
vote
1 answer
find out elevated privileges in Postgres database
I was trying to find out the user/roles which got elevated privileges in the postgres database.
Elevated privileges like alter/drop/create/owner of table i.e. any user who can change the existing tables or can create/drop the table..
I was going…
Sandy
- 419
- 1
- 8
- 15
1
vote
1 answer
SQLAlchemy create unique constraint with the hash value of multiple columns
I want to create a UniqueConstraint using a hash of multiple columns.
Postgresql 15 provides the hash function hash_record_extended, but I couldn't figure out the syntax.
__table_args__ = (
UniqueConstraint(
func.hash_record_extended(
…
Roy Wang
- 11,112
- 2
- 21
- 42
1
vote
0 answers
postgresql-14 with huge_pages won't start
If I try to enable "huge_pages = on" PostGresql fail to start :
FATAL: could not map anonymous shared memory: Cannot allocate memory
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory,…
jimbolino
- 95
- 1
- 9
1
vote
1 answer
json string to table view in Postgres 14
Working with data as a json string
{"tc_0": "Namefield1:ValueField", "tc_2": "Namefield2:", "tc_1": "Namefield3:", "tc_3": "Namefield4:ValueField:ValueField"}
I need to convert them into entries like this
select *
from
(
…
Ambasador
- 365
- 3
- 14
1
vote
1 answer
sum until reach a value postgresql
I need to create a query to sum values until I reach a certain amount.
select client_id, dt, SUM(value)
from my_table
group by 1, 2;
My table is something like this:
client_id | dt | value
----------+------------+-------
23 |…
devinho
- 404
- 4
- 18
1
vote
3 answers
converting records to the desired json format in postgres 14
I have data in the following form
select t.name_field, t.value_field
from
(
values
('name_1', 'val_fld_1'),
('name_2', null),
('name_3', 'val_fld__3')
) as t(name_field,value_field);
From this data, I need to get a…
Ambasador
- 365
- 3
- 14
1
vote
1 answer
How to add new key-value pair in each object of JSONB Array- PostgreSQL
I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below JSON structure:
[{
"name": "abc",
"email": "abc.pqr@gmail.com"
},
{
"name": "xyz",
…
srp
- 619
- 7
- 18
1
vote
1 answer
Why TO_CHAR(interval, format) works differently for 'weeks' than days/months in Postgres?
I am trying to convert an interval value to a string value using TO_CHAR() function. But I noticed when I pass formatter for weeks, it always returns at least 01, whereas it works as expected for days and months. For example:
postgres=# select…
DGulshan
- 11
- 3
1
vote
0 answers
auditing the user connectivity in PostgreSQL database
is it possible to find out when was the users last connected to DB, and stored it in a table for tracking purpose, by writing trigger on pg_stat_activity view.?
Thank you
Raju
- 27
- 5
1
vote
1 answer
How to include columns from two tables based on a common column value but include rows only from the left table?
I have two tables tb1 and tb2. tb2 is connected to tb1 via a foreign key named tk_id. Here is how my two tables look like
tb1
Column | Type | Collation | Nullable | Default …
Souvik Ray
- 2,899
- 5
- 38
- 70
1
vote
0 answers
Why won't default potgresql text search parser tokenize Georgian words?
I am trying to get postgresql full text search work with the Georgian language. I created a dictionary:
create text search dictionary georgian_hunspell(...);
and a configuration with the default parser:
CREATE TEXT SEARCH CONFIGURATION…
Andrew Slock
- 21
- 3