I have a procedure with an int parameter. The syntax for the add_retention_policy function is add_retention_policy(‘hypertable’, INTERVAL ‘x days’, true). I want to prefix the hypertable with the schema which is always ‘schema_’ and then followed by the id parameter, how do…
Tag: postgresql
Consider two left join with same table SQL
I have this query I need to consider procedimentos when faturamento_lancamentos_bpa_adicionais.procedimento_id and faturamento_lancamentos_bpa.procedimento_id Answer You need to alias the “doubled” tables, as every entity needs a unique name The use of meaningful aliases will keep also the query b…
How to apply this query to all the employees
I have a query to compute the 3rd highest salary for an employee as shown below. How can I apply this query to give the 3rd highest salary for each employee which can be fetched by the query Note: without using special functions like dense_rank() Answer You are looking for each employee’s third highest …
Save few values to the row in Postgres [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 3 months ago. Improve this question Is it possible to save few values to the row. For example, I have 2 tables: For Participant…
PostgresSQL: Get first and last values of an entities chain (self referential table)
I have a table where entities are events. Each event belongs to a group. Each time a new event for this group is inserted, it refers last event of same group on the attibute field “prior” (self referential FK). The other attributes are the id of the event itself, the group which the event belongs …
PostgreSQL find the sum of two queries from different tables
I have two queries that returns the total number of issuances and redemptions from two different tables. This one returns issuances, around 18k This one returns redemptions, around 7k I need the sum of them, I came up with this but this only returns 2, counting it wrong What should I do? Answer
Python SqlAlchemy adding escape characters to json string while inserting in postgresql json column, so queries not working
I am adding JSON string to Postgres jsonb column in python using the sqlalchemy library. Code to create json string is Code to add to postgresql database is: My JSON is printed properly in logs But when it goes to database the json is enclosed in a string and an escape character is added in front of every dou…
SQL query to aggregate result of a column comparison
I have a SQL (Postgres) data structure that represents games for a group of matches. Table below shows that there have been 3 matches played (each consisting of either 2 or 3 games). match_id player_one player_two p1_game_result p2_game_result 1 player1 player2 12 10 1 player1 player2 3 11 1 player1 player2 5…
How to get the cumulative sum of children up its parents?
So I have written a query to get the cumulative sum of children but I think partition sum has error as its totalling for the parent that is not part of the children. My fiddle is http://sqlfiddle.com/#!15/88828/1 I have dont a running total but thats wrong. I want the siblings total to a child and child total…
How to group by the amount of values in an array in postgresql
I have a posts table with few columns including a liked_by column which’s type is an int array. As I can’t post the table here I’ll post a single post’s JSON structure which comes as below I’m trying to send posts in the order they are liked (Most Liked Posts). Which should order…