Exception when using INSERT... RETURNING with Postgres 9.1

[lachelt]

I'm new to myBatis...

I have a table in a Postgres 9.1.3 DB with the following schema:

  CREATE TABLE contact (
     id serial NOT NULL,
     lastName varchar(50) DEFAULT NULL,
     firstName varchar(50) DEFAULT NULL,
     PRIMARY KEY (id)
  );

Here is my ContactMapper declaration:

        final String INSERT = "INSERT INTO CONTACT (EMAIL, LASTNAME,
FIRSTNAME, PHONE) "
                        + "VALUES (#{email}, #{lastName}, #{firstName}, #{phone}) RETURNING
id";

        @Insert(INSERT)
        @Options(keyProperty = "id", useGeneratedKeys = true, flushCache =
true)
        Integer insert(Contact contact);

And here is where I'm invoking it:

                        Contact newContact = new Contact();
                        newContact.setFirstName("Josh");
                        newContact.setLastName("Pacer");
                        int id = mapper.insert(newContact);

But I get the following exception:
   org.postgresql.util.PSQLException: ERROR: syntax error at or near
"RETURNING"

I don't have this problem when issuing the statement in psql.

MyBatis: 3.1.0
Driver: postgresql-9.1-901.jdbc4.jar driver:
Postgres Server: 9.1.3

Thanks for any insight

[François Schiettecatte]

Dumb question but your table does not include EMAIL or PHONE?

François

On Apr 3, 2012, at 1:39 PM, Jon Lachelt wrote:

> I'm new to myBatis...
>
> I have a table in a Postgres 9.1.3 DB with the following schema:
>
>  CREATE TABLE contact (
>     id serial NOT NULL,
>     lastName varchar(50) DEFAULT NULL,
>     firstName varchar(50) DEFAULT NULL,
>     PRIMARY KEY (id)
>  );
>
> Here is my ContactMapper declaration:
>
> final String INSERT = "INSERT INTO CONTACT (EMAIL, LASTNAME,
> FIRSTNAME, PHONE) "
> + "VALUES (#{email}, #{lastName}, #{firstName}, #{phone}) RETURNING
> id";
>
> @Insert(INSERT)
> @Options(keyProperty = "id", useGeneratedKeys = true, flushCache =
> true)
> Integer insert(Contact contact);
>
> And here is where I'm invoking it:
>
> Contact newContact = new Contact();
> newContact.setFirstName("Josh");
> newContact.setLastName("Pacer");
> int id = mapper.insert(newContact);
>
> But I get the following exception:
>   org.postgresql.util.PSQLException: ERROR: syntax error at or near
> "RETURNING"
>
> I don't have this problem when issuing the statement in psql.
>
> MyBatis: 3.1.0
> Driver: postgresql-9.1-901.jdbc4.jar driver:
> Postgres Server: 9.1.3
>
> Thanks for any insight

[Jeff Butler]

You don't need "returning id" on your insert statement.  Because the
id is of type serial, the driver will know what column to grab.

There is an example with PostgreSQL here:

http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibatis/submitted/keycolumn/

Jeff Butler


On Tue, Apr 3, 2012 at 1:39 PM, Jon Lachelt <[hidden email]> wrote:

> I'm new to myBatis...
>
> I have a table in a Postgres 9.1.3 DB with the following schema:
>
>  CREATE TABLE contact (
>     id serial NOT NULL,
>     lastName varchar(50) DEFAULT NULL,
>     firstName varchar(50) DEFAULT NULL,
>     PRIMARY KEY (id)
>  );
>
> Here is my ContactMapper declaration:
>
>        final String INSERT = "INSERT INTO CONTACT (EMAIL, LASTNAME,
> FIRSTNAME, PHONE) "
>                        + "VALUES (#{email}, #{lastName}, #{firstName}, #{phone}) RETURNING
> id";
>
>        @Insert(INSERT)
>        @Options(keyProperty = "id", useGeneratedKeys = true, flushCache =
> true)
>        Integer insert(Contact contact);
>
> And here is where I'm invoking it:
>
>                        Contact newContact = new Contact();
>                        newContact.setFirstName("Josh");
>                        newContact.setLastName("Pacer");
>                        int id = mapper.insert(newContact);
>
> But I get the following exception:
>   org.postgresql.util.PSQLException: ERROR: syntax error at or near
> "RETURNING"
>
> I don't have this problem when issuing the statement in psql.
>
> MyBatis: 3.1.0
> Driver: postgresql-9.1-901.jdbc4.jar driver:
> Postgres Server: 9.1.3
>
> Thanks for any insight

