Postgresql – How to run pg_cron job as per local time?
Currently pg_cron works as per UTC/ GMT time only. There is no configuration option available to run job as per local time server time. How to run pg_cron job as per local time?
Postgresql – How to join two tables based on a calculated field?
I have two SQL queries that output the same kind of output and have the same grouping and order : select date_trunc('month', inserted_at)::date as date, count(id) from payment_logs where payment_logs.event_name = 'subscription_created' group by date order by date desc; select…
Using a PostgreSQL function inside a loop
Suppose I have a PostgreSQL function that takes 2 parameters: id (INT), email (TEXT) and can be called like this: SELECT * FROM my_function(101, '[email protected]') I want to run a SELECT query from a table that would return multiple id's:…
Insert into table using data from JSON value in PostgresQL
I am working on a sensitive migration. The scenario is as follows: I have a new table that I need to populate with data There is an existing table, which contains a column (type = json), which contains an array…
Postgresql – SQL update values by comparing it to other entries
so I got Questionnaire Data where the same Questions got asked multiple times, sadly they didnt think of giving them different question_ids, but at least i have the timestamps of when they were answered. Similar to this person_id question_id answer…
Postgresql – Left join on single record
I have the next table that stores events: (simplified structure) ID User Action Timestamp 12 user1 END 2022-01-01 05:00 43 user1 START 2022-01-01 04:00 54 user1 END 2022-01-01 03:00 13 user1 START 2022-01-01 02:00 I need to join 2 events…
Postgresql – Returning a list containing certain parameters from an array of json in PSQL
I am having some trouble figuring out on extracting a parameter (contactId) from a JSON list, and returning the extracted parameters in a list. Data Table Name: users contact ----------------------- 1 { "mainContact": { name: "Timmy Tom", contactId: "11111" phoneNumber:…
How can I get the rows from the PostgreSQL with specific value. Django
How can I get the rows from the PostgreSQL with specific value? I have this table with columns: id | accountid | dictdata | date | Inside the dictdata column contains a bunch of dictionary data. in my models.py class…
Mysql – Creating SQL Tables for a Game's Leaderboard
Newbie here having trouble wiring up some tables for a game's leaderboard. Would appreciate help! I have 3 tables; here's an ER diagram: ┌─────┐ ┌─────┐ │ IPs ├─||─────────────────|<─┤Names│ └──┬──┘ └──┬──┘ │ │ │ │ │ │ │ │ │ │…
Postgresql – Error "more than one row returned by a subquery used as an expression"
I have the following 3 tables: airport airport_id name 1 Frankfurt 2 Paris 3 Amsterdam area areaid name airport_id 1 name1 2 2 name2 2 3 name3 3 4 name4 3 booking id booking_date price commission areaid 1 2022-09-1T10:00 70…