Replace NULL values per partition

I want to fill NULL values in device column for each session_id with an associated non-NULL value. How can I achieve that? Here is the sample data: +————+——-+———+ | session_id | step …

Postgresql – access/use joined subquery result in another joined subquery

I have a database with tables for equipment we service (table e, field e_id) contracts on the equipment (table c, fields c_id, e_id, c_start, c_end) maintenance we have performed in the past (table m,…

Regexp search SQL query fields

I have a repository of SQL queries and I want to understand which queries use certain tables or fields. Let’s say I want to understand what queries use the email field, how can I write it? Example SQL …

PostgreSQL array overlap with the ALL construct

I want to achieve the same behavior as in next example: select array[1, 3] && array[1] and array[1, 3] && array[2] using the ALL construct like so: select array[1, 3] && all(…

Django complex partitioning & ordering for tree Table

I’m using Django with PostgreSQL as my Backend-Stack for an application of which the main feature is a complex, multilevel table displaying annotated time-series-data for different products. So …

Should the column used to order results be included in the index of a postgresql table?

I am creating indexes for a PostgreSQL database. I would like to know whether the column(s) used to order results in a PostgreSQL statement should be included in the index. Assume I have created a …

Count the amount of likes a user received on each post sql

I have two tables. Likes table which contains it’s like_id (PK), vole_id, user_id, and created_at timestamp. And another table called voles that contain vole_id (PK), and user_id, as well as a bunch …

ERROR: invalid input syntax for type numeric (SQL)

I’ve created a data set with Mockaroo and then added the file with cmd. But I am getting this error. ERROR: invalid input syntax for type numeric: The table definiton is this : create table car( id …

Obtaining summary result since last occurence of specifc hour of day in postgresql

I have rainfall measurements stored in a postgresql table and wish to select the min and and max value since the last occurence of 9am, whether that is 9am of the current day, if after 9am, or 9am of …

Postgres comparing strings from json and table

I have the following postgres stored function: CREATE OR REPLACE FUNCTION wg.get_user( req jsonb) RETURNS jsonb LANGUAGE ‘plpgsql’ COST 100 VOLATILE AS $BODY$ DECLARE …