How to create a pivot table in PostgreSQL
I am looking to essentially create a pivot view using PostgreSQL, such that the table below: Column A Column B Happy Sad Sad Happy Happy Sad becomes Count Column A Column B Happy 2 1 Sad 1 2 I've been…
Error Installing AGE from Source Code on Mac – Postgresql
I am trying to install AGE on my system running macOS 12.6.3 using this guide. I am getting an error on the step I try to make PG_CONFIG=/path/to/postgres/bin/pg_config install and am unsure how to get it fixed. Here are the…
Debugging an Out of Memory Error in Node.js – Postgresql
I'm currently working on a Node.js project and my server keeps running out of memory. It has happened 4 times in the last 2 weeks, usually after about 10,000 requests. This project is live and has real users. I am…
PostgreSQL Upsert With Multiple Constraints
I'm trying to do an upsert on a table with two constraints. One is that the column a is unique, the other is that the columns b, c, d and e are unique together. What I don't want is that…
Remove space between number and character – PostgreSQL/REGEXP_REPLACE
I have a table with medication_product_amount column where there are spaces between numbers and characteres like below: medication_product_amount 1 UN DE 50 ML 20 UN 1 UN DE 600 G What I want is to remove the single space ONLY…
How to lag over month and over year at the same time? – Postgresql
I am attempting to find percentage change per each month per company. Table Year Mon company_id Revenue 2018 2018-06-01 42 2000 2018 2018-07-01 42 3000 2019 2019-06-01 42 4000 2019 2019-07-01 42 9000 I attempted this and failed. select *,…
Restrict string_agg order by in postgres – Postgresql
While working with postgres db, I came across a situation where I will have to display column names based on their ids stored in a table with comma separated. Here is a sample: table1 name: labelprint id field_id 1 1,2…
Can't filter empty fields in Django – Postgresql
I want to get a list of users that have provided an email in my database using Django. This is my query: list(App.objects.exclude(admins__email__exact='').values('admins__email')) which strangely excludes everything and gives: [] although when I run: list(App.objects.all().values('admins__email')) i can see a list…
SQL – % split between sale types – Postgresql
I need some SQL help. I have a table where I'm trying to get the % split between online sales and POS sales. Below is the query I have written and the screenshot is what I get, but I don't…
One to one relationship Error on Postgresql
I would like to seek your insights regarding the error I'm encountering with my postgresql commands. Basically, what I want to achieve is to create a "booking" entity with one to one relationship to another table called "booking details". But…