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
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
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'...
1
vote
1answer
2k views

How can I control how Pentaho DI formats integers coming from a text file?

I have a simple pipe-delimited text file with integers recorded as a sequence of numbers: 012345|00678 |various|text|fields There are some leading zeros, and some trailing white space, but no ...
9
votes
5answers
11k views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
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 ...
0
votes
2answers
500 views

Is it possible to insert multiple IP addresses in a single entry in a pg_hba.conf file?

Let's say I have the following 3 child IP addresses - 192.168.10.15 ,192.168.10.41 and 192.168.10.81 1 master IP address - 192.168.10.37 I want the 3 child IP addresses to be able to access the master ...
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: * ...
3
votes
2answers
430 views

How to check if the current connection is in a transaction?

I need my program code to ensure that certain part of logic is being executed within a transaction. What query would tell me the current transaction ID / other information that would allow me to ...
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
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, ...
0
votes
1answer
1k views

install postgresql in unattended error [closed]

I try to install PostgreSQL within myapp.exe (which used advanced installer to build) in unattended mode, I set parameters as below: --mode unattended --superpassword xxxxxxx --prefix c:\programs ...
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. ...
-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 ...
10
votes
1answer
9k views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE (...
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
191 views

Postgressql - could not fork new process for connection: Cannot allocate memory

Postgres instance restarted multiple times with out of memory errors. could not able to get what process would have consumed all memory. 2020-09-03 04:54:14 PDT LOG:  could not fork new process for ...
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" ...
2
votes
1answer
527 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
-2
votes
1answer
2k views

Inserting null value without quotes

I'm creating a PHP application which relies on PostgreSQL. Now I have this query: $query= 'INSERT INTO "users" ("name", "username", "email", "phone") ...
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
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
230 views

In which timezone does psql display “timestamp without timezone”?

What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a ...
1
vote
2answers
241 views

Query is failing

I am running a query from SQL Server 2019(64bit) SSMS to PostgreSQL on AWS through Linked Server. Other queries are working fine but this particular query is failing due to message column. My query is ...
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",&...
133
votes
6answers
170k views

How to get the name of the current database from within PostgreSQL?

Using \c <database_name> in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: ...
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
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 ...
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 ...
2
votes
1answer
1k views

xlog flush request is not satisfied in log files

Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong. ERROR: xlog flush request 6E/...
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 ...
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
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 ...
-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 ...
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? '...
0
votes
1answer
1k views

Postgresql - how to list indexes on a table that is partitioned?

This command works for non-partitioned tables: select * from pg_indexes But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition. ...
13
votes
1answer
28k views

SUM over distinct rows with multiple joins

Schema: CREATE TABLE "items" ( "id" SERIAL NOT NULL PRIMARY KEY, "country" VARCHAR(2) NOT NULL, "created" TIMESTAMP WITH TIME ZONE NOT ...
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 ...
0
votes
1answer
1k views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length) [closed]

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
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 ...
4
votes
3answers
20k views

How to switch master server and warm standby (with streaming replication) in PostgreSQL?

I would like to know what is the way to switch the role of 2 servers when using warm standby? I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
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 ...
5
votes
2answers
7k views

Postgres multiple joins slow query, how to store default child record

Ive got a small database in Postgres, about 10,000 records storing company customers. I have a "slow" performing query (about half a second) which is performed very frequently, and my boss wants me to ...
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 ...
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 ...
2
votes
1answer
8k views

How to assign privileges on a postgresql schema to a user?

pg_hba.conf contains: ... host 10.10.10.10 service_db admin md5 ... Logged in as root, executed the following commands: ###CREATE DB ADMIN sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER '...
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-...
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 ...
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
2 3 4 5
266