Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
264