I have to create a service availability using info from this 2 query. First Query. example result second query example result The formula as below ((Reporting Period – Unplanned Outage)/Reporting Period)*100 Answer Consider using conditional aggregation: The availability computation could probably be simplified:
Tag: postgresql
Postgresql – Compare a string with a null value
I’m a bit puzzled because I believe the answer to this question is fairly simple but I’ve searched and tried several options and couldn’t find the right answer. The database is a PostgreSQL 13.1 I am using an API which sends a JSON object to a stored function in the database as follows: The function queries a table with the
How can I use a SET variable concisely in a DROP/CREATE/INSERT statements in PostgreSQL?
I have a template of a migration script that we run with new version when needed. It’s something like: As you can see, the myapp.user value (someuser) is used at three places in the script. Is there a more elegant way to use myapp.user in the INSERT? How can I use myapp.user in the DROP TABLE and the CREATE TABLE
Trigger before insert in PostgreSQL and except some columns
I’m trying to write a trigger for my table. This table has 50 columns and 3 of them are timestamp type. In the future I will insert new rows and they can be duplicate of existing, so I need to compute hash of each row. My idea is to compute row’s hash in each insertion and check it’s existing, that’s
How to assign result of a sql query to a variable and use it into a built in function?
Looking for a solution In Postgresql Answer SQL has no variables, so short of using a function, you can use a common table expression: Or just:
sql get balance at end of year
I have a transactions table for a single year with the amount indicating the debit transaction if the value is negative or credit transaction values are positive. Now in a given month if the number of debit records is less than 3 or if the sum of debits for a month is less than 100 then I want to charge
Query to replace partial values in an array of int in PostgreSQL
In PotgreSQL, how do I write a query to replace some values in an int4 array? {1,2,10} -> {1,2,999} {2,3,10} -> {2,3,999} is what we are aiming for. Answer Seems like you want to replace 10 with 999, you can use array_replace
how to select set of records is ID is present in one of them
Here is the table where ORGID/USERID makes unique combination: I need to select all records (organizations and users) wherever USERID 1 is present. So USERID=1 is present in ORGID 1 and 2 so then select all users for these organizations including user 1 itself, i.e. Is it possible to do it with one SQL query rather than SELECT *.. WHERE
How to perform query on two unrelated tables
I have a table A with Id and Geometry And Temporary table B Each Geometry of Temporary table B has intersecting geometry in Table A. I want to find the intersecting geometry and make a union of the geometry. The final table should be id of Table A which is having intersection and the union of geometry. If there are
SQL: Retrieve missing values from superset
I have a table like this: I want to return a table with all the ratings possible. In this case 1 to 5. So my resulting table will be: I have tried using something like: but this does not work. Can you please suggest a solution for this? Answer You can cross join the list of distinct devices with numbers