Consider the following setup:
create table x (a int, b int);
insert into x
values
(1,1),
(1,2),
(2,5),
(2,6),
(2,7);
Now run the following query:
select a, array_agg(b)
from x
group by a;
This returns:
A | ARRAY_AGG(B)
-
1 | (1, 2)
2 | (5, 6, 7)
Now if you add an
But to be consistent you would need to throw an exception for derived
tables as well, as they have the same visibility.
However, the following works as expected (given the table definitions from
my initial post):
select *
from (
select c3, c4 + 42
from b
) a;
Am Freitag, 30. Juni 2017
Consider the following sample data:
create table a (c1 integer, c2 integer);
create table b (c3 integer, c4 integer);
insert into a values (1,1), (2,2);
insert into b values (3,3), (4,4);
with a(col1, col3) as (
select c3, c3 + 42
from b
)
select *
from a;
The above returns
C1 | C2
That syntax is already supported by H2 since version 1.4.177 (see:
http://www.h2database.com/html/changelog.html)
However, the "fetch first" variant is apparently not yet documented.
Am Sonntag, 19. März 2017 10:29:11 UTC+1 schrieb Philippe Marschall:
>
> Now that 12c supports row limiting
>
I think there are some errors in the DBMS comparison at:
http://www.h2database.com/html/features.html#comparison
Postgres has "Linked tables" through Foreign Data Wrappers:
https://wiki.postgresql.org/wiki/FDW (which is available since V9.0 which
that comparison is based upon)
Neither
Not sure what you are asking, because the || concatenation operator
works in H2 the same as in all other standard compliant databases.
On Dec 29, 11:00 pm, leojhartiv leo.h...@gmail.com wrote:
We're currently using H2 as our local development database, while
Oracle is our production database
On Aug 2, 2:31 pm, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
You have a serious complexity problem here.
No I don't ;)
These are aggregating reports that do some pretty nifty aggregation
calculation on a daily basis. And in order to re-use the intermediate
results CTEs are very
On Jul 28, 8:26 pm, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
As far as I can tell this currently *only* works for recursive queries
- at least I could not get a non-recursive CTE to work.
Yes, that's possible. I only tested the recursive case. I think that's
the main reason to use
Hi,
in the last version Experimental support for recursive queries (=
common table expressions) was added, which is very cool!
As far as I can tell this currently *only* works for recursive queries
- at least I could not get a non-recursive CTE to work.
Is it planned to extend this to a
Thanks for the feedback.
What I find confusing is, that the rownum is reset when handed
from a derived table to the upper select (see my example earlier)
Having the rownum assign the numbers for each partial select would be
more consistent (until windowing functions with row_number() are
On May 14, 12:22 pm, Rami Ojares rami.oja...@gmail.com wrote:
14.5.2010 13:00, Thomas Kellerer kirjoitti: On May 14, 11:40 am, Kerry
Sainsburyke...@fidelma.com wrote:
Sorry -- I don't know! I don't see much value in rownum() either. I've
never needed or wanted to use it for anything
On Apr 26, 6:43 am, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
Hi,
What about adding this at the very beginning:
cd %~dp0
Regards,
Thomas
I think simply using %~dp0 makes it more stable and easier to use for
other scripts.
If you do change the current directory that might confuse
On Apr 23, 7:47 pm, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
Hi,
When executing as admin, the default directory is c:\windows\system32
Do you mean, if you double-click it?
and the script fails since it has relative paths. Would be nice to add a
cd command to fix that.
Could
On Apr 9, 4:30 pm, dmiche...@googlemail.com
dmiche...@googlemail.com wrote:
For instance, if I want to test my sql statement from the H2 GUI front-
end before doing via my JDBC java code.
I tried to pass the path , i.e. INSERT INTO mytable (id,name,file)
You have two easy options:
Create a global environment variable named JAVA_OPTS and the contents
-Duser.home=/path/to/your/home/dir
or create a script setenv.bat (or setenv.sh) in $CATALINA_HOME/bin
that contains the above environment variable.
Thomas
On Mar 22, 9:15 pm, Thomas Mauch
On Feb 10, 10:05 am, Remo remo.ma...@gmail.com wrote:
@Thomas
I do have an ORDER BY in my query. But due to equal values, there is
not one single correct order. If the db engine uses stable sorting
algorithms, the rows should be returned in the same order over
multiple runs.
Ah sorry,
The only(!) way to get a deterministic ordering from a relational
database is to use ORDER BY.
Just because you get a consistent ordering between two different runs
(in some databases), does not mean you get it on a third run. As soon
as you insert or update or even change the WHERE condition,
Hi,
maybe I'm missing something, but the following statement
ALTER TABLE my_table DROP PRIMARY KEY;
fails with the error message:
Index PRIMARY_KEY_14E belongs to a constraint
When I run
ALTER TABLE my_table ADD CONSTRAINT PK_MY_TABLE
it is working.
According to the SQL Grammer description
when dropping a column that is a (single
column) foreign key. It cannot be dropped unless I drop the FK
constraint first :(
On Feb 5, 10:31 am, Thomas Kellerer google-gro...@sql-workbench.net
wrote:
Hi,
maybe I'm missing something, but the following statement
ALTER TABLE my_table DROP PRIMARY
This should work
INSERT INTO target_table (id, some_data)
SELECT id, some_data
FROM other_table o
WHERE NOT EXISTS (SELECT 1 FROM target_table t1 WHERE o.id = t1.id);
Assuming column ID is the primary key in both tables.
On Feb 1, 10:03 pm, gabjos sikyala_raq...@bah.com wrote:
I need to
On Jan 19, 8:53 pm, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
The best of course would be to allow case sensitivity as well but I
gather that that is harder to implement.
There is a reason why standards exists and just because they are
ignored by Microsoft or (even more so) by the
On Jan 17, 4:50 pm, ggrothendieck ggrothendi...@gmail.com wrote:
Is there documentation which tells one which SQL statements return a
result set and which ones do not.
You can always use the generic execute() method and then test for
result sets and/or update counts:
On Dec 29, 11:48 pm, Nitai @ Razuna ni...@razuna.com wrote:
thus the question is it possible to disable
constraint checking for the complete database or single tables?
http://www.h2database.com/html/grammar.html#set_referential_integrity
--
You received this message because you are subscribed
Hi,
I have seen the new feature to add new function directly with Java
source code. Pretty cool feature.
Is there a way to retrieve the Java source somehow, or is that not
stored in the system tables?
I checked INFORMATION_SCHEMA.FUNCTION_ALIAS but as far as I can tell,
the source is not
DatabaseMetaData meta = connection.getMetaData();
rs = meta.getTables(connection.getCatalog(), schemaName,
null, new String[]{ SEQUENCE });
I didn't know about this, what databases support this feature?
The Postgres JDBC driver does, if I remember correctly
Yeah, but postgres views are not writable :(
Yes, they are through the use of rules.
It's a bit cumbersome especially when you need to do that for a large
number of view, but it works
--~--~-~--~~~---~--~~
You received this message because you are subscribed to
On 9 Aug., 16:53, charly gartenzwer...@googlemail.com wrote:
This would be a proprietary feature of H2 (and maybe other databases
like the above mentioned Oracle)
and is fine, if the SQL-statements are running only on H2 i.e. need
not be portable.
I'm not sure what kind of escaping is
But it seems its not standard
1. that there is a default ESCAPE char active, if the keyword ESCAPE
is NOT used
Correct. The standard requires that no escaping takes place if ESCAPE
is not specified.
2. that ESCAPE '' i.e. with empty string (meaning no escape char
active) can be provided
On 3 Aug., 21:10, Thomas Mueller thomas.tom.muel...@gmail.com wrote:
I don't think it's incorrect. It matches the documentation, and as far
as I know it works like Oracle.
Right, this is exactly the way Oracle's ROWNUM behaves (although in
Oracle it's not a function, so ROWNUM() does not
On 11 Jul., 17:46, Thomas Mueller thomas.tom.muel...@gmail.com
wrote:
I didn't know there is a standard... Do you have a link to the standard?
I think it's called: SQL/PSM (Persistent Stored Modules) and is
implemented in HSQLDB 1.9 beta and (IIRC) DB2.
Thomas
Hi,
with V1.0.79 the following SELECT statement would return MY_ID for
ResultSetMetaData.getColumnName(1)
SELECT id as my_id FROM my_table
with 1.1.114 getColumnName() returns ID
Is that indended?
Regards
Thomas
--~--~-~--~~~---~--~~
You received this
16, 2009 at 6:44 PM, Thomas Kellerer
google-gro...@sql-workbench.net wrote:
I don't think support for 1.4 is important. How long is it that this
has been de-supported by Sun?
Even 1.5 will be de-supported by Sun end of this year (October).
Just my .02€
Thomas
On 16 Mai, 12:20
I don't think support for 1.4 is important. How long is it that this
has been de-supported by Sun?
Even 1.5 will be de-supported by Sun end of this year (October).
Just my .02€
Thomas
On 16 Mai, 12:20, Thomas Mueller thomas.tom.muel...@gmail.com wrote:
Hi,
So far the H2 source code is
My, granted very small, test suite now takes ~1.1s on postgres and ~0.6s
with H2. So, almost 2x as fast. Not a big deal now, but on larger test
suites, that will be nice.
A bit off-topic here:
For Postgres you could setup your schema in a template database. When
you want to run your tests,
Methods setBytes and setBinaryStream are not supported (H2 throws
Unsupported Exception). How can I use Blob? Is it even possible?
This works for me:
PreparedStatement stmt = connection.prepareStatement(INSERT INTO
blob_table (blob_column) VALUES (?);
InputStream blobData = new
On 2 Feb., 19:36, Mike Goodwin mkpgood...@gmail.com wrote:
It surprised me that the sum over 0 rows comes to null and not zero.
So what is the sum of nothing?
DROP TABLE IF EXISTS x;
CREATE TABLE x (val INTEGER);
INSERT INTO x values (null);
SELECT sum(val) FROM x;
also returns null.
If you have a column that contains the creation timestamp in your
table then you can use the following:
select *
from my_table
where datediff('MINUTE', created_at, current_timestamp) = 2;
will return all records that were created in the last two minutes.
On 24 Jan., 10:42, Thotheolh
Hi,
MySQL and MS SQL Server compatibility: support case sensitive (mixed
case) identifiers without quotes.
Thanks!
If I had the slightest hope that it would help I'd file an issue with
MySQL and Microsoft to make them respect the ANSI standard a lot
more :)
By the way, do you have a link to
One question though: are there plans to support preservation of case
in table names without in H2 in the near future?
I doubt that this will be implemented (for time and other reasons as
Thomas has explained to me in another posting)
But: you are aware that this would make H2 non-compliant
Hi,
I have a rather strange question:
I'm using H2 a lot to do unit testing on my application. Now SQL
Server (and MySQL) can be configured to be case-sensitive for table
names even without double quotes. As much as I dislike this behaviour,
I need to test my application if it can cope with
40 matches
Mail list logo
