将db导入为文本文件 [英] importing db as text files

问题描述

导入文本文件有什么大不了的？我有一个70 MB的文件要导入

，这是一个接一个的问题。我使用了复制命令，看起来好像是b $ b，这是不可能的。我终于将文件按摩到.sql文件中并使用\i db.sql运行了
但是也失败了因为我忽略了''名字中的''
就像D' Adario。我遇到的另一个问题是数字字段有
来包含数据，pg不会默认为默认值。因此，我没有再次按摩所有数据，而是决定更改该列的数据类型。

这是我第一次使用postgresql，我想知道是否我应该期待

遇到类似的痛苦，因为我会进一步深入研究这个问题？到目前为止，我一直非常痛苦地尝试做我认为很容易的事情以及我认为应该很容易的事情。



PostgreSQL 7.3.4 on linux redhat 9

---------------------------（end of广播）---------------------------

提示7：别忘了增加你的可用空间地图设置

What''s the big deal with importing text files? I have a 70 MB file to import
and it''s been one problem after another. I used the copy command and it appears
that it''s just not possible. I finally massaged the file into a .sql file and
ran that using \i db.sql but that failed too because I overlooked '' in names
like D''Adario. The other problem I encountered was that a numeric field had
to have data in it, pg would not default to the default value. So instead of
massaging all the data again I decided to change the data type for that column.
This is my first experience with postgresql and I''m wondering if I should expect
to encounter similar pain as I go further into this? So far it''s been very
painful trying to do what I thought would be easy and what I think should be
easy.

PostgreSQL 7.3.4 on linux redhat 9
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings

推荐答案

2003年8月13日星期三14:14:20 -0700，

expect< ex **** @ ihubbell.com>写道：

On Wed, Aug 13, 2003 at 14:14:20 -0700,
expect <ex****@ihubbell.com> wrote:

导入文本文件有什么大不了的？我有一个70 MB的文件要导入
，这是一个接一个的问题。我使用了复制命令，看起来它是不可能的。我最终将文件按摩到.sql文件和


这不太可能，但没有更多细节，很难说你需要什么来做b $ b。

使用\i db.sql运行，但也失败了，因为我忽略了''这样的名字，比如D''Adario。我遇到的另一个问题是数字字段中有数据，pg不会默认为默认值。因此，而不是


您可以在insert语句中使用关键字default来获取默认值。

再次按摩所有数据我决定更改数据类型对于那一栏。
这是我第一次使用postgresql，我想知道我是否应该期待
遇到类似的痛苦，因为我会进一步探讨这个问题？到目前为止，尝试做我认为容易的事情以及我认为应该容易的事情是非常痛苦的。

What''s the big deal with importing text files? I have a 70 MB file to import
and it''s been one problem after another. I used the copy command and it appears
that it''s just not possible. I finally massaged the file into a .sql file and
That is unlikely, but without more details it is hard to say what you need
to do.
ran that using \i db.sql but that failed too because I overlooked '' in names
like D''Adario. The other problem I encountered was that a numeric field had
to have data in it, pg would not default to the default value. So instead of
You can use the keyword default in insert statements to get a default value.
massaging all the data again I decided to change the data type for that column.
This is my first experience with postgresql and I''m wondering if I should expect
to encounter similar pain as I go further into this? So far it''s been very
painful trying to do what I thought would be easy and what I think should be
easy.

我得到的印象是，当

出现含糊不清的数据时，你希望postgres做出最佳猜测。这是一件非常危险的事情。我希望
更喜欢模糊的数据被拒绝，这样我就可以确定我认为值是什么，这与数据库认为的价值相同。 br />

---------------------------（广播结束）------- --------------------

提示6：您是否搜索了我们的列表档案？

http://archives.postgresql.org

The impression I get is that you expect postgres to make a best guess when
presented with ambiguous data. That is a very dangerous thing to do. I would
much prefer ambiguous data be rejected so that I can make sure what I think
the value is, is the same as what the database thinks the value is.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

嗨期待，


我认为最好的方法是使用copy table命令。这样Pg将实际上按摩数据（字符串逃逸和全部）给你。


如果你有最好的复杂文件使用sed / awk组合预先按摩

字段分隔符并将数据导入临时表。在大多数情况下，最好使用stdin中的复制表将数据传递给psql命令

从文件中要求您是流程所有者。


总是使用临时导入表并在该表（IMO）中执行验证/进一步转换

。


例如（来自我以前的一篇文章）


cat unprocesseddata.txt | sed" s / \"，\" /

Hi expect,

Best way in my opinion is to use the copy table command. This way Pg will
actually ''massage'' the data (string escapes and all) for you.

If you have complex files best to use a combo of sed/awk to pre-massage the
field delimiters and import the data to a temporary table. In most instances
it is best to pipe the data to the psql command using copy table from stdin
as from file requires that you are the process owner.

Always use a temporary import table and perform validation/further conversion
in that table (IMO).

eg (from one of my earlier posts)

cat unprocesseddata.txt | sed "s/\",\"/

TAB / g" | seds / \" // g" | seds /.

TAB/g" | sed "s/\"//g" | sed "s/.

这篇关于将db导入为文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！