13,917 reputation
12253
bio website
location Munich, Bavaria
age
visits member for 3 years, 10 months
seen 27 mins ago

May
7
comment Upgrading using pg_upgrade on windows fails
Just use a regular user but make sure that user can write to both data directories (the new and the old one).
May
7
comment PL/pgSQL fuction to find circular references
It's quite easy to prevent the infinite loop for a recursive query, but I have not found a way to actually mark a row as having a cycle (because the JOIN condition to prevent the loop will filter out the last element).
May
7
comment How to create a versioning DDL/DML change script for SQL Server?
Check out tools like Liquibase or Flyway.
May
7
comment Storing changes to stored procedure and triggers in PostgreSQL
Do you want to track the changes to the source code of the stored procedures? Then use a version control system like Subversion or Git. And make sure you only deploy in a controlled way (or even consider a tool like Liquibase or Flyway)
May
5
comment MySQL Enums have non-intuitive behaviour, why?
I think the reason why many people try to store numbers as enums is the lack of check constraints in MySQL. I see this very often as a workaround for that.
May
5
comment MySQL Enums have non-intuitive behaviour, why?
MySQL doesn't have real booleans. It uses "C-Style" booleans where 0 means "false" anything else means true. So the "boolean" expression b=1 returns a number - either 0 or 1 and that is then used as an index for the actual enum definition.
May
4
comment Cluster index or Partition in postgresql
Postgres does not have a "cluster index". When asking advice on performance problems please see this: wiki.postgresql.org/wiki/Slow_Query_Questions
Apr
30
comment Postgres Replication From Different Masters?
I'm not entirely sure, but my understanding is that the new "timelines" introduced in 9.3 are specifically made for this use case: a slave following a new master without the need to completely re-initialize the slave from scratch - which would be necessary in earlier versions.
Apr
29
comment How to update a table from a another table
@mark: yes I know that. But the syntax given in this answer is not valid for Oracle.
Apr
29
comment Postgres database level encryption
What about using an encrypted filesystem?
Apr
28
comment How to generate current Oracle timestamp in perl?
The generate the date as a string in e.g. ISO format, and use to_date() or to_timestamp()
Apr
28
comment How to generate current Oracle timestamp in perl?
Are you maybe looking for current_timestamp? INSERT INTO ... VALUES (current_timestamp, ...);
Apr
28
comment How can I update a json element in postgresql json datatype
@user1795779: frlan is correct. Updating individual elements in a JSON document will be in 9.4 however (if I'm not mistaken)
Apr
28
comment Create index on very large table with many shared values
Regarding 2): unless you really see a problem with update/delete/insert performance you should keep that index in the database rather than dropping and re-creating it constantly. You can create the index using the CONCURRENTLY option. That will allow concurrent DML to the table but will increase the time to build the index even more.
Apr
28
comment Database Backups of 2GB+
The backup strategy might also depend on the DBMS you are using and what kind of backup it offers (e.g. incremental backups, online backups, file system backups, database dumps, ...)
Apr
26
comment How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)
It's not your job to "create" an execution plan. Just run the statement and Oracle will do that. To see the execution plan use explain plan. Oh, and stop using that old outdated and error prone implicit join syntax in the where clause. Use a proper JOIN condition instead.
Apr
25
comment Sort within row
Which DBMS are you using? Postgres? Oracle? And do you want four columns or is it OK if you get two columns: the first with the val and the second as a comma-separated list? Because otherwise this won't work.
Apr
25
comment Limiting the Number of Records Joined
Which DBMS are you using? Postgres? Oracle?
Apr
24
comment Change posgresql 9.3 slave roll password
You will need to change the password on the master, not on the slave
Apr
23
comment Inserting empty string on Windows MySQL doesn't use default value
'' is not a valid number, it's a string - why would you ever want to insert a string into a number column? The difference in behavior probably comes from using a different value for the strict_mode or the no_auto_value_on_zero configuration parameter. If you do a show warnings on the Linux installation you'll probably see the explanation. In general you should not ignore the message 2 warnings.