[lachelt]

Yea... I intended to remove that stuff in this posting to simplify the
example.
My table and code all have all of the right fields.

On Apr 3, 11:56 am, François Schiettecatte <[hidden email]>
wrote:

> Dumb question but your table does not include EMAIL or PHONE?
>
> François
>
> On Apr 3, 2012, at 1:39 PM, Jon Lachelt wrote:
>
>
>
>
>
>
>
> > I'm new to myBatis...
>
> > I have a table in a Postgres 9.1.3 DB with the following schema:
>
> >  CREATE TABLE contact (
> >     id serial NOT NULL,
> >     lastName varchar(50) DEFAULT NULL,
> >     firstName varchar(50) DEFAULT NULL,
> >     PRIMARY KEY (id)
> >  );
>
> > Here is my ContactMapper declaration:
>
> >    final String INSERT = "INSERT INTO CONTACT (EMAIL, LASTNAME,
> > FIRSTNAME, PHONE) "
> >                    + "VALUES (#{email}, #{lastName}, #{firstName}, #{phone}) RETURNING
> > id";
>
> >    @Insert(INSERT)
> >    @Options(keyProperty = "id", useGeneratedKeys = true, flushCache =
> > true)
> >    Integer insert(Contact contact);
>
> > And here is where I'm invoking it:
>
> >                    Contact newContact = new Contact();
> >                    newContact.setFirstName("Josh");
> >                    newContact.setLastName("Pacer");
> >                    int id = mapper.insert(newContact);
>
> > But I get the following exception:
> >   org.postgresql.util.PSQLException: ERROR: syntax error at or near
> > "RETURNING"
>
> > I don't have this problem when issuing the statement in psql.
>
> > MyBatis: 3.1.0
> > Driver: postgresql-9.1-901.jdbc4.jar driver:
> > Postgres Server: 9.1.3
>
> > Thanks for any insight

[lachelt]

Oh... now looking at your example I now see how this is supposed to
work.

I had tried it without the "RETURNING" clause, and it "worked" but I
wasn't getting back what I expected.

I was expecting the id to be returned, like this:
                        int id = mapper.insert(newContact);

but from your example I see that the # of rows inserted is what gets
returned from the method, and the id is getting set in the passed
entity.

It works for me just fine.  Thanks for your patience with this newbie.



On Apr 3, 12:07 pm, Jeff Butler <[hidden email]> wrote:

> You don't need "returning id" on your insert statement.  Because the
> id is of type serial, the driver will know what column to grab.
>
> There is an example with PostgreSQL here:
>
> http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibat...
>
> Jeff Butler
>
>
>
>
>
>
>
> On Tue, Apr 3, 2012 at 1:39 PM, Jon Lachelt <[hidden email]> wrote:
> > I'm new to myBatis...
>
> > I have a table in a Postgres 9.1.3 DB with the following schema:
>
> >  CREATE TABLE contact (
> >     id serial NOT NULL,
> >     lastName varchar(50) DEFAULT NULL,
> >     firstName varchar(50) DEFAULT NULL,
> >     PRIMARY KEY (id)
> >  );
>
> > Here is my ContactMapper declaration:
>
> >        final String INSERT = "INSERT INTO CONTACT (EMAIL, LASTNAME,
> > FIRSTNAME, PHONE) "
> >                        + "VALUES (#{email}, #{lastName}, #{firstName}, #{phone}) RETURNING
> > id";
>
> >        @Insert(INSERT)
> >        @Options(keyProperty = "id", useGeneratedKeys = true, flushCache =
> > true)
> >        Integer insert(Contact contact);
>
> > And here is where I'm invoking it:
>
> >                        Contact newContact = new Contact();
> >                        newContact.setFirstName("Josh");
> >                        newContact.setLastName("Pacer");
> >                        int id = mapper.insert(newContact);
>
> > But I get the following exception:
> >   org.postgresql.util.PSQLException: ERROR: syntax error at or near
> > "RETURNING"
>
> > I don't have this problem when issuing the statement in psql.
>
> > MyBatis: 3.1.0
> > Driver: postgresql-9.1-901.jdbc4.jar driver:
> > Postgres Server: 9.1.3
>
> > Thanks for any insight