invalid single-column insert syntax

[TravisCardwell]

I am using PostgreSQL and have a table with a SERIAL primary key and only one other column. Example:

CREATE TABLE HRR.example (
    id SERIAL,
    value VARCHAR(32) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (value)
);

One can insert into the table with an insert statement as follows:

INSERT INTO HRR.example (value) VALUES ('test');

Implementing this in HRR, my usual method of using a Pi does not work:

data Example' = Example' ByteString

$(makeRecordPersistableDefault ''Example')

piExample' :: Pi Example Example'
piExample' = Example' |$| Example.value'

insertExample' :: Insert Example'
insertExample' = typedInsert Example.tableOfExample piExample'

The SQL generated for the insert is as follows:

INSERT INTO HRR.example value VALUES $?;

This results in an SqlError because parenthesis are required both for the list of columns and the list of values, even when there is only one:

http://www.postgresql.org/docs/9.1/static/sql-insert.html

Investigating, I see that insertSQL formats the list of columns in insertPrefixSQL and the list of values in insertChunkSQL, both using rowStringSQL, which only calls paren when there is more than one value.

---

I just created a test and confirmed that this issue also affects insertion into tables of one column, without using a Pi.

Table:

CREATE TABLE HRR.example (
    value VARCHAR(32), 
    PRIMARY KEY (value)
);

Code:

insertExample :: ByteString -> IO Bool
insertExample val = withConnectionIO connect $ \ conn -> do
    rv <- runInsert conn Example.insertExample $ Example val
    commit conn
    return $ rv == 1

Error:

SqlError {seState = "42601", seNativeError = 7, seErrorMsg = "execute:
PGRES_FATAL_ERROR: ERROR:  syntax error at or near \"value\"\n
LINE 1: INSERT INTO HRR.example value VALUES $1\n                                   ^\n"}

[igrep]

I got stuck with the same error. You can reproduce it on my repository's this commit: ttps://github.com/igrep/office-climate/commit/6e2e8ef086890761fac842ffb68db7b0f73f69c8 https://github.com/igrep/office-climate/tree/059ef562278fcadd00cc8185e634129c8878f69c

$ psql -f db/structure.sql
$ cabal repl src/OfficeClimate/User.hs
...
GHCi, version 7.8.4: http://www.haskell.org/ghc/  :? for help

...

[*OfficeClimate]
> :m +Database.HDBC.Session
[*OfficeClimate Database.HDBC.Session]
> :m +OfficeClimate.User
[*OfficeClimate Database.HDBC.Session OfficeClimate.User]
> :m +OfficeClimate.Connection
[*OfficeClimate Database.HDBC.Session OfficeClimate.User OfficeClimate.Connection]
> withConnectionIO' connect $ \c -> create c "name"
*** Exception: user error (SQL error:
  seState: '42601'
  seNativeError: 7
  seErrorMsg: 'execute: PGRES_FATAL_ERROR: ERROR:
  "name"またはその近辺で構文エラー
  LINE 1: INSERT INTO OFFICE_CLIMATE.user name VALUES $1
                                             ^
                                              '
                                               )

[debug-ito]

This affects SQLite, too.

Table definition:

CREATE TABLE IF NOT EXISTS sample (
  val TEXT NOT NULL PRIMARY KEY
);

Code:

insertVal :: Insert String
insertVal = typedInsert Sample.tableOfSample Sample.val'

main = do
  conn <- connectSqlite3 "sample.db"
  withTransaction conn $ \conn -> do
    runInsert conn insertVal "foobar"

Result:

Test: SqlError {seState = "", seNativeError = 1, seErrorMsg = "prepare 37: INSERT INTO MAIN.sample val VALUES ?: near \"val\": syntax error"}

[debug-ito]

seems like this bug is corrected in relational-query-0.5.0.0. Thanks a lot.

[khibino]

Fix this problem in 64ddb50 and apply this fix relational-query-0.5.0.0 . Thanks for these reports!