12,570 reputation
12048
bio website
location Munich
age
visits member for 3 years, 6 months
seen 3 hours ago

6h
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.
1d
comment how to select from two tables
You are looking for a JOIN
1d
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.
2d
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
19
comment PL/SQL IDE like C++ compilers
What's wrong SQL Developer?
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 Get Current Row and Next Row
Check out the lead() function: postgresql.org/docs/current/static/tutorial-window.html
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
16
comment What is the best database system for my situation?
Personally I can't see any reason to prefer MySQL over Postgres. Postgres has many more modern SQL features than MySQL and there is hardly any feature that MySQL has, that Postgres doesn't (group by ... rollup is the only I can think of). Other people seem to share my opinion: stackoverflow.com/q/110927/330315 and stackoverflow.com/q/27435/330315 Especially if you plan to use Geo spatial features, the extension PostGIS is much more advanced than anything MySQL can offer
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.
Nov
14
comment Looking for a recipe to merge multiple rows to single row
To me it sounds as if a materialized view is your best choice. The refresh is transactional so concurrent inserts are not seen during the refresh. The question is how often you need to refresh it and whether building the mview would be fast enough.
Nov
14
comment Need help designing a database
You don't have to worry about maintaining the index "manually", the database takes care of that (you might want to read the manual: postgresql.org/docs/current/static/indexes.html). The index does indeed incur a write overhead, but can speed up reading massively (it can also speed up the delete). Another option might be a materialized view that is refreshed nightly and only contains the most recent rows. What you have is essentially a greatest-n-per-group problem. Here is a discussion about this: stackoverflow.com/a/7630564/330315