With postgres I’m trying to get a single user and all their posts, but when I inner join the Users and Posts tables I receive this: QUERY: I’m also trying to do the same thing but as a json QUERY: I know why but I don’t know how to avoid it, can someone help me to get something like this:
Tag: postgresql
Why does Postgresql function not work as expected
I have table like this I create get function like this I tried to call function like this but return json all 3 row instead of 1 row with booking_id 1467 can you explain root cause ? Answer The root cause of you problem is naming your parameter (booking_id) the same as a column name (booking_id) in your table…
How to fix cache lookup failure/corrupted database?
I’ve got a postgres database that I’m trying to clean up with drop schema public cascade. The data on it is not that important and I never made any backups. I’m just trying to rebuild it. However, it seems an error I made earlier is causing the drop command to fail. When I run drop schema pu…
select subset of rows
I have the following table I want to write a query that ignores the rows with the latest DATE for each NAME No idea how to write a query like that…. Answer You can use rank() to rank the rows by descending DATE, then filter out rows with rank = 1 : Fiddle
How to convert this string into timestamp in postgresql?
I want to convert a string into timestamp I am using the following command for it However it is returning me ‘2023-07-17 23:38:02’ this instead of ‘2021-31-12 23:38:02’ Answer Place of month & day in the input string doesn’t match format string. Use: As pointed out in the com…
Finding the immediate upper and lower of X
For a project, I have a table nearest_values as id value 1 8450 2 8500 3 8550 4 8600 5 8650 6 8700 Given a value say 8555, I need to select rows 2 and 3 ie immediately below and above as below. id value 2 8500 3 8550 another example for say value 8601 the selected rows should be
SQL Function has no destination when called
I’m getting a success when creating the below function, but when I call it I receive an error stating there is no destination. Answer Language SQL: https://www.postgresql.org/docs/current/xfunc-sql.html language plpgsql: https://www.postgresql.org/docs/current/plpgsql.html Language sql is more easier to…
Postgresql sum not working as expected when it is clear
I am solving the following Hard Leetcode SQL Question. Link to Question: https://leetcode.com/problems/trips-and-users/ (You can directly look at the solution and understand the problem) Question: Trips table: Users table: Output: Here’s my code: And the output is: How is the cancellation_rate is 0.00 w…
How can I write a Postgres (SQL) query for FIFO ‘closing stock’ inventory valuation?
Background I need to implement inventory valuation / costing using the FIFO (first-in, first-out) method. I’m running Postgres 11 running on CentOS 7. I’ve looked at, and tried, a fair number of hypotheses from SO and the wider internet (as well as searching my own print library which includes SQL…
How to separate column values by condition (pivot) to fill one row
I have two tables that I’d like do a full outer join where the resulting view separates the values table into two separate columns with one row for each name_id. I have made one approach with a CASE expression to select by type and then use it with pandas to fill in the values and return distinct name_i…