13,175 reputation
12151
bio website
location Munich, Bavaria
age
visits member for 3 years, 8 months
seen 3 hours ago

8h
comment How to stop Firebird SuperClassic (run as application) in Win 7?
I don't think it's possible from the command line.
8h
comment Cannot create MySQL database with name '5e370227_db'
A SQL identifier is not allowed to start with a number. You must quote the name: "5e370227_db" or using MySQL's dreaded backticks: `5e370227_db`
13h
comment Single Transaction across multiple Sql Database
This is known as a "distributed transaction". Which DBMS are you using? Postgres? Oracle?
13h
comment find tables present in a query
There is no varchar(max) in Postgres
1d
comment SELECT primary key as “serial” or “bigserial”
There is between those two. But the difference is that the first table as a default value for the id column (taken from a sequence) whereas the second one doesn't. When you add a default value to the second then there is no difference any more. You shouldn't care about how that default is defined during table creation, only if it's there.
1d
comment SELECT primary key as “serial” or “bigserial”
"I need to differentiate between integer and serial" - no you don't. If you look at my SQLFiddle you will see that there is no difference. In fact pgAdmin will show exactly the same create table statement for both tables and psql will show exactly the same definition for both tables. serial is just a "macro" for a more lengthy expression. All you need to care about, is the datatype (integer vs. bigint) and the default value (taken from a sequence or not). The serial attribute is just "noise". You need to accept that different DBMS work differently.
1d
comment SELECT primary key as “serial” or “bigserial”
sqlfiddle.com/#!15/034ae/1 This has nothing to do with "hating" people. It's a matter of flexibility. And besides: the "shortcut" serial was added long after sequences and default values where implemented. Rather than checking for "serial" you should check if there are any sequences associated with a column. It doesn't matter how it is associated
1d
comment SELECT primary key as “serial” or “bigserial”
Which programming language are you using? The JDBC driver correctly returns the IS_AUTOINCREMENT attribute for the table columns. And I think the MySQL does as well. Btw: are you aware that you can't distinguish a serial column from one that has been manually defined with a default nextval('...')?
1d
comment How to do an =ANY(SELECT…query in postgresql?
@ErwinBrandstetter: you are absolutely right, thanks. I added that
1d
comment Cannot add a column to an InnoDB table in Mariadb
@Michael: if this worked after a restart my guess would be that one of those "bunch of sleeping and killed processes" still kept some kind of lock on that table that prevented DDL but not DML.
2d
comment Is it bad practice to launch more than 100 sql statements at once?
A busy server will have to cope with far more than "over 100 queries in a short amount of time". They were built for that. Good hardware and a properly tuned DBMS can support thousands of statements per second. (Unrelated but: "I am using Postgres but would prefer answers not specific to Postgres" is like asking a programming question for PHP but expecting answers not specific to PHP)
2d
comment Is making joins between different table spaces slower?
@ypercube: that is only for MySQL Cluster, not for a "regular" MySQL installation.
Jan
26
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.
Jan
26
comment How to check table
MySQL or SQL Server? Big difference
Jan
25
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!)
Jan
25
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
Jan
25
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)
Jan
25
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
Jan
25
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)
Jan
24
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