db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: attempting to migrate from postgres to derby
Date Thu, 15 Nov 2018 20:24:58 GMT
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 <alexoree@apache.org 
> <mailto:alexoree@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.hillegas@gmail.com <mailto:rick.hillegas@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
>
>


Mime
View raw message