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

3h
answered Adding column to Postgres table fails
3h
revised Adding column to Postgres table fails
deleted 8 characters in body
3h
comment Adding column to Postgres table fails
First: 8.4 is no longer maintained, you should plan an upgrade to supported version as soon as possible. The alter table was probably waiting to get an exclusive lock on the table. If you have transactions touching the table, you can't run the DDL. Check pg_locks. If you have sessions that are shown as idle in transaction those are a potential cause for that.
6h
comment How to check table
MySQL or SQL Server? Big difference
15h
comment Why there are so much restriction on LONG datatype in Oracle
LONG was based on the architecture of Oracle 7. With 9i Oracle introduced a new way of storing LOB data and introduced CLOB and BLOB. I guess(!) it was just not feasible to implement the new features based on the existing implementation of the LONG data type (which dates back into the early 80s!)
15h
comment what databases have the implementation of sort merge join?
According to this: use-the-index-luke.com/de/sql/join/sort-merge (at least) Oracle, Postgres, SQL Server and DB2
20h
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)
1d
revised Uexpected values inserted in table
edited title
1d
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
1d
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)
1d
revised Uexpected values inserted in table
added 36 characters in body; edited tags
1d
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
2d
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?
2d
comment Take advantage of monotonic columns in PostreSQL
"Adding a multicolumn index is useless" - are you sure? Did you test it?
2d
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.
Jan
23
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