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
1
vote
2 answers
Why does PostgreSQL use sequential scan instead of index scan?
I'm learning PostgreSQL and I'm trying to understand the details of how to choose how to scan this database.
I have postgtresql 14.2 and run the following code:
DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO…
ListOFF
- 21
- 4
1
vote
1 answer
Azure PostgreSQL - How to create admin user
We use Azure Database for PostgreSQL flexible servers. My requirement is creating admin user that should be able to anything (DDL/Alter etc.) with any table in any schema throughout the database, irrespective of which user has created that table,…
Tomer Stein
- 11
- 2
1
vote
0 answers
How can I set up AWS Aurora Serverless v2 Postgresql to do full text searches using tsvector in Hebrew?
It seems that Hebrew is not one of the built-in supported languages and it doesn't seem like there is a way to add custom dictionaries in Aurora Serverless Postgresql.
Is there a way to get full text searching to work with Hebrew, and if so, how?
I…
Yosef G
- 11
- 1
1
vote
1 answer
Select rows from table based on if serial is in multirange
Problem
Given a table with a BIGSERIAL I want to query for the rows which have an index contained in a multirange passed as an argument.
The table, trimmed down, looks like:
CREATE TABLE event
(
id UUID PRIMARY KEY,
index …
Martin Barksten
- 405
- 6
- 11
1
vote
0 answers
PostgreSQL ON CONFLICT DO UPDATE by giving a ROW type variable instead of spelling out all columns?
PostgreSQL 14: This works splendedly:
DECLARE
_context audit.etl_failed_execution_contexts;
BEGIN
snip...
INSERT INTO audit.etl_failed_execution_contexts VALUES (_context.*);
But there is a unique constraint, so I want something like:
INSERT…
quickdraw
- 101
- 8
1
vote
0 answers
How to Restore Tables in a Database from csv files in PgAdmin (PostgreSQL)
I am trying to restore a database using PgAdmin on a Mac but the tables do not get imported. Tables came in csv format and compressed in a zipped file but I made it into a tar file because PgAdmin only accepts tar format but I still don't get the…
Laeticia
- 11
- 4
1
vote
1 answer
I want to keep reservation dates in postgresql table column
I have a table named villas and this table has a column named reserved_dates of type reserved_dates in daterange[]
I want to keep the booked dates in the reserved_dates column.
Villas are booked between certain dates .
For Example:
Check In Date:…
Eyyüp Ensar Özcan
- 11
- 1
1
vote
2 answers
Postgresql restore with compressed dump file
I took a postgresql DB Bacup with the below command
pg_dump -Z6 -h localhost test_db -f test_db.tar -p port
Now If I try to restore it with psql facinf the below error:
psql:test_db.sql:117359: error: invalid command…
Santhosh Kumar
- 33
- 5
1
vote
0 answers
User Defined Function performance tuning PostgreSQL
I have the following function which is taking around 6secs time to complete the execution on 25,000 records.
Function:
CREATE OR REPLACE FUNCTION public.fun_getData
(
p_pin VARCHAR(15),
p_datetime TIMESTAMP
)
RETURNS…
MAK
- 6,824
- 25
- 74
- 131
1
vote
0 answers
PERN stack deploying to Railway
Hi I've just finished a PERN project that I want to deploying using Railway, should be very easy according the people on the internet.
But I'm just so lost even to begin, I tried connecting to my Github, and I run into a an error already.
Then I…
bwongyh
- 58
- 4
1
vote
0 answers
Why jit can destroy performance on Linux server?
We have a Postgresql development server on Windows and a production server on Ubuntu Linux. The Postgresql release versions are slightly different, the newer in on linux (Postgresql 14.5).
One database was exported from development and imported in…
Filippo
- 1,123
- 1
- 11
- 28
1
vote
0 answers
In postgresql, copy a view from one database on remote server to a table on local server
I have a remote postgresql server running with views in one of the schema. I need to copy the data from a view in the remote database server and put it in a new table in my local database server
Assume
remote
remote host: hostA
remote port:…
Vinit Khandelwal
- 490
- 8
- 20
1
vote
1 answer
Create a nested json with column values as key-value pairs
I am trying to build a JSON from the following tables
table : car_makers
+------+-------------+---------+
| cmid | companyname | country |
+------+-------------+---------+
| 1 | Toyota | Japan |
| 2 | Volkswagen | Germany |
| 3 |…
an33sh
- 1,089
- 16
- 27
1
vote
1 answer
Translate greek characters in PostgreSQL full-text search
I would like to translate greek characters to their common latin equivalents for the purpose of full-text search.
Consider the following:
SELECT
to_tsvector('english', 'α-decay') @@ to_tsquery('α & decay') AS greek_greek,
…
ennui
- 73
- 5
1
vote
2 answers
PostgreSQL interval returned in days
I have this basic PostgreSQL query:
SELECT AGE('2021-01-21', '1942-11-20');
Which returns an interval in days:
output: 28531 days, 0:00:00
I am using PostgreSQL version 14, according to the docs, AGE() should return a symbolic result in years,…
Ewald
- 11
- 1