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
1answer
27 views

How can I trigger an error on transaction commit?

I develop a library that implements transactions in Postgresql, and at my job we had a weird bug in production with it, when transaction failed not on one of the queries itself, but on COMMIT, because ...
1
vote
2answers
335 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
18 views

postgresql vacuum analyze on restored snapshot

Frequently we restore a Postgres DB from a RDS snapshot to an entirely new RDS instance. We notice that queries on certain tables perform really slowly but improve by a huge magnitude after running ...
9
votes
2answers
3k views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
18
votes
1answer
14k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
0
votes
1answer
44 views

Postgres foreign key on delete cascade deletes all but 1, why?

I am using postgres v and I am trying to get a multi table cascading delete to work. However, when I do it, 1 table/entry doesn't get removed and I can't for the life of me understand why. Here is my ...
4
votes
2answers
236 views

Transaction strategy for UNIQUE constraint?

How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day? It was suggested to me that it will different for every isolation level. Could ...
0
votes
1answer
29 views

Error: relation doesn't exist on self referencing check constraint when restoring database

I'm trying to take a database dump from a PostgreSQL 9.4 database and restore it into a 9.6 database. It works, but in the error output I see the following error: ERROR: relation "my_tbl" ...
0
votes
1answer
3k views

pg_upgrade issue from postgres 9.3 to 9.6

I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6. I have both binaries on the windows server R8. Here are the 2 pg_hba.conf : > 9.3 : # TYPE DATABASE USER ...
0
votes
1answer
34 views

How to use ORDER BY and LIMIT inside JSON_AGG

