I have the following JSON document stored in a PostgreSQL JSON column: I am trying to retrieve the price for specific customer_ID I am using this query to get the price for Customer_ID=1 The problem is that i am getting the same results for Customer_ID=1 and for Customer_ID=10 I am basically getting both elem…
Tag: postgresql
Best way to create a table with each possible combination of product options and variations
I need to implement in PostgreSQL a table for each combination of option and variation. This is my existing tables: Imagine this scenario: Product: T-Shirt Options: Size, Color Variations: Size:…
Does ALTER SCHEMA NAME affect permission grants to the schema in Redshift
If i update a schema which has been set up with a bunch of permissions to access other schemas with different access rights, will updating the name undo those grants or will they remain in place? Redshift lists the following on their docs as the syntax to run an alter schema, but does not write if the grants …
SQL Collect duplicates to one place? PostgreSQL
Sorry I’m new here and I’m also new with SQL and can’t really explain my problem in the title… So I have a TV show database, and there I have a Genre column, but for a TV show there are multiple Genres stored, so when I’m selecting all my TV Shows how can I combine them? It needs…
Aggregate function not accepted inside WHERE clause
I have the following command. With this PostgreSQL schema. After adding AND day_of_week=3 to WHERE I get the following error. Why cannot I restrict this column? Answer You cannot use a derived column in a where clause. A simple solution is a lateral join: Some advice: Do not use NATURAL JOIN. There is nothing…
PostgreSql : Json Array to Rows using Lateral Join
I have two following JSON Array in details field of my table and need to evaluate the query as I use in another relational table. { “city”: “London”, “name”: “Sainburry”, “quantities”: […
Expanding a JSON array embedded in an object in Postgres 11.7
Postgres 11.7. An upgrade to PG 12 (JSONPath, I know) is in planning stages, not sure when we’ll get there. I’m working on passing some data to a PL/PgSQL stored function, and am struggling to unpack an array embedded in an object. I’m dealing with some client libraries that Really Like Obje…
SQL: Convert integer into formatted hex string
In PostgreSQL, let’s say I have the following table: I want to convert the myInt column into a formatted string that contains the hex representation of the integer in it and rename the column. So it would look something like the following: What would the query look like to convert the integer? Answer Tr…
How to use PostgreSQL column value with some calculations as second column value
I am inserting a huge data set into the SQL table and I want to achieve something like this. The problem is I don’t want to call generate_series function for b and c. I want to take the value of a and perform mod on it for b and c like below or even efficient way but I am unable
Get the latest child messages and also parent messages that are childless
The following is the Message model message_participants has a record for each message and the various people who have sent or received that message. It has a user_id on it. The problem with the above latest_messages_by_participant scope is that it is able to get all the child messages BUT it gets only the las…