13,020 reputation
12151
bio website
location Munich, Bavaria
age
visits member for 3 years, 8 months
seen 2 mins ago

4h
comment Best approach to maintain a single set of DDLs and DMLs for multiple database type
@ypercube: I can actually find 5 DBs ;) The following works on Postgres, Oracle, DB2, Vertica and HSQLDB: current_date + interval '3' day But in general I totally agree that writing DBMS independent SQL simply means it will be equally slow on all databases (if it can be achieved at all)
10h
revised Uexpected values inserted in table
edited title
11h
comment Take advantage of monotonic columns in PostreSQL
Here is a complete fiddle: sqlfiddle.com/#!15/0d48e/1 You can see that the index scan only retrieved 3 blocks (Buffers: shared hit=3) which accounts for roughly 1000 rows that were evaluated to get the result out of 100.000
13h
comment Take advantage of monotonic columns in PostreSQL
The Buffers: read = 275 indicates this (275 blocks were read) and the index is indeed 275 blocks in size. But that was the query where bar >= 1020 and bar <= 1100 when I use bar >= 1020 and foo <= 1100 (the original query) Postgres visits only parts of the index: explain.depesz.com/s/PEwA (the second plan was taken with 5 million rows in the sample table)
23h
revised Uexpected values inserted in table
added 36 characters in body; edited tags
23h
comment Take advantage of monotonic columns in PostreSQL
@giomasce: using Jack's sample setup (but adding additional columns to make it more realistic), Postgres uses a combined index on both columns, so I'm wondering why you think it wouldn't help: explain.depesz.com/s/ogtw
1d
revised Tree structure hierarchical query sorting
edited tags
1d
comment Storing latitude and longitude
I would say if you are doing a lot of GIS processing then adding PostGIS will actually remove some complexity. But even if you only use the PostGIS data types, I don't see how that adds "additional complexity". You install it once and then you are done. Btw: what exactly was the problem when using the point data type?
1d
comment Take advantage of monotonic columns in PostreSQL
"Adding a multicolumn index is useless" - are you sure? Did you test it?
1d
revised Difficulty with nested query order & grouping
added 38 characters in body; edited tags
2d
revised Timeline or time function in PostgreSQL
remove link to unsupported version
2d
comment PostgreSQL operator uses index but underlying function does not
Do you have to use a PreparedStatement? As long as you don't use that, you can use a ? without any problems.
2d
comment Timeline or time function in PostgreSQL
What exactly is "messy" about that output? To me this looks exactly the way you want it.
Jan
22
revised Oracle Package in Data Dictionary that can't be dropped
added 76 characters in body
Jan
22
revised Improving lots of Left Joins ( SQL Server 2008 - procedure )
edited tags
Jan
22
comment How to store thousands of properties related to a record in PostgreSQL?
I think the only option you have is to actually create a sample database with a hstore column and see how this performs.
Jan
22
comment what is the difference between data file and data block in oracle
I think this link to the manual is better: docs.oracle.com/database/121/CNCPT/logical.htm#CNCPT004
Jan
22
comment Access Oracle GTT table data from another session?
"I know GTT tables data is only session specific vs. "is their any way to access/read GTT table that belongs to another session" if you know it's impossible then why do you ask?
Jan
22
revised sql agent job hangs without any errors or warnings
edited tags
Jan
22
comment Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”
You state that "one person can only have one license" so why is person_id not a primary key in the license table then?