db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alex O'Ree" <alexo...@apache.org>
Subject Re: attempting to migrate from postgres to derby
Date Thu, 15 Nov 2018 17:18:04 GMT
Also is "full outer join" statements supported?

On Thu, Nov 15, 2018 at 11:09 AM Alex O'Ree <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>
> 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