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
7 views

Joining two tables partitioned tables having index with the partition key and joining columns does not help much

I have two partitioned tables that have an index created on the joining key and the partition/primary key (because on partitioned tables on Postgres the partition key must belong always to the index). ...
0
votes
0answers
6 views

how can I tell if my postgresql instance supports kerberos/gssapi authentication

I installed it following instructions from digital ocean how-to-install-postgresql-on-ubuntu-20-04-quickstart % sudo apt install postgresql postgresql-contrib I also did a 'show all' in psql and didn'...
0
votes
2answers
12 views

How to quote arrays in postgres copy statement to inser into JSON field

I have some data that looks like this: id,bo,c,url 1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]" 2, "more text", 83, "[{'id': 'W3nAl', '...
0
votes
0answers
18 views

Is my schema correct?

The system has 3 main entities: Staff Courses Students I will try to explain only the Student table, and I hope the rest of the data will be clear. [ Students ] student_id: Primary Key full_name: * ...
0
votes
1answer
17 views

Update with distinct on violates unique key in postgres?

update schema.current_status a set status = stat.overall_status, created_date = stat.created_date, parent_id = stat.parent_id, id = stat.id from (select distinct on (id) id, ...
-1
votes
0answers
15 views

handling time data for a materialized view [closed]

I am migrating data from one source to postgres. My materialized view is created from this and order by time is important. However, I will get new data that is in the past. If I insert this data. How ...
0
votes
1answer
22 views

Postgres 13 - Grant to Active Directory Groups?

Is it possible to grant an AD group in PG 13 without sorting to creating the group in the database? Similar to how AD groups can be granted directly in SQL Server. The PG server is on a Windows server ...
0
votes
1answer
23 views

general design pattern for smushing rows into one column

I have a table of people : id, person_name I have a table of tags: id, tag_name there is a junction table for the tags that connects people and tags: id, person_id, tag_id I have a query to get a ...
0
votes
1answer
15 views

Configure listen_addresses automatically in custom installation

I have included Postgres in a custom installer for a Windows application. I do not use the installer binary, but extract the Postgres zip archive for installation and then run initdb from a batch ...
1
vote
2answers
61 views

What did I miss in EXPLAIN that hinted at server crippling loads?

I reviewed EXPLAIN ANALYZE output for a query and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment. ...
0
votes
1answer
19 views

postgresql: procedure to return the number of occurrences of given year and service [closed]

I am creating a procedure to return the number of times a given service is done in a year. The procedure cannot return a NULL value. The table I am taking the data from is called "tb_orders" ...
0
votes
1answer
21 views

Trigger to prevent column value from A to B, and from B to A

I'm trying to create a trigger that prevents a column to be updated as follows: If column value is "A", it cannot be updated to "B", and if column value is "B", it cannot ...
0
votes
1answer
17 views

Join two Json arrays to one with key and value

I have two jsonb columns(keys,values). Eg: keys colum value = ["key1","key2","key3","key4"] values column = ["val1","val2","val3",&...
0
votes
0answers
15 views

PostgreSQL - enforce uniqueness and cardinality of relationships [closed]

I'm trying to design a secured schema to support a feature in an existing multi-tenant database. The existing database holds customers and child branches (many branches to one customer). I'm trying to ...
1
vote
1answer
27 views

Improving indexing of jsonb data when the json structure is well defined

I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
0
votes
1answer
25 views

How do I see where postgres privileges are coming from for a role?

Im using google cloudsql but this is a more generic postgres question. I have a role (that is already created and managed by cloudsql) called cloudsqliamserviceaccount. The cloudsqliamserviceaccount ...
3
votes
2answers
21 views

Conflict resolution for hash-backed unique constraint of large text bodies

So we've just run into ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un" Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index ...
0
votes
1answer
10 views

How can I make an index covering a ts_vector expression and also an integer?

I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this. SELECT "foods".* FROM "foods" INNER JOIN (SELECT "foods&...
0
votes
1answer
32 views

Function without parameters, and calling a function inside

I'm new with PostgreSQL and feeling quite overwhelmed.. What I'm trying to achieve is something like this: Wanted outcome year descrip percent 2019 Word word word 0.10 2018 Word word sad 0.80 2017 ...
0
votes
1answer
12 views

Archive logs Pg_archive cleaner

I have Postgres installed on windows 10. I have master-slave replication ( 1 pc is master and 2 other pc are slaves). I have archive logs saved on a file on master pc and slaves are connected to that ...
0
votes
2answers
36 views

not returning 0 if value is NULL

Everything works as intended, but function should return 0 if value is NULL. Currently it returns nothing (see problem at the end of the post). How could I make to return 0 if no order is found? '...
-1
votes
1answer
21 views

Initializing Posgres Database on External SSD [closed]

I have been struggling to setup a Postgres database on my external SSD. The goal is to store a database larger than my internal storage on the external SSD. Postgres is successfully installed locally ...
1
vote
0answers
19 views

History data in CUBE

Not sure if this is the right place to ask. I am working on an ETL procedure that gathers the data about medication consumption and I have something like this: Dim_Patient, the dimension for all the ...
0
votes
0answers
22 views

Deconfliction join table [closed]

I have a table that gets populated by various means and I need to create a join table to deconflict the data. To do the deconfliction, we run algorythms to determine the probability ID# 1 is the same ...
1
vote
1answer
38 views

Office Hours in PostgreSQL

How to store office hours in PostgreSQL rows, so that I can calculate the office hours. Example: We have open from 9:00 to 18:00 from Monday till Friday. Saturday we have open from 10:00 to 15:00 ...
1
vote
1answer
25 views

Cumulate count between date with 0 when there are records for a given day

How could one write a Postgresql query that gives both daily and cumulative counts without gaps? E.g. if there is no data for a given day, it would show 0 as a daily count for that that day and the ...
0
votes
1answer
34 views

Need help understanding Postgres materialized views under the hood

postgres 9.6.20 Need some help getting an accurate mental model of whats happening with postgres materialized views. Based on this from postgres docs (https://www.postgresql.org/docs/9.6/rules-...
1
vote
1answer
20 views

Using a commit inside plpgsql block works in one case not another

Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
0
votes
1answer
15 views

How can I dynamically create views in Postgres 10 using variables?

I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and ...
1
vote
1answer
24 views

Am I supposed to let PG know in advance before I back up its datadir?

I run PostgreSQL on Windows. Its data dir is located in "My documents". I often back up that entire dir tree (among others) with ROBOCOPY, which "robustly" copies the dir tree to ...
2
votes
2answers
48 views

PostgreSQL not SELECTing by RETURNING value of INSERT

I have a query that's supposed to insert a new row, then return it. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after ...
4
votes
2answers
374 views

In postgres is there any real drawback on using a json field over multiple tables and one to many relationships?

In our current application we are basically going to provide something called "translations" for each field. So a table would originally be like: CREATE TABLE organisation ( id SERIAL ...
0
votes
0answers
12 views

How can I manage newly created tables permissions in the PostgreSQL? [duplicate]

I want to create default permissions on the schema and automatically assign these permissions to newly created tables. For this purpose, I've created a role and assign a user. Afterward, I've created ...
6
votes
1answer
299 views

PostgreSQL connection URI fails, but parameters work

Running postgreSQL 13.2 with ssl on. I need to connect to a database from a third party application that requires a connection URI, but it is not working. So I tested connecting with psql using two ...
0
votes
1answer
29 views

Memory Optimized Azure Postgres

I am trying to make sense of the "memory optimized" setting for Azure Database for PostgreSQL. Main memory data management can be viewed as quite different from secondary storage data ...
1
vote
1answer
25 views

PostgreSQL keeps asking for user password with “trust” authentication enabled

I created a user john with createuser and didn't supply any passwords for it, i.e. no -P option specified. I then proceeded to add the following line above all other lines in the pg_hba.conf file: ...
4
votes
1answer
69 views

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
1
vote
1answer
29 views

Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What'...
0
votes
1answer
25 views

pg_ctl says “no server running” when I'm connected to postgres and running queries

EDIT: the original question can be found below. Basically, this is a misconception on my part of how pg_ctl works and if you're getting this message too (from pg_ctl), then the TLDR; is, as @jjanes ...
1
vote
0answers
15 views

Why does a GIST index on a cube column in PostgreSQL actually make K-Nearest Neighbor (KNN) ORDER BY queries worse?

Adding a GIST index actually seems to make K-Nearest Neighbor (KNN) ORDER BY queries on cube columns worse in PostgreSQL. Why would that be, and what can be done about it? Here's what I mean. In a ...
0
votes
0answers
14 views

Postgres - how to put DB in Maintenance mode [duplicate]

Sometime I need to do bigger update on DB, hoping my local user will not be interfering But happens that sometime is connecting to DB causing my scripts deadlocks ... Only (easy) way to address that ...
0
votes
0answers
37 views

PostgreSQL remote connection failing

I've recently installed PostgreSQL on my PC as a requirement for my DB class, and as a practice, I decided to try and setup a remote connection for it so I can access it via online services. But after ...
0
votes
1answer
27 views

Cascading PostgreSQL triggers between three tables

I have a ‘test’ PostgreSQL 12 schema, defined with initial tables: begin; drop schema if exists test cascade; create schema test; set search_path = test,public; create table jobs ( job_id serial ...
0
votes
2answers
26 views

Weird tables with numbers and underscores in PostgreSQL execution plans

I'm debugging performance problems with my DB and see some unusual entries in the DB log: 2021-04-21 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan: Query Text: select systemv0_....
0
votes
1answer
21 views

Parsing DATE while copying csv file into PostgreSQL table

I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads ...
0
votes
0answers
16 views

How to upsert when i use pandas to_sql? [closed]

can anyone help me how can i do an upsert on this code? the goal is to overwrite the value if the primary key already exists I have already looked at all the corresponding questions but with this code ...
1
vote
1answer
31 views

Best index for case insensitive match on one field and ordering by another field

I want to run a query like this: SELECT * FROM "core_user" WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com') ORDER BY "core_user"."...
2
votes
2answers
78 views

How to set statement timeout per user?

I have multiple users in Postgres. I would like to set up different statement timeouts for different users. Eg: Guest 5 minutes and Admin 10 minutes. Is it possible in Postgres 11.11?
0
votes
0answers
13 views

PostgreSQL for user management and another database for file management (Blob storage)

I am working on a project with video hosting and online editing platform with a microservice architecture in mind. One of my teammates uses PostgreSQL SQL for user management, but I need to handle ...
0
votes
2answers
33 views

PostgreSQL: Can i use tuple in where clause?

Actually might be a simple question: Given 2 tables: table1: A,B,C (A1,B1,28471), (A1,B2,01244), (A2,B1,1283a), (A2,B2,82r7e); table2: A,B,D,E,F,G (A1,B1,18,1,6,8), (A2,B2,18,2,3,0), (A3,B1,18,7,1,4),...

1
2 3 4 5
266