Suppose we have an array: With the operator <@ I can query whether the left-operand is a sub-array of the right-operand: The above works greatly but now let’s take the following case: In this case the above will return false as expected. Do we have an operator which says the following: either the lef…
Tag: postgresql
Conditional update with jsonb_set()
I have a table in a Postgres 11.3 database with a jsonb column. Trying to update all objects inside a nested array name “iProps”. If the path {iProps -> value -> rules -> ao -> sc} is an object, then the path should be updated from an object to a string with the value {iProps -> val…
Finding only active items from a column of ‘added’ / ‘removed’
I’m attempting to write a query that tries to find the most recent items. Given a set of parent_service_items find the relevant child service items and return a list of them. For finding just the direct children, I’m using query which simply finds all the associated children. I then use this query…
How to get all data post midnight of different timezone?
I have a PostgreSQL table named testing with a column named creation_time as timestamp with time zone. The database timezone is UTC Now I want to get all rows whose time is greater than 00:00 of the current day as per the timezone “America/New_York”. I know how to get all rows after local midnight…
SQL: Updating 2 different columns in table from CTE condition
Hi I have a temp table with 2 different columns that I am trying to update from a CTE that has a boolean column ‘uninstalled’. As in: I then want to update the table ‘temp_customers’ above from the CTE and their respective columns (where uninstalled = true shall update column ‘ap…
Insert a record into database and associate it to a record in another table without the id
This may be a duplicate question, but I do not know the terminology to use to search for the answer. I have an account table with the following fields: id email And a widget table with the following …
Three-Way Diff in SQL
I have three SQL tables (A, B, and C), representing three different version of a dataset. I want to devise an SQL query whose effect is to extract the ids of rows/tuples whose values are different in all three tables. (Two records are different if there exists a field where the records do not share the same v…
update value only when value changed
I have to update main_table data from another table. Below is statement We will have value in any one column among value_string,value_date in a row or for a key, based on type_key. (like if type_key is string then value_string will have value and value_date is null). There is a trigger which ensure this const…
Compare the sums of 2 separate columns with an SQL Statement
I need help with the following code: Issue with the code: The resultant table contains only NULL in the delay fields. I’m unsure why this happens, as removing ArrivalsDelay and DeparturesDelay from the GROUP BY results in the SELECT failing, as I’ve not included ArrivalsDelay in the GROUP BY which…
Use extra columns in INSERT values list in ON CONFLICT UPDATE
I have an UPSERT query where I want to insert value y value in column b, but if it already exists I want to update it with value z. How can I achieve this? P.S: A simple value list (without select) did not work because we cannot have more columns in values list than we are inserting. Answer In the