I have a problem with a constraint that is being violated by some rows when restoring my DB, but has never been violated at usage. I have 3 tables : some_table here is another table, which I think is not relevant for this problem. Now what I want is to have a unicity constraint between as.id and bs.some_id th…
Tag: postgresql
Syntax error while using WHERE clause in PostgreSQL
I’m trying to run some queries related to the University database in PostgreSQL. To find students advised by instructors from different departments, I used- However im getting the following error: ERROR: syntax error at or near “where” LINE 3: student join instructor where student.dept_name …
How to group consecutive rows with same values in a result table into groups with date_from and date_until
I have a simple database table (Postgres) consisting of 3 attributes (f1,f2 and f3) ordered by date. (dbfiddle). I want to transform (group?) the result into the following table: f1 f2 f3 …
speed up the query which is taking 2 seconds on 1000000 on active records also on PostgreSQL Sql
I’m creating a dynamic query on the server side beside the parameter. However, my query is taking 2 seconds to fetch the records. I’m passing the query through active records let me share the query and Active record rails code SELECT (custom_attribute_values.attributable_id) FROM custom_attribute_…
Create date in format YYYY-MM and add 0 in case month < 10
I want to create a date in the format YYYY-MM using postgresSQL. The month should always be two digits. For example if it is August 2020 the result should be 2020-08 and not 2020-8. Therefore, I …
1:N relationship to profile or user table?
Suppose I have a User Table and a UserProfile Table that I have separated. The two tables have a 1:1 relationship. User table contains only data like email and password, while UserProfile contains …
SQL – WITH RECURSIVE doesn’t work, when there is another query before
I have a (postgresql) query, that goes like This works perfectly fine. But when I reorder the with queries to that it suddenly shows a syntax error. This behaviour doesn’t occur, when I have standard non-recursive queries. With non-recursive queries, I can order them, as they please me. Why does it do t…
Count number of students per one role SQL
I have a table students with student id, course id and role number looking like this (ordered by student_id): How to count number of students for each role? If student is enrolled in multiple courses with the same role number, then count this as one. The expected output for above table would be similar to thi…
Set minutes of timestamp to a specific value
What is the easiest way to set the exact minutes value of a timestamp? This only adds minutes instead of setting the exact value: Answer Use date_trunc() before you add 2 minutes: Or, to retain seconds and sub-seconds: Demo: Should be substantially faster than manipulating the text representation and casting …
Find students who take most courses SQL
Assume there’s a table students containing student id, semester id and number of courses taken in each semester: How can I find students in each semester who took the largest number of courses? Expected output would be: I thought of using OVER PARTITION BY, but I’m not sure how to use it correctly…