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

Nov
6
comment Best design to handle multiple query keys
So, could you please clarify why do you want only one table? That would possibly save us a lot of unnecessary discussion.
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ your approach is simply not one for an RDBMS. That also means that you can do it, if you really want, but most probably you'll get an awful performance.
Nov
6
comment Assign to NEW by key in a Postgres trigger
I haven't thought about hstore, it's much simpler than my approach.
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ Yes, there is, it's called normalization ;)
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ hm, there is no accepted answer there. Also, I think if you cannot normalize your data, PostgreSQL is not the right platform to do this.
Nov
6
comment Optimizing SELECT queries
Well, your table and the index on tag are very close in their size. Postgres simply decided that the sequential scan would be the cheaper. In the aggregate case, it's no wonder that aggregating 100M+ rows takes time. At the same time, it would be interesting to see the EXPLAIN (ANALYZE, BUFFERS) output to see the ratio between pages read from disk and pages cached.
Nov
5
comment sorting by partial key name in json column
I'd store the integer part separately. Then you may be able to have a slightly different JSON structure that can be searched to your liking.
Nov
5
comment Deadlock detected on CREATE TABLE in PostgreSQL
@khorvat you should probably flag your post on SO for migration to here, then the two posts will be merged and all collected information seen in one place.
Nov
5
comment Deadlock detected on CREATE TABLE in PostgreSQL
May be related (and unanswered, too): dba.stackexchange.com/questions/66445/…
Nov
5
comment modeling data dimensions as columns - some dimensions are highly variable while others have low variability
Please add here the main points of the document you linked. This could make your answer a good/excellent one.
Nov
3
comment Order specific columns in json_agg
'Does not work' means exactly what? Is it an error, unexpected output, unexpected pizza delivery or something other? My guess is you get ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list - meaning that the expression for ORDER BY must be (colA, colB) (omitted the ASC as noise).
Nov
3
comment PostgreSQL: schema diff/patch tool
There is no problem with resurrecting old questions (there are even badges to achieve from that). And a question: is it possible to compare whole databases (I mean, at least, all tables in them)?
Nov
1
comment Postgresql 9.3: Add Unique constraint using an existing unique index
I tend not to add a unique constraint at all. This way, it is somewhat easier to solve issues with bloated indexes on heavily used tables.
Oct
30
comment Slow index scans in large table (PostgreSQL 9.2)
If you refer by 'the cost estimates seem to be a factor 4x larger' to the fact that the cost numbers are about 4 times those of the actual time, then please notice that the two has nothing to do with each other. The cost is only an estimate, helping the query optimizer to choose the best looking plan. Outside of this context, it is usually a meaningless value.
Oct
30
comment Postgresql and UTF8 to Latin1 conversion?
Yeah, this used to be (or maybe still is) a problem for Hungarian texts. For a time, you could see many websites with the same misinterpreted characters (well, the browsers were also confused...) Once I could relatively easily read all these ä and similar vowels :)
Oct
30
comment Different kinds of pricing PostgreSQL
Well, 'while stocks last' is definitely not a price, so you are right, it would be silly to store these texts as a price. It would be useful to describe the use case a bit more. Until that, my guess is that you have only one of these text per product at any given time - you can just store them in a separate table and add a new column to the pricing that references these. As the pricing usually keeps changing, it's usually a good idea to separate it from the basic product data.
Oct
28
comment Insert New Foreign Key Row for new Strings
Yeah, that's true. What this shows is that a CREATE TABLE would be much more understandable...
Oct
28
comment Insert New Foreign Key Row for new Strings
Just add the referenced value in the same transaction? Also, your example is not very clear. We don't know what sort of type someval_str is, but it is certainly not an integer. Probably some actual CREATE TABLE statements would help.
Oct
27
comment Why does a select statement dirty cache bufferes in postgres
Your blog is a source of really valuable information. At the same time, please add the main points of your post there to this answer, so that it complies with the rules here.
Oct
24
comment How to import large sql file in postgres?
So, what is the exact error message? Please add it to your post.