LEFT OUTER and RIGHT OUTER joins are supported but not FULL OUTER.
Also, when it comes to migrating your data, you might want to take a
look at the foreignViews optional tool:
http://db.apache.org/derby/docs/10.14/tools/rtoolsoptforeignviews.html
Cheers,
-Rick
On 11/15/18 9:18 AM, Alex O'Ree wrote:
Also is "full outer join" statements supported?
On Thu, Nov 15, 2018 at 11:09 AM Alex O'Ree <alexo...@apache.org
<mailto:alexo...@apache.org>> wrote:
Thanks Rick
I also noticed that the wording and ordering of limit and offset
for select statements is way different in derby.
Postgres style: select * from table limit 3 offset 5
Derby: select * from table offset 5 rows fetch next 3 rows only
Next issue I ran into was that I have tons of insert statements
that read like this (postgres style)
insert into table (column1, column2) values ('asd', 'xyz') on
conflict do nothing;
An insert statement like this is used in a batched prepared
statement. Overall goal is to insert everything and when there is
a primary key collision, just ignore it. In postgres, any failure
will cause the whole batch to abort. Is there a derby equivalent
to this? I did run across this merge jira which may solve the
problem. https://issues.apache.org/jira/browse/DERBY-3155 but is
that the only solution?
On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas
<rick.hille...@gmail.com <mailto:rick.hille...@gmail.com>> wrote:
Hi Alex,
Thanks for compiling this list of issues. Some comments inline...
On 11/14/18 1:22 PM, Alex O'Ree wrote:
> Greetings. I'm looking for some kind of migration guide and
for things
> to watch out for when migration an application to derby.
>
> Since i haven't found one yet, i decide to write down and
share some
> of my notes on the things I've ran into so far:
>
> DDL - From postgres, there's lots of differences.
> - Postgres 'text' becomes 'long varchar'
Sounds like LONG VARCHAR wasn't long enough for you and you
needed CLOB
instead.
> - Can't insert from 'text literal' into a blob without some
quick code
> and a function to convert it
BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
> - Postgres gives you the option to select the index type,
derby does
> not appear to. have this function. Not really sure what kind
of index
> it is either. btree?
All Derby indexes are btrees. They can be unique or non-unique.
>
> JDBC clients
> - limit and offset has a bit of a strange syntax. most rdbs
will
> access just the literal limit 10 offset 1 syntax. Derby
appears to
> need to wrap this in { }, so select * from table { limit 10
offset 10}
Derby supports the SQL Standard OFFSET and FETCH clauses. See
http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
> - from a JDBC client, don't include semicolons in your sql code.
Again, Derby supports SQL Standard syntax. The semicolons are
not part
of the Standard grammar, although they are used by command line
interpreters (like Derby own ij CLI) to mark the end of
statements. I
agree that rototilling your code to remove non-Standard
semicolons
sounds like a drag.
>
> For the last two, is this "normal"? I have a large code base
and
> refactoring it would be painful. I'm thinking it may be
easier to hack
> up the jdbc driver to "fix" the sql statements on the fly. Any
> thoughts on this? maybe there is some kind of configuration
setting to
> make this easier?
The place to hack this would be in the parsing layer, below
the embedded
JDBC layer. You might also want to take a look at the code for
the ij
tool, which has to deal with semicolons.
Hope this helps,
-Rick
