Tag: postgresql

ID primary key of ONE to MANY related tables jumping numbers with POSTGRESQL

alembic fastapi postgresql sql sqlalchemy

I am having an issue where the id column of tables users and posts are jumping in increments users and have many notes ==> one to many relationship table users table notes what is happening is the id column of the two tables jump…if there is 3 in id of notes, then next if for users will be 4 how

How to process json data and put it into arrays in postgresql

json postgresql sql

I have a postgresql table with two columns. name VARCHAR(255) and notes JSON. A sample dataset might look like this: Now I want to do two things. put the list from the json at ‘link_to’ into another column called referrals_to (which must be of array VARCHAR type then). From my example: name notes …

Getting values from 3 tables where two of those are self-joined

postgresql sql

Problem Introduction: I’m working on a faker e-banking system as a side project for practicing my SQL skills (which are super bad), and I’m now stuck at a point where I want to pull data from two tables but cannot do that with joins So, basically I have a users table which looks like this: And I h…

Invalid reference to FROM-clause entry for table “mc”

postgresql sql

I’m trying to get an item “manufacturer” with an array of contacts and each contact contains an array of translations using this query but I get this error this is the ER diagram dummy data example: I tried to change the structure of the query to solve it but I wasn’t able to fix it. S…

Performance impact of view on aggregate function vs result set limiting

performance postgresql postgresql-performance sql sql-execution-plan

The problem Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute. Here’s a sample setup: What I found out I’m executing two statements which result in completely different execution plans and run…

Split single JSON array column and update multiple integer columns with this data

postgresql sql

I have a table with a JSON array column containing an integer of every hour of the day, e.g.: In the same table I have added 24 new integer columns named i0 till i23 so I can easier filter on each individual column without complicated JSON queries. With which SQL query can I update the 24 columns with the val…

Postgresql how to change row to column

postgresql sql unpivot

lets say i have a table like this: and i want to create a table like this with query what query should i type to show a table like above? using colpivot or crosstab? Answer You can try to use UNION ALL Query 1: Results:

How to write DELETE Statement with Inner Query in PostgreSQL?

postgresql sql

The below query works fine in Oracle DB. The same query fails in Postgres DB, but the inner query works fine in Postgres DB. I am getting syntax error for this. Error :- [Code: 0, SQL State: 42601] ERROR: syntax error at or near “(” Position: 13 [Script position: 3109 – 3110] How to fix this…

Postgres: Split column values & transpose

postgresql split sql

I have a table like this: And I want to convert it to this: I have tried splitting the column based on ; but the product_id count varies based on the category_id. Is there anyway I can achieve this? Answer You can use PostgreSQL’s array manipulation functions: string_to_array does exactly what it says —…