What I try to do is that bind rows in my PostgreSQL databes in matched columns like rbindlist’s (from data.table) fill argument. In short, the table I’d like to see in my database is like this; I tried it in RPostgres in this way; But it doesn’t work and fields an error because the second ta…
Tag: postgresql
Error: SQL Substring with the “ON” Part of the JOIN
I am using PostgreSQL and must join tables using substring. As I demonstrated below, xyz.MODEL and first three characters of “columnname” from abc Table should match. However, the query below does not work. It returns the error: Can anyone help me to correct this query? Thanks for your help alread…
Mikro-ORM: error: there is no parameter $1
Alright so I’ve been trying to search Mikro-ORM’s docs to find how to pass in native sql query params in but I haven’t been able to find anything. After playing with the code a bit this is what it looks like which I think is sort of right but I’m getting this error My code currently lo…
What do I do to user data that is currently not verified?
Good day, I’m new to backend coding. What do I do to user data that did not confirm verification code from email during registration? Should I save it to database forever or do I have to delete it after a set of limited time when verified boolean is not met? I’m sorry if this is so beginner questi…
Using outer query ID in a subquery
I have a table with a list of invoices with there statuses and reported date, let’s say each date we have new report could change the invoice status from PENDING to PAID. I want subquery the first date an invoice was paid for each invoice. The table looks like this: invoice_id report_date status A1 08-2…
pivot table with multiple value columns in postgres
I need a query which will help me to get data as described below I have a table as followed ID date STATUS TIME09_10 TIME10_11 TIME11_12 TIME12_13 1 2021-09-01 RUN 30 60 45 0 2 2021-09-01 WALK 15 0 o 30 3 2021-09-01 STOP 15 0 15 30 I want this data to be in below format. From the above
How to query this in raw SQL?
I have a table user with 2 columns, name and role. I want to group together for one role all the names which are associated with it. So suppose for the role “admin” is used by 3 different names. I want to start my query with this Answer listagg function should help. need to group the column contai…
Can’t give PRIMARY KEY property in POSTGRES
Why doesn’t this work exactly? The error is syntax error at or near “PRIMARY” Answer The syntax for PostgreSQL is as follows : Try with this: Documentation : https://www.postgresqltutorial.com/postgresql-primary-key/
Return number of rows (streak count) from entries where each entry is the previous days date
I have a table with two columns, userid and date. I want to query this table using a specific userid and date, and from that I want to return the row count of entries going backwards from the entered date. i.e. where userid = 1 AND date = ‘2020-07-09’. It should stop counting if there is a gap bet…
How to pass an array of JSON (or JSON array) to pg function to be INSERTed into a Table?
Function to insert rows of json array into a table: Call this function: or this form: or this form: Always received: Answer A JSON array (json) is different from a Postgres array of JSON values (json[]). vs: The first is an array nested inside a single JSON value, the second is an array of JSON values. Postgr…