Here is the code: As you can see the recursive code gives the result in right order, but the non-recursive code does not. They both use union, why the difference? Answer Basically, your query is incorrect to begin with. Use UNION ALL, not UNION or you would incorrectly remove duplicate entries. (There is noth…
Tag: postgresql
Postgresql: In-Row vs Out-of-Row for text/varchar
Two part question: What is Postgresql behavior for storing text/varchars in-row vs out-of-row? Am I correct in thinking that with default settings, all columns will always be stored in-row until the …
Storing ‘Rank’ for Contests in Postgres
I’m trying to determine if there a “low cost” optimization for the following query. We’ve implemented a system whereby ‘tickets’ earn ‘points’ and thus can be ranked. In order to support analytical type of queries, we store the rank of every ticket (tickets can …
How to use ANY instead of IN in a WHERE clause?
I used to have a query like in Rails: Which generates sql query like: Now I want to change this to use ANY instead of IN. I created this: Now when I use empty array ids = [] I get the folowing error: Answer There are two variants of IN expressions: expression IN (subquery) expression IN (value [, …]) Si…
Return first element in array_agg()
I am writing a query to get all players for all teams. Instead of looping in the application, I decided to get the players of all teams in a single query using array_agg(). I have written the query as follows: This query gives me the result as below, in the result set the teamname is being repeated (exactly t…
Can’t extract string from database sql
My aim is to test if the user and password inserted, existed in Table1. However, if I typed (pink,floyd) which exists in the database count still null and it appears the message “user doesn’t exist”. Here’s my database : Answer It turns that the stocked user and pass parameters in Tabl…
Writing my own aggregate function in postgresql
I’ve never wriiten my own aggreagtes, only store procedures and I need some advice. I want to write a custom aggregate which is going to return maximum value of the integer rows and incerement it by 10. How can I do that? I tried this: but it didn’t work. Can someone help me out? I got the error: …
SQL: error when creating a foreign table that has an enum column
I’m creating a foreign table (foo_table) in database_a. foo_table lives in database_b. foo_table has an enum (bar_type) as one of its columns. Because this enum is in database_b, the creation of the foreign table fails in database_a. database_a doesn’t understand the column type. Running the follo…
Is there a way to have a placeholder when select returns no rows?
I have a table, report_total, that contains calculated values for some of the total_types_cd (codes) but not necessarily all of them. If there is no corresponding row, I’d like to have a …
How to calculate MAX(SUM()) in postgresql in particular to query below
I am trying to get MAX(SUM()) in PostgreSQL, which is not possible directly in PostgreSQL. I am trying to get the result by any method, but unable to get the desired result. My table is like this – …