复制带有 ID 列的 CSV 文件
我有一个 Rails 应用程序，我正在尝试通过 PostgreSQL 的 COPY 命令将数据加载到其中。该文件是 CSV。该表映射到 Rails 模型，我需要保留主键 ID 列，以便我可以使用 model.for_each 来处理数据——该表将有 5M+ 行。
我的问题是，如何构建 CSV 以便将数据加载到表中并仍然允许 ID 列存在？这很糟糕，因为我需要它的唯一原因是 for_each 方法。
无论如何，我尝试发送 NULL，如下所示：
NULL,col1,col2,col3,col4, etc.
但它不起作用。
谢谢。
I have a rails application and I am trying to load data into it via PostgreSQL's COPY command. The file is CSV. The table maps to a Rails model and I need to retain the primary-key ID column so I can use model.for_each to play with the data--the table will have 5M+ rows.
My question is, how can I structure my CSV so that I can load data into the table and still allow the ID column to be there? It's a bummer because the only reason I need it is for the for_each method.
Regardless, I tried sending NULLs, as in:
NULL,col1,col2,col3,col4, etc.
But it doesn't work.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
无论您为空字符串设置什么选项，为主键传递空值都不会起作用。您将告诉后端将主键设置为空，无论要插入的命令是什么，它都永远不会允许。
我真的不知道你所说的保留主键是什么意思。这是无论做什么都会保留的东西。如果您的意思是让数据库为您选择值并且主键是序列（自动增量），则显式命名除主键之外的所有列：
阅读有关您需要哪些空字符串选项的文档也可能会更快喜欢使用而不是猜测可能的值：
http://www.postgresql。 org/docs/9.0/static/sql-copy.html
Passing in null for the primary key will never work no matter what options you have set for null string. You would be telling the backend to set the primary key to null which it will never allow no matter what the command to insert might be.
I really have no idea what you mean by retaining the primary key. That is something that is going be retained no matter what do. If you mean letting the DB pick the value for you and the primary key is a serial (auto-increment) then explicitly name all the columns but the primary key:
It also might be quicker to read the documentation on what null string options you would like to use instead of guessing possible values:
http://www.postgresql.org/docs/9.0/static/sql-copy.html
使用WITH CSV时的默认值是不带引号的空字符串，例如：
这将创建如下所示的记录：
The default when using WITH CSV is an unquoted empty string, such as:
Which would create a record that looks like: