7,044 reputation
31640
bio website
location Berlin, Germany
age 39
visits member for 2 years, 11 months
seen 2 days ago

Feb
27
comment Group By days interval (aging type)
And your question is? What do you have already?
Feb
27
comment How to pass a table type with an array field to a function in postgresql
Sorry, the correct one is ave_book((179, 'the art of war', 'fiction', '{190,220}')::book, just as Andriy said.
Feb
27
comment How to pass a table type with an array field to a function in postgresql
Your function looks a bit overcomplicated for me, but anways. An array literal must be enclosed by single quotes, so try the following: ave_book((179,the art of war,fiction,'{190,220}')::book. The constructed row does not need the quotes.
Feb
27
comment Truncate table is taking too long in PostgreSQL
What do you get from SELECT * FROM pg_locks WHERE relation = 'exceptions'::regclass::oid?
Feb
27
comment Can this array group count query be improved?
In the query plan all the row estimates are far off from actual numbers - don't you think a few ANALYZE commands would make things (maybe only slightly) faster?
Feb
26
comment Display user-defined types and their details
This tip with ECHO_HIDDEN is priceless.
Feb
26
comment Postgresql: type_modifier in UDT
@navititious No. A trigger is coupled to a table (or column(s) of a table, no matter what data type those columns are.
Feb
26
comment Output count percentage in 2 decimal places
The error message tells all. What do you expect to be, say, 'RMBS' + 'abs'? Because sum() does exactly this - that is, it does not, because it doesn't know what to do. (Reading for the second time: do you want to get the percentage of 'RMBS' rows of all ('RMBS','abs', 'cmbs', 'clo', 'cdo') rows?)
Feb
26
comment What are the advantages of a database design with single table for MtM relationships?
That Collections table is a terrible idea IMHO. How do you know in your example which ID to look for? Instead, you have to search into a semicolon-separated list, which (slightly depending on your RDBMS) will not perform well. Then how do you keep data integrity? Apart from a rather fat trigger I don't see any other possibility - that won't make insertions and updates fast. I understand that creating many junction tables don't look great at first, but it would solve the above mentioned problems immediately.
Feb
25
comment Faster joining on 'most recent' related rows
Relying on ordering by ID is not a very good practice, I think. In a few similar cases I keep a current boolean column, with new entries always being TRUE, the last current entry set to FALSE at insertion of the new entry.
Feb
25
comment How to solve index missing problem in one table, while other table of same property does not produce any error?
Could you post a diff of your DB settings instead of that huge chunk of code? Would be easier to check that way.
Feb
25
comment Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?
Have you already tried it? :)
Feb
25
comment How and what is the most efficient way to join two tables, retaining a particular field from both?
Is Component1 present in both tables? If yes, how do you identify it in each table? If no, how do you get data3 and data4 for Component1?
Feb
23
comment Why is a new user allowed to create a table?
Regarding the error, it can be easily reproduced by issuing the commands from the question and your REVOKE in order :)
Feb
23
comment Why shouldn't we allow NULLs?
I have NULLs in my database and I still can build nice and performant reports on top of it... At the same time, I don't see how a NULL would cause 'more load on Engine'.
Feb
22
comment Unit testing of stored procedures
Firstly, thank you for your answer - I didn't expect any further advancement on this question... Secondly, I have to admit that you are right about the simple operations: even a two-column INSERT can produce a bug. If it is written so that the column order can be matched against the arguments then it may be OK, but then you are right, again: it is probably better keeping the whole app under the testing regime.
Feb
21
comment How can FLOOR(3) equal 2?
@Andomar have you read the question? How would you calculate the number of digits of 999 with round()?
Feb
21
comment How can FLOOR(3) equal 2?
Illustration and a possible solution: sqlfiddle.com/#!6/d41d8/2739
Feb
21
comment Does PostgreSQL optimize queries in transaction?
There is no such optimization (to my knowledge there is no RDBMS that can do this). So you'll have to roll your own solution.
Feb
21
comment Does MYSQL join before LIMIT?
Let me add that in the case of a JOIN, it must be performed first to get the output rows to be sorted.