I’m trying to update rows in PostgreSQL DB. When I try to run an update query to update values in 2 columns (with no constraints). My table structure is as follows (variable names changed) schema….
Tag: postgresql
Value counts for every field of a table
In PostgresSQL, I am trying to get the value counts of every field and value pair. For illustration, I have 2 columns, but I really have 100 columns. So, I need a programmatic solution. For a table,…
How to use timebucket_gapfill when rows can have null values?
I have a time series table where measurements are recorded into “wide” rows. Rows may contain all measurements or only some. The other columns are then set to NULL. I would like to use timebucket_gapfill() to “clean” this table and make sure that every row in the output has data in all…
How to split time intervals by day in PostgreSQL
I have a query that returns some device state information with device id and timestamp. I am trying to do some daily reports and for that I need to know what state devices were in during the day. So, for example, my query might get me something like this what I want to end up with is What I tried,
Error: ‘duplicate key value violates unique constraint’ even when checking with ‘where not in’
I get the error: ‘duplicate key value violates unique constraint’ even when checking if the primary key is already in the table in which I want to insert. I have tried to check with either ‘where not y.a in…’ as well as with ‘where not exists…’ The code I use ri…
How to register trigger in PostgreSQL?
I have trigger and procedure. I want to be sure that string saved to database will have no ‘-‘ characters. After executing UPDATE interesant SET interesant_nip = ‘555-555-5555’ I get error …
Expand a postgres array of JSON within a single column into multiple records
I have a table called goals where each row contains 1 goal. The data about specific metrics for the goal is stored in ARRAY of JSON called goal_metrics. Sometimes there is 1 metric, sometimes there …
refactor duplication of SELECT inside CASE inside SELECT statement
how to refactor this select statement to remove duplication select ( case when ( select my_product_int_attribute — duplication from app_public.products where …
PostgreSQL CTE UPDATE-FROM query skips rows
2 tables table_1 rows: NOTE: id 2 has two rows ———————– | id | counts | track | ———————– | 1 | 10 | 1 | | 2 | 10 | 2 | | 2 | 10 | 3 | ——–…
LIKE in multiple columns
I have columns title, description, keywords and I want to search a text in them. I’m using PostgreSQL. SELECT title FROM products WHERE ANY(ARRAY(title, description, keywords) like ‘%test%’) I’…