I have a query which returns a desired output. SELECT shop, JSON_AGG(item_history.* ORDER BY created_date DESC) as data FROM item_history GROUP BY shop; Results: [ { "shop&...
0
votes
2answers
337 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
0
votes
1answer
108 views

Units of measurement in the database

I have a table of products that can be measured by weight and size, and the user can enter which unit he wants to use for the measurement of any product. In the future he might need to see these ...
0
votes
1answer
288 views

PostgreSQL's PGP_SYM_ENCRYPT log security

In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with ...
13
votes
2answers
99k views

Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and I want to update the ...
0
votes
2answers
32 views

How to “succinctly” detect changed values in the PostgreSQL's upsert (on conflict) where clause while supporting null changes?

We maintain a large data warehouse implemented in PostgreSQL and python. One very common pattern we do is to make upserts, and log when things were updated. We have some unique key my_key and values, ...
0
votes
1answer
593 views

Merge jsonb objects and sum their values over multiple rows

I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used ...
0
votes
1answer
374 views

Pgpool - 3.5 configuration

[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING: could not open configuration file: "pgpool.conf" 2017-06-20 11:34:41: pid 103313: DETAIL: using default configuration ...
1
vote
0answers
90 views

SQL statements to ensure that the same location cannot be reserved more than once on the same day?

I have two tables one for venues (3 locations hall1, hall2 and hall3) and one for orders (a row could be 441,2004,50895,'Requested','2021-01-02 10:53','2021-03-06 11:46',7,3) as follow: CREATE TABLE ...
0
votes
2answers
39 views

In PostgreSQL, how can I make Bitmap Index Scan parallelized?

I want to improve the performance of an SQL statement. I am on version 13. Here are the sample codes and the query I am interested in. drop table ords; CREATE TABLE ords ( ORD_ID INT NOT NULL, CUST_ID ...
4
votes
2answers
526 views

Pragmatic approach to k-means clusters with PostgreSQL

I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone. While I am fully aware that this method may not yield meaningful ...
0
votes
1answer
573 views

Use ENUM in array of objects

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique? Data examples: ...
1
vote
1answer
737 views

PostgreSQL WAL file database system identifier wrong

I'm having a problem restoring a copy of a PostgreSQL database to another server. I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive ...
0
votes
0answers
139 views

postgres performance tuning - what to try next?

I have a simple (but large) postgres database. I have a table defined like this…. CREATE TABLE TABLE_A ( MessID SERIAL UNIQUE, rawData CHAR(250), loadedTime TIMESTAMP)...
1
vote
1answer
77 views

Error during plperl compilation: quoted execution trapped by operation mask

When I try to create a perl function, I get an error. CREATE OR REPLACE FUNCTION foo() RETURNS text AS $BODY$ use v5.28; use warnings; my $output = `/data/myapp`; # or qx('/data/myapp') return ...
1
vote
1answer
27 views

Calculate days between to dates postgresql

I'm having a problem: I'm trying to calculate days between two dates. Let's say I have the date '2005-05-25 06:04:08' and I want to count how many days are from that day to the current date. I tried ...
2
votes
1answer
115 views

How to use ts_headline with multiple languages

I am using full-text search against a single table column. The data in the column could be of any language with no way to determine the language ahead of time. Assuming I only care about some subset ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
2
votes
1answer
1k views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
0
votes
1answer
627 views

Difficulty understanding Postgres synchronous_commit = off and data loss

I am confused what "data loss" means the context of synchronous_commit = off. It sounds like synchronous_commit = off is pretty safe to increase write perf, and may result in "data loss", but not "...
0
votes
1answer
1k views

PostgreSQL stop streamig replication

I've setup streaming replication between a master and a backup server. Now I want to stop the replication but I've noticed that every time I stop postgres in the backup server some old WALs files stay ...
0
votes
1answer
12 views

pg_dump dumps data from dblinks?

I'm working with a postgres database recovered from a dump, and there are some empty tables and foreign keys with null values. I'm suspecting the dump could be incomplete, because original database ...
0
votes
2answers
35 views

Postgresql split single row into multiple rows based on date [duplicate]

In my case, we have a table ABC with the supposed 1 row. id name start_date end_date 1 Rahul 01/01/2021 06/01/2021 but while retrieving I would like to get multiple rows where the start date is ...
1350
votes
7answers
2.7m views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
1
vote
1answer
30 views

How does PostgreSQL handle parallel transactions with different transaction isolation levels?

I have a typical Java Spring + Postgres environment (the project is legacy). A persistence layer of backend has a mix of declared isolation levels - some of them are the default i.e. READ COMMITED, ...
0
votes
1answer
19 views

Partitioned table not optimizing the query of ranges of timecols

I have a table for statuses of lots of things on PostgreSQL13. Because of the number of objects I am keeping tabs on I decided to partition my table into years. According to the pgconf-eu-2019 ...
2
votes
1answer
398 views

Hashtags and synonyms table design

So what I'm trying to do is setup a tag and synonyms structure. Where you can assign a tag as a synonym to another tag. E.g for tag: photographer I'd want to add 'photography, photographers, photo' ...
0
votes
0answers
26 views

Is there any way I can optimize this merge ranges function?

I have this this function which takes in an array of tsranges, and convert them into one array with tsranges, where each range contains all overlapping ranges from the input. Version: 12 https://...
8
votes
2answers
21k views

How do I install/enable the uuid-ossp extension on Postgres 9.3?

I’m using Postgres 9.3 on Ubuntu Linux 14.04. How do I install/enable the uuid-ossp extension? I first verified the contrib package was installed … root@prodbox:/home/rails/myproject# apt-get ...
0
votes
1answer
32 views

Split a comma-separated record into its own rows

I have the following table, named stores: store_id INT emails VARCHAR That contains values: store_id emails 1 user_1@example.com,user2@example.com 2 uswe3@example.com,user4@example.com 4 admin@...
0
votes
0answers
15 views

How to case text array elements automatically?

According to PostgreSQL documentation on the ARRAY constructor: By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE ...
1
vote
1answer
18 views

How to keep array fields in PostgreSQL jsonb

It seems that PostgreSQL (12+) cannot keep the PostgreSQL array type in jsonb objects. If a PostgreSQL array is stored in a jsonb, it seems that it's converted to a jsonb array. For example, given the ...
0
votes
1answer
45 views

PostgreSQL UPSERT issue with NOT NULL columns

I want o know if it's possible to do an upsert when one of the columns (column1 or column2) is null, I just need a partial update, and of course, I'm trying to avoid a SELECT to verify if the record ...
1
vote
1answer
753 views

How to access PostgreSQL Functions from Oracle through DBLink?

I'm having two DB server (PostgreSQL, Oracle). I'm able to access the table in PostgreSQL from Oracle through DB-Link. I've use the below query to access the table. select "Cust_Id", "Mobile_No" ...
6
votes
4answers
8k views

Using Postgresql logical replication, how do you know that the subscriber is caught up?

Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are: pg_stat_replication ...
1
vote
1answer
617 views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
0
votes
2answers
526 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
1
vote
1answer
17 views

Can I make postgres' pg_dump condense the alter table statements in the create table section?

If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this: CREATE TABLE public.account ( id uuid NOT NULL, db_version ...
0
votes
0answers
18 views

Insert checking in another table if value exists

My problem is the following: I have 2 Tables T1(idName) and T2(idName, name). I need to do an Insert in T1 with only the name and that the query compares if the name exists in T2 and get the idName to ...
0
votes
1answer
163 views

Insert multiple rows into a table with id from other table if not exists insert to other table

I have done similar task where I can insert a row into a table if data doesn't exists: WITH user_exists AS ( Select id from users where username='%s' ), user_new ...
5
votes
3answers
136 views

Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many

We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...