7,178 reputation
31740
bio website
location Berlin, Germany
age 39
visits member for 3 years
seen 8 hours ago

8h
comment Keep trailing zeros on Timestamp SELECT
the function that is reading from the database expects to get a timestamp in a given format To me this means that the given function expect these to be text parameters, not timestamps. It would otherwise be content with the timestamp...
18h
comment Postgres 9.0 recovery from inadvertent drop-all-tables event
Before/instead of doing anything on that machine, consult some professionals of saving PostgreSQL databases.
1d
comment Query working in phpMyAdmin but not in Java
Welcome on DBA.SE! I have formatted your post a bit, before posting please check what those buttons above the editor mean :) On the other hand, 'is not working' is not a description of a problem - please be much more specific.
Jan
23
comment PostgreSQL operator uses index but underlying function does not
To me it smells like a bug, it may make sense to visit the bugs mailing list... Temporarily, you may be able to wrap this statement into a function and call it instead of the statement itself.
Jan
23
comment PostgreSQL operator uses index but underlying function does not
stackoverflow.com/a/14786469/246260 about how to get around the limitation of question marks (basically define an other operator name). However, this is really interesting - apparently, the planner is not aware that it can optimize the latter query just as it does with the first one (see cost estimates).
Jan
23
comment Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?
This sounds a really bad idea. Do you have specific problems that you plan to solve this way? It would definitely not decrease complexity. Also, you would lose the ability of creating good indexes, introduce the need of querying for the default data in basically all queries and so on.
Jan
22
comment How to store thousands of properties related to a record in PostgreSQL?
Aaron's post summarizes the possible pitfalls very well. With careful design and planning, this may be a viable solution. It usually has relatively high development costs, as far as I can tell, so using some ready-to-use structures like hstore or jsonb can mean faster delivery. YMMV, as nearly always.
Jan
22
comment Lower() vs ilike on postgresql
It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.
Jan
22
comment How to store thousands of properties related to a record in PostgreSQL?
... or jsonb, if you have typed data (i.e., of non-text type). You can also try EAV, if you are brave.
Jan
21
comment PostgreSQL and array of custom type
ARRAY[('tag', 'some value'), (...)]::contactinfo[] - this should work.
Jan
20
comment Is there any free, usable PostgreSQL formatter on Windows?
To tell the truth, I don't know any really good formatter that handles pl/pgsql code well. As far as I remember, this applies to all non-free tools I ever used, too.
Jan
19
comment Monitoring Amazon RDS with pgtop
@JackDouglas I added two words, is it specific enough now? :) Please note that this seems a real pain point with PostgreSQL.
Jan
13
comment Optimise huge Postgres table for fast read
The first idea is to create a unique index on the data (which you may need anyway), then CLUSTER the table along it. This way it is quite possible that the planner can choose an index only scan to fulfill the search (given that you always search for equality). Also, depending on the size of the actual data, it may be useful to have enough RAM (and setting shared_buffers accordingly) to keep the whole dataset in cache all the time.
Jan
8
comment Postgresql function to create table in other database
@a_horse_with_no_name It is possible using dblink, isn't it?
Jan
7
comment Scaling PostgreSQL TRIGGER(s)
You may find checking PgQ useful, it uses C triggers for registering the data modification events.
Jan
7
comment Is there a way to “SELECT and TRUNCATE” without losing new data?
Yes, that solution will work. The interesting part will be the performance. How many rows do you process (per minute, for example)?
Jan
6
comment Should database be denormalized if I need an index across multiple tables
Well, it is duplicated, yes. The difference is that you don't have to maintain the denormalization logic, just keep refreshing the MV. If the tables are updated from batch jobs, this is probably the easiest way to go.
Jan
6
comment Should database be denormalized if I need an index across multiple tables
If you don't update your tables too often (or can refresh the materialized view often enough), you can create a materialized view from the two tables and create the index on it.
Jan
6
comment Should database be denormalized if I need an index across multiple tables
Are you really looking for exact matches (ie equality) only?
Dec
18
comment Are A or D series VMs better for deploying PostgreSQL on Azure?
@CraigRinger ... and in this case, faster disks (which we don't know here if those are the SSDs or not) give an advantage, especially when much WAL is written, right?