为什么CSVREAD应该从CSV文件中读取列名时无法按预期工作？
根据 h2文档 for csvread
如果指定了列的名称（用fieldSeparator分隔的列名列表），则使用这些名称（或者将其设置为null）将文件的第一行解释为列名称。
我希望阅读
id,name,label,origin,destination,length
81,foobar,,19,11,27.4
这样的
insert into route select * from csvread ('routes.csv',null,'charset=UTF-8')
CSV文件会起作用。但是，实际上抛出了JDBCSQLINTEGRITYCONDERTINCONTEXCEPTION，说null不允许列“ Origin”，并指示错误代码23502。
如果我明确地将列名添加到类似的插入语句中，
insert into route (id,name,label,origin,destination,length) select * from csvread ('routes.csv',null,'charset=UTF-8')
则可以正常工作。但是，我宁愿不重复自己 - 遵循干燥原则：)
使用2.1.212版。
According to the H2 documentation for CSVREAD
If the column names are specified (a list of column names separated with the fieldSeparator), those are used, otherwise (or if they are set to NULL) the first line of the file is interpreted as the column names.
I'd expect reading the csv file
id,name,label,origin,destination,length
81,foobar,,19,11,27.4
like this
insert into route select * from csvread ('routes.csv',null,'charset=UTF-8')
would work. However, actually a JdbcSQLIntegrityConstraintViolationException is thrown, saying NULL not allowed for column "ORIGIN" and indicating error code 23502.
If I explicitly add the column names to the insert statement like so,
insert into route (id,name,label,origin,destination,length) select * from csvread ('routes.csv',null,'charset=UTF-8')
it works fine. However, I'd prefer not to repeat myself - following the DRY principle :)
Using version 2.1.212.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
csvread函数生成虚拟表。它的列名可以在参数或CSV文件中指定。insert带有查询的命令未使用目标表的列名映射此查询的列名，而是使用其序列位置。从查询的第一列中的值插入插入列列表中指定的第一列或目标表的第一列，如果未指定插入列列表，则将第二列插入第二列，依此类推。您只能以与源查询相同的顺序定义表格（在CSV文件中的情况）定义相同的列时，才可以省略插入列列表。如果您的表的列以不同的顺序声明或有一些额外的列，则需要指定此列表。
The
CSVREADfunction produces a virtual table. Its column names can be specified in parameters or in the CSV file.INSERTcommand with a query doesn't map column names from this query with column names of target table, it uses their ordinal positions instead. Value from the first column of the query is inserted into first column specified in insert column list or into first column of target table if insert column list isn't specified, the second is inserted into second column, and so on.You can omit insert column list only if your table was defined with the same columns in the same order as in the source query (is your case in the CSV file). If your table has columns declared in different order or it has some additional columns, you need to specify this list.