Reputation
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
3 29 58
Newest
 Copy Editor
Impact
~1.0m people reached

Aug
5
comment Create postgres schema in db with multi user in AUTHORIZATION
@tymik: those are different things: grant all on schema grants the right to "use" the schema in the first place. It's a pre-requisite to be able to "access" anything in the schema. See my edit. This is explained quite nicely in the manual: postgresql.org/docs/current/static/sql-grant.html
Aug
5
comment Create postgres schema in db with multi user in AUTHORIZATION
@tymik: no unfortunately you have to run each of those statements. There is no single privilege - especially for the default privileges. But you only need to do this once to grant this to a role. Then grant the role to the users who should actually get those privileges.
Aug
5
comment Why does Postgres run SORT on many rows, before GROUP BY?
Apparently 512MB is still not enough, because the sorting is still done on disk
Aug
5
comment Why does Postgres run SORT on many rows, before GROUP BY?
I think the sort step you see, is done for the group by. If you look at the "Sort Key", it's exactly the columns you specified for the group by. Maybe it's doing the two step grouping because of lack of work_mem. What happens if you increase work_mem for the session? (set work_mem='512MB')
Aug
5
comment Recursive BOM (Bill Of Material) type query
postgresql.org/docs/current/static/queries-with.html
Aug
4
comment Why does PK constraint require a separate index when I already have a suitable unique clustered index?
Why don't you simply remove the CREATE INDEX statement from the script?
Aug
4
comment Why does PK constraint require a separate index when I already have a suitable unique clustered index?
The question is: why did you add the unique index if you already have a PK on that column?
Aug
4
comment Priority search on columns
@Innnh Sorry, or (c1 <> 2 and c2 = 1 and c3 <> 1) was a copy & paste error. Was playing around with different search values. And the c3 <> 2 in second condition was wrong.
Aug
4
comment Priority search on columns
If you search for the value 1 I assume only the first row of your sample data should be returned?
Aug
3
comment Versioning the DB scripts (MySQL)
Check out Liquibase or Flyway
Aug
3
comment Getting the last data update time of a specific table in Oracle
Cool, I have never come across those. But it's not really "automatically". From the manual: "These views are populated only for tables with the MONITORING attribute"
Aug
3
comment Postgres query plan has high rowcount, slow execution
Can you please add the create table statements and the create index statements for the involved tables and indexes?
Aug
2
comment A costly approach to filtering on a calculated date
Did you try to use a daterange instead of two columns and create an index on that single column, e.g.: sqlfiddle.com/#!15/9f3b9/1 Btw: DATE '2014-07-18' + (INTERVAL '1 day' * order_items.lead_days can be simplified to DATE '2014-07-18' + order_items.lead_days the unit for an expression date + integer is days.
Aug
1
comment How many passes of a table are done with UNION ALL?
Check the execution plan. And please add the tag (postgresql, oracle, ...) for your DBMS
Aug
1
comment View of structurally same tables
I would go for the last solution and add a new column that identifies the source of the rows.
Jul
31
comment Selecting records where date range is current year minus #years
The question is tagged oracle. There is no dateadd() function in Oracle.
Jul
31
comment Optimise huge Postgres table for fast read
Essentially all DBMS use a some kind of a tree to represent an index ("B-Tree" - balanced trees). I'm not aware of any DBMS that does this differently (for standard indexes). Postgres' indexes are explained in the manual: postgresql.org/docs/current/static/indexes.html
Jul
31
comment Optimise huge Postgres table for fast read
Which is essentially the same thing as creating the index on the MD5 sum suggested by Frank. A lookup on an index is a binary search as well
Jul
31
comment Recommended Database(s) for Selecting Random Rows
So how do you select the random row? Show us your query, table definitions and the explain plan.
Jul
31
comment Minimum value on most recent date per account MS ACCESS SQL
The example is actually standard SQL - no T-SQL specialties in there. It will run without any change on Oracle, Postgres and DB2