In a PostgreSQL database, I’ve got a table payment with a column payment_date of type timestamp. My goal is to count payments made on Monday. The following query: gives result such as: However when I try to count Mondays like this: the result is 0 even though the previous query shows it should be greater than 0. What’s wrong about
Tag: postgresql
Select pairs of values based on condition in other column – PostgreSQL
I’ve been trying to solve an issue for the past couple of days, but couldn’t figure out what the solution would be… I have a table as the following: +——–+———–+——-+ | ShopID | …
Finding records older than a minute
I have a users table which consists of some columns like name,age,created_at created_at contains time in unix timestamps format (epoch). I basically want to create a query to show all queries that …
I want to group time interval based on given tolerance in postgres sql
create table sample(id integer, name varchar(100), timeint time); insert into sample values(1, ‘aaa’, ’00:00:01′); insert into sample values(1, ‘aaa’, ’00:00:01′); insert into sample values(1, ‘aaa’, …
How do I get IDs associated with the most frequent value in PostgreSQL?
I have following data in my PostgreSQL 11 database count_tbl (Windows 10 x64 machine). The following query, for each group (grp), finds the most frequent value: The output of above query is: Now, I would like to associate the most frequent value to the corresponding IDs (for example, for grp = 1, most frequent value 19.7 have ids 1, 2,
Optimizing Queries while being limited on the number of Indexes I can use
I have a query that involves a join on three tables. Let these tables be A, B and C. A and C have a primary key and they are joined through an equi-join to B using 2 foreign keys that B has. The optimal indexing strategy would be to use 4 indexes on all 4 keys. However I am constrained
Why does the id serial primary key keep changing?
I have just started a full stack web developer course which includes PostgreSQL. I have been give some practice questions to do and when I clicked on run SQL it displays the id, first_name and last_name but when I entered in more lines of code to answer more questions and clicked on run SQL again, the id number changed to
Perform UNNEST, INNER JOIN and then ARRAY_AGG as part of an UPDATE query
I am trying to unnest an array from one table using ORDINALITY to preserve order, then perform an INNER JOIN on another table to find the corresponding value from a specific column and then use ARRAY_AGG to package this back up and UPDATE the original table. I have something working for a single query, but I want to do an
Postgres order entries by id, self reference id and date, after every parent, list childs
I have the table orders with the following fields id, updated_at, self_ref_id, and others which doesn’t count. The column self_ref_id is a self reference two an order, one order can have multiple children, a child will not have other children. I am trying to order all entries by updated_at desc but after every parent order, I want to have his
Sort by NULL field only if field is NULL, then sort by name in Postgresql
I have the following sort in Postgresql: What I want is having all records with verified_at NULLS first, but only if the field verified_at is not null, then order by name. I don’t want the verified_at to have the priority on name if verified_at IS NOT NULL. With this code, verified_at has the priority on name, after the NULLs are