I have two tables, a table called companies_display with information about publicly traded companies such as ticker symbol, market cap etc. and a partitioned table stock_prices with the historical stock prices for each company. I want to calculate the beta of each stock and write that into companies_display. …
Tag: postgresql
How can I make these Active Record queries faster?
I’m using Active Record to send a few queries to a postgres database. I have a User model, a Business model and a joiner named UserBiz. The queries I mentioned go through the entire UserBiz collection and then filter out the businesses that match user provided categories and searches. These “work&…
Trying to figure out a sql query to fetch the right data from multiple tables
So I have a database and I have 4 tables (admin, appointment, doctor, patient). I only care about appointment, doctor and patient. Diagram of these 3 tables: What I want is based on a patients amka(int) to find all his appointments and show his name, surname and doctors name, surname. I made this query: The p…
How to get JSONB array index
I’m sure there’s a simple answer to this but I just can’t find it. I need to obtain JSON array elements as rows, but their index is relevant for the subsequent processing. Here’s a [very simplified] example: I need to get: I’ve tried: But the section column is not computed correc…
Insert small dictionary like {1:23, 2:45, 3:17} into a column of SQL database table in Postgres using python language
I have a table having one varchar type column and 2 json type columns, which i created using: I now want to insert values like this using python: For which i wrote a string in python like this to execute: But this doesn’t work. I am getting following error: I searched for similar answers but couln’…
Does row-level security (RLS) apply on a join statement?
Given the following (simplified) tables: If I run the following query to get which posts a user (?) liked: Would the RLS policy for SELECT on the likes table and posts table both be invoked, or would it only apply to the posts table since that is where we are SELECTing FROM? I’m under the assumptiom tha…
Pivot with changed structure of the final table
I have the following table – How do I write a query to produce this output – The output table is a connection table between all the columns in the input table. For example, if the value in column A <> column B in the input table then insert a record in the output table. If a value in column
Using group by with order in postgresql
Query: It yields this: What I need is to show those “fightEventId”s that have more than 2 CONFIRMED fights. Currently all records are ordered in the right way, so the result I expect is: How do I do this? Every time I try, I get errors or the order is broken. As for the DBMS used in the project, i…
how to copy values from a column to a non-existent column in postgresql
i have a table like this: and i want to copy the values from the first column to the non existent column that is ‘qty’ how do i do that if they have different data type? what i originally wanted is when the 1st column’s value is 0, then the column qty should be blank or empty, not [null], i
Sum of two counts from one table with additional data from another table
I have two tables as follows: I want to get the sum of two counts, which are the number of true values in col_a and number of true values in col_b. I want to group that data by user_id. I also want to join Table B and get the name of each user. The result would look like this: So