I have a .net core application using dapper with Postgresql. Windows, PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit When i do simple calculation in the select-statement, then i get different results if i do not provide fractions: 1258 1208 What’s the reason and what’s the best way to get correct numeric result? Answer Postgres, as many other databases
Tag: postgresql
Create a PostgreSQL view from a unique list of exploded data
When I make this request: I get results like this: Each line has values separated with / (space, slash, space). How to create a view with a list of unique values with count ? Answer You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate: Demo on DB Fiddle: val | cnt :—- | –:
Postgres, split single row into multiple rows
I’ve got a table with columns like: I want to return: I ended out writing a large query using 3 unions like this, but this does not seem right: Is there a way to do this without the mess? Seems like I’m missing something really obvious. Answer In Postgres, you can efficiently unpivot the columns to rows with a lateral
Postgresql: how to calculate the percentage correctly?
With this very simple query I obtain the count(total) and genre of movies from my db. select genre,count(*) from titles group by genre order by count desc; genre | count —————–+-…
Get the logs of the current week in postgresql
I need to get the logs of the current week in postgresql, I have tried to use the native mysql functions yearweek for that, but here in postgresql it is different This is my table CREATE TABLE …
Sql assign unique key to groups having particular pattern
Hi I was trying to group data based on a particular pattern. I have a table with two column as below, I need to generate a key column that increment only after rollingsum equals 0 is encountered.As given below I am using postgres, I tried to increment variable in case statement as below But I am getting an error near
Calculate a column value backwards over a series of previous rows/RECURSIVE/CONNECTED BY
need your help. I guess/hope there is a function for that. I found “CONNECT DBY” and “WITH RECURSIVE AS …” but it doesn’t seem to solve my problem. GIVEN TABLES: Table A +——+-…
SQL – select where for specific id, all distinct values for other column exist
I am having a bit of a hard time explaining the logic I want to achieve in the title. Anyway, I have a database containing two tables (rent and car). The car table contains data about cars (license …
How to average/aggregate the result off a SQL query?
I have a table with many rows each belonging to different users. I first want to select all rows the belong to one user and then on each row add the average for that a certain column but can’t make it work. I’m would like the result to display the average on each row for the user but it’s currently
PostgreSQL, summing multiple date columns over multiple rows
Let’s assume the following table: CREATE TABLE assumption ( datetime_1 TIMESTAMP, datetime_2 TIMESTAMP, datetime_3 TIMESTAMP); Now I want to know the total amount of times a month is set in any row …