Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,191
questions
0
votes
0answers
4 views
is postgresql identify would be continue in new version of PostgreSQL
Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and not continue, it will waste many ids, I read the docs and found PostgreSQL add indentify as a SQL ...
1
vote
1answer
21 views
How to represent a list of entities within a table of the same entity in PostgreSQL?
I have a table called user with an attribute friendsList which has to be a list of ids pointing to different users. I really have no clue how to do this using Postgres
0
votes
0answers
7 views
Local Postgres Server on Mac Pw Auth Fail
Disclaimer: I am new to MacOs. Haven't found any useful related question.
After successfully installing postgresql 13.2 via homebrew on MacOs 11.2.1 (BigSur) i run into the following problem:
Use ...
0
votes
1answer
7 views
Postgres view role permissions
I am trying to work out how database roles work.
Here is my user case...
create role user1 login password 'user1';
create schema authorization user1;
CREATE ROLE DEV_ROLE;
grant connect, temporary ...
0
votes
1answer
26 views
table organization with data that will never change
for reference, almost all my DB experience is with mongodb and this is my first postgres project.
I'm storing data updates from decentralized finance crypto pools.
The data structure I'm exploring is ...
0
votes
1answer
16 views
Disk space requirement for REINDEX in Postgresql
There is a server with postres 13 running out of disk space.
It contains a table metrics that occupies 90% of the entire database.
I have deleted old records in this table.
Due to this table is used ...
0
votes
0answers
18 views
Very slow query for massive stats calculation
In our app, we need to calculate the price comparison between a listing and its applicable comparables. It needs to happen for all active listings in DB on daily basis. The numbers of listings we are ...
0
votes
2answers
21 views
Join multiple tables without duplicated results and without UNION in PostgreSQL
Lets assume we have two tables A and B in PostgreSQL:
A
title
first_group_id
second_group_id
My Title
1
2
B
group_id
element_name
1
First
1
Second
1
Third
2
Fourth
2
Fifth
I want to get the ...
0
votes
0answers
15 views
Same query taking different execution times on equivalent RDS instances
The train table has around 60million records all of which have their updated_at timestamp in the year 2021. The schema of the table is as below.
Table "public.train"
...
1
vote
1answer
39 views
How do I debug Postgres' join strategy?
In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can't provide a reproducible example for ...
0
votes
0answers
27 views
An update operation that would target 0 rows, aborted by timeout
I have an update operation that is being executed on a quite large table (postgresql@10):
UPDATE contacts_trashbin
SET op_id='\x60771801dbed2d6e021ec067'::bytea
WHERE contacts_trashbin.company_id = '...
0
votes
0answers
27 views
Select table and count aggregate information
i have the following DB
CREATE TABLE public.contacts (
id int4 NOT NULL,
"name" varchar NULL,
address varchar NULL,
CONSTRAINT contacts_pk PRIMARY KEY (id)
);
CREATE TABLE ...
0
votes
0answers
12 views
Mapping auto incremented primary keys of deeply nested objects in PostgreSQL when moving between databases
Situation
I have a Postgres database where most of the data belongs to a specific tenant. Most tables have an auto-incremented primary key, and the tables have a deeply nested web of foreign keys ...
0
votes
1answer
30 views
How do I add a where statement at the end of query for a column that does not have a table to reference from?
select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v....
0
votes
0answers
19 views
Index on json list
I have an attribute (ids) defined as jsonb in Postgres that contains a list of values:
CREATE TABLE test.id_data2 (
key bigint NOT NULL,
ids jsonb,
CONSTRAINT tag_data2_pk PRIMARY KEY (...
0
votes
1answer
20 views
How to find the counts of word occurrences in a column?
I have a column containing text in postgresql. Now I want to find, for a given input word, the counts of occurrences of the word in my text column.
So I have
date_col | text_col
-------------+----...
0
votes
0answers
14 views
PostgreSQL cascading triggers based on the condition of which other table triggered it or a value given by the other trigger?
I want to keep (slightly different) tables synced to one another and with only 2 tables, this is fine. Because of pg_trigger_depth() = 0 in the WHEN clause they will not trigger each other.
I ...
0
votes
0answers
7 views
PgPool setup with remote master and local read replica
I have working two server postgres setup with local (in docker) replica and remote (via VPN) master.
Without enabled load balancing everything (all queries) are redirected to master with enabled load ...
0
votes
1answer
16 views
Checking if entities match an id in on relation where it could show up in either of two columns
The problem
I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list.
Given the owner_id of a Group its group_id I ...
0
votes
0answers
21 views
connect to remote PostgresDB through VPN
I am not very advanced DB Admin/User, know only basics. Though as developer I need to have a direct access to a production-deployed database by using my VPN configuration so that I wouldn't need to ...
0
votes
0answers
20 views
Postgres insert fails silently
I have a table with about 7000 rows defined
CREATE TABLE public.cage (
cage_id serial NOT NULL,
location_id int4 NOT NULL,
"name" text NULL,
lon numeric(11,8) NOT NULL,
lat numeric(10,8) NOT ...
0
votes
0answers
36 views
Convert a numeric number to a letter PostgreSQL
i have this function : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=22a67dedd54291d7ffb527e2f9d482ca
this function retrieve only integer part, i want to retrieve also decimal part.
i tried : ...
0
votes
1answer
16 views
Postgresql schema does not exist [closed]
This query works fine.
select
icca.invoice_id as "DR Inv Reference",
icca.total_ex as "Line Tax Exclusive",
icca.total_inc as "Line Tax Inclusive",
icca....
0
votes
0answers
7 views
Get command line of connection, even if “pg_stat_activity.application_name” is empty
I would like to know who is blocking the development DB from being dropped.
But "pg_stat_activity.application_name" is empty.
Is there a way to get to the command-line nevertheless?
...
0
votes
1answer
15 views
When to cluster a table using an index? [postgresql]
https://www.postgresql.org/docs/9.1/sql-cluster.html
Now suppose I have pg_repack installed so that it won't lock the table when clustering.
Should you cluster a table using an index that has a high ...
0
votes
1answer
31 views
Postgresql 13 pg_dump utility - pass a password through the Lazarus app in Windows
I did a small app that trying to pass a password to pg_dump console utility. It's working, but with password provided. Please see full code here and the problem there: no way to pass a password to ...
0
votes
1answer
18 views
How to get count of an object, through 3 different tables in postgres with ID's stored in each table
I'm currently using Postgres 9.6.16.
I am currently using 3 different tables to store a hypothetical users details.
The first table, called contact, this contains:
ID, Preferred_Contact_Method
The ...
0
votes
1answer
31 views
Understanding Postgres query planner behaviour on GIN index
Need your expert opinion on index usage and query planner behaviour.
\d orders
Partitioned table "public.orders"
Column | ...
3
votes
2answers
118 views
Query 2 tables without a join
I am given the schema:
BOOKING (hotelNo, guestNo, dateFrom, dateTo)
GUEST (guestNo, guestName, guestAddress)
I an asked to formulate the query for:
List the guest numbers associated with the first ...
0
votes
1answer
12 views
Postgresql script doesn't work as I expect
DO $$
DECLARE companyNrs text[] = ARRAY(SELECT LPAD(companynr::text, 5, '0') FROM s_users);
companyNr text;
BEGIN
FOREACH companyNr IN ARRAY companyNrs
LOOP
raise notice '...
-1
votes
2answers
42 views
Postgres: How can you select last 12 months only, and return total count of each month, even with null/empty months?
I'm using Postgres, and my dataset is an orders table. I'm trying to learn how to show the total amount of orders per month, per user (assuming there's only one user in this case).
My dataset:
id, ...
0
votes
0answers
33 views
PostgreSQL: Query runs fine in Prod but really slow in QA
Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
0
votes
1answer
16 views
'Could not fsync file' while running VACUUM FULL on a table
I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL.
For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error:
ERROR:...
0
votes
2answers
20 views
PostgreSQL: Insert into JSONB key failing with syntax error at or near “->>”
Running the following query auto-generated by Eloquent's upsert function is throwing a syntax error and I'm not sure why? I couldn't find a supporting statement that says PostgreSQL supports the ...
1
vote
2answers
33 views
How to write a query for exact pairs of groups
I am given the schema:
EMPLOYEE(Fname,Ssn,Dno)
DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date)
PROJECT(Pname,Pnumber,Plocation,Dno)
WORKS_ON(Essn,Pno)
fiddle: https://www.db-fiddle.com/f/...
0
votes
0answers
13 views
Count Values within Two Different Date Ranges + Include Counts of Zero
I have a smart travel card dataset containing records for every boarding transaction validated during a 4 week period in 2019, and the corresponding 4 week period in 2020. The dataset corresponds to ...
0
votes
2answers
15 views
How to update a table from a query result of another table related to the first one?
Here is my attempt. What is the correct syntax to achieve this?
UPDATE table_a a
SET a.firstCreationTime = c.created_at
FROM (
SELECT created_at
FROM table_b b
WHERE a.id = b.id
ORDER ...
0
votes
0answers
23 views
PostgreSQL initdb initialize a new database cluster has stopped working
During install of postgresql using binary zip file , this error occurs
initdb initialize a new database cluster has stopped working
on this command
initdb.exe -U postgres -A password -E utf8 -W -D C:\...
1
vote
1answer
47 views
Postgres database insert become slow after 10 days, and then it needs a full vacuum
We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and ...
0
votes
0answers
11 views
Workaround for repmgr of PostgreSQL in Windows Server OS 2019 [closed]
I have been provided a VM on Windows Server OS 2019 for installing my web-app which uses PostgreSQL 9.6.6 as DBMS. As per the documentation at this link, there is There is no support for Microsoft ...
2
votes
1answer
7 views
Impact on Recovery Time Using A Single Perpetual Base Backup and WAL Archives
What would be the impact on recovery time when restoring a Postgres database from a backup where one base backup was taken at the start of the backup strategy and then only applying WAL archives from ...
0
votes
1answer
16 views
Get another function to return timestamp in PostgreSQL [duplicate]
Can I have another function to return the last timestamp.
I want to compare with this query to use the best query.
My query:
CREATE OR REPLACE FUNCTION f_last_update1(nomTable text , OUT updated ...
1
vote
1answer
29 views
Recursively find the path to all leaves descending from a given parent in a tree
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
0 1
| |
2 3
| |
4 5
...
0
votes
1answer
18 views
Postgresql: sort by value position in array column, then by secondary order
I'm not quite sure what the best way to phrase this is...
So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from ...
2
votes
0answers
21 views
Drop intermediate results of recursive query in Postgres
I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table.
Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
0
votes
1answer
25 views
find if the column of the PostgreSQL table is empty with minimum cost
I need to find if there are any values written to the column but with minimum cost.
There is a table with ~1B rows and about 100 columns with data. I need to find columns that have no data at all (all ...
1
vote
1answer
28 views
Filtering Escape Characters in PostgreSQL
I am trying to filter distinct serial number from a table and join two tables. Current my code looks like
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM ( select
serial_nbr,...
0
votes
1answer
50 views
Convert a number to a letter postgresql
i have a function that convert a number to a letter.
i want to optimise this function. see query: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2586d3e0b98df1c899defc7b4078e1ef
i want to have ...
0
votes
2answers
25 views
Concatenate string based on a condition
I want to assign a value to a new column based on a condition for other rows associated with a user_id.
A user can see the same step with different devices at different times. However, only the first ...
1
vote
1answer
11 views
Master ip has changed in postgresql replication
I have postgresql replication system with two machines, suppose I want to change the master ip,the streaming to slave machine will stop. Can I resume streaming with the new master ip instead of doing ...
