12,800 reputation
12049
bio website
location Munich
age
visits member for 3 years, 7 months
seen 15 hours ago

Nov
26
comment Get list of characters in column
@Colin'tHart: not really, no ;)
Nov
25
comment Can any one Explain me What is the Use of Cascading Replication in Postgresql?
If you do a switchover from master to slave1, you still have a spare slave if the now active master also fails. Otherwise you'd be running with no failover computer at all.
Nov
24
comment Increase performance WITHOUT adding indexes
I wouldn't call statements "which are only performed every few seconds" rare. The are executed often enough to warrant an index. I have never seen a DBMS where adding an index would make the statement slower because parsing (the step where the best index is searched for) became an issue. When it comes to performance don't just assume things. Test them. Your concern about the DML is a valid one though, but I have seen only a few systems where adding an index really made the DML so bad that it had to be removed. But again: you have to test this.
Nov
24
comment How long to learn PostgreSQL?
"Entirely removing Postgres can be a bit tricky" - to "play" around I usually only unzip the archives (e.g. the zip on Windows) and run initdb manually. To "throw away" such an installation, all I need to do is to delete the directory where I unzipped the archive.
Nov
24
comment Error on DDL statement never invokes CATCH
"I understand that DDL statements are implicit COMMIT" - I think you are confusing Oracle (or MySQL) and SQL Server.
Nov
24
comment How long to learn PostgreSQL?
Make sure you take the time to go through the manual, at least the chapter "SQL Language": postgresql.org/docs/current/static/sql.html
Nov
23
comment How to restrict access to current user record and its rights in postgres
"Is it possible and reasonable to create plpgsql methods for those operations" - yes, that is possible and probably a bit safer than using a view.
Nov
22
comment Prevent Primary Key increment when an INSERT statement fail in PostgreSQL
You can't prevent. That's the whole point of using sequences. And you don't have to care either. The generated id is just some artificial value that has no meaning whatsoever. Whether your artificial key is 1, 42, 36387 or 43873483 is completely irrelevant as long as it is unique.
Nov
21
comment postgres materialized views and indexes to improve aggregate performance
Oracle actually does this. When the optimizer sees a query that matches a MVIEW query, it will use the mview instead if that is cheaper (called "query rewrite")
Nov
20
comment Error importing postgres DB dump into empty database
You might want to first try an up-to-date version. 8.4 is no longer maintained. You didn't mention your exact 8.4 version, but did you check the latest patch version for 8.4?
Nov
18
comment The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values
"for good reasons" - possibly to enhance Oracle compatibility ;) I didn't know that SQL Server had exactly the same restriction.
Nov
18
comment copy data from public schema to another schema in postgresql
If both schemas are in the same database, you don't need pg_dump/pg_restore. Using insert into my_schema.some_table select * from public.some_table will work just as well. Or even create table my_schema.some_table as select * from public.some_table. You can automate this using a stored function, or a script that generates the necessary SQL statements
Nov
17
comment Change constraint on column based on value of another
If you allow refunded = true AND refund_id IS NULL then (and only then) it would not be redundant. But that would seem like a strange combination to allow.
Nov
17
comment Change constraint on column based on value of another
@KinnardHockenhull: why it is redundant? Well if refunded is true, apparently refund_id must be not null and therefor it points to a valid transaction. If refunded is false, refund_id should be null. Unless the last assumption is not correct, the value of refunded can be deduced by the fact that a refund_id exists. And yes, that's how foreign keys work.
Nov
17
comment PostgreSQL queries slower than before?
Cross post: stackoverflow.com/q/26978554/330315
Nov
17
comment Why does = NULL not work in Oracle?
This is how NULL comparison is defined in the SQL standard. It is nothing that is specific to Oracle.
Nov
16
comment Table design for optimum performance
You should never mix different types of information into a single column, not even "hypothetically". Especially your third query will benefit from properly putting the gender into its own column (you can do an equality on the gender column rather than needing a LIKE query on the EmployeeID column)
Nov
16
comment where in (sub query) vs join
Which DBMS are you using? Most query planners are smart enough to apply such a transformation for you (if possible). And a join is not necessary a replacement for an IN condition because the join might return multiple rows where the IN would only return a single row.
Nov
15
comment When using underscores, should they be reserved to junction tables?
According to the SQL standard CompanyType will be stored as COMPANYTYPE because all non-quoted identifiers must be converted to uppercase. Therefore it's better to use "snake case": COMPANY_TYPE
Nov
14
comment SQL Server: performance of IDENTITY vs INT as primary key
An identity column is an integer column.