12,179 reputation
11947
bio website
location Munich
age
visits member for 3 years, 5 months
seen 14 mins ago

Oct
2
comment Slow querys on billions-rows-table // index used
Postgres doesn't have clustered indexes (although it can cluster a table along an index - but that needs to be done manually and won't "stay")
Sep
28
comment How to combine a default value with a new value?
If really all values should have that prefix, I wouldn't even store that. Create a view that returns the concatenated value. There is no sense in storing data that is always the same thing.
Sep
28
comment How to combine a default value with a new value?
Which DBMS are you using? Postgres? Oracle?
Sep
28
comment Prevent gaps while using Identity/sequence object in sql server
"I want to prevent gaps in the primary key column" - why? The value of a primary key - especially an auto generated one - is meaningless. It's only purpose is to uniquely identify a row. And to do that job the value itself is totally meaningless. And gaps are equally meaningless.
Sep
28
comment DIRECT and SORTED in H2 and How to insert data with SORTED technique in H2
In the link you provided (right below the values clause)
Sep
27
comment DIRECT and SORTED in H2 and How to insert data with SORTED technique in H2
"How to insert data with SORTED technique?" - maybe by specifying the sorted keyword as shown in the manual?
Sep
27
comment Postgresql: Domain of two values
I agree with ypercube: range types are the way to go here.
Sep
26
comment How much should I care about prepared statements for simple queries?
When using JDBC a parameterized statement is always a prepared statement.
Sep
23
comment Postgres - Combine similar queries with extra where clause
You can use a CTE to only have the "broader" query once, but you still need to return a single result set.
Sep
23
comment Alternatives for a LEFT OUTER JOIN query
@MarkSinkinson: ah, thanks. I didn't know that.
Sep
23
comment Postgres - Combine similar queries with extra where clause
A single query can only return a single result set. The only way to "combine" results is by using UNION but the outcome of that is again a single result.
Sep
23
comment Extracting an an integer value from an xml column
Added sql-server tag based on the usage of patindex and NOLOCK
Sep
23
comment Alternatives for a LEFT OUTER JOIN query
Btw: SQL Server supports concat() as well - actually in my experience concat() is the most portable way to concatenate strings. It works (at least) in Postgres, Oracle, DB2, SQL Server, MySQL, H2 and HSQLDB
Sep
23
comment Alternatives for a LEFT OUTER JOIN query
will work just fine - if you use standard SQL syntax for the alias. nlsGuid = CASE is invalid (standard) SQL. If you change that to case .. end as nlsGuid your query works just fine (and will still work in SQL Server). When I do that, your example query and data returns exactly the same result in h2 and SQL Server
Sep
23
comment Alternatives for a LEFT OUTER JOIN query
"uses concat() which is crap because afaik this does not match the SQL specifications" + is the wrong operator here. The SQL standard defines || as the string concatenation operator and that's what H2 is using. But I fail to see how H2 following the SQL standard for string concatenation means the outer join "has a bug". Those are two completely different things. And the SQL query you have shown us does not contain a +, or concat(). What exactly is the "bug" you claim to have found in h2? The statement you have shown will work just fine.
Sep
22
comment composite index vs normal index
Check out this website. It really explains how to use indexes: use-the-index-luke.com
Sep
22
comment Using subqueries in delete query
where (SELECT * FROM candidate limit 1,1) is wrong. You need to use where some_column IN (select other_column ...) But don't do that with MySQL. MySQL is horribly bad at using sub-queries. Use a join instead.
Sep
22
comment Using subqueries in delete query
MySQL neither supports common table expressions nor window functions.
Sep
19
comment dm_exec_query_stats in PostgreSQL
the pg_stat_statements extension is probably the closest thing: postgresql.org/docs/current/static/pgstatstatements.html
Sep
19
comment Foreign key without referenced columns specification
The rule is actually: "an implicit foreign key references the primary key - and only the primary key - of the referenced table". A unique constraint will never be eligible for this (it's the same in Postgres btw).