blocks|key|1964517|text|Postgres使用\N作为空值的替代符号。但是，所有psql命令都以反斜杠\符号开头。当复制语句失败时，您可以获得这些消息，但转储的加载仍在继续。这条消息是假警报。如果要查看复制语句失败的真正原因，则必须在此错误之前搜索所有行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1964518|可以将psql切换为“在第一个错误上停止”模式并查找错误：|1964519|psql+-v+ON_ERROR_STOP=1|code-block|syntax|javascript|1964520|entityMap^0|A|2|12|1|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|U|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Postgres uses <code>\N</code> as substitute symbol for NULL value. But all psql commands start with a backslash <code>\</code> symbol. You can get these messages, when a copy statement fails, but the loading of dump continues. This message is a false alarm. You have to search all lines prior to this error if you want to see the real reason why COPY statement failed.
Is possible to switch psql to &quot;stop on first error&quot; mode and to find error:
<pre><code>psql -v ON_ERROR_STOP=1
</code></pre>

blocks|key|1964545|text|在尝试从二进制pg_dump恢复时，我收到了相同的错误消息。我只是使用pg_restore来恢复我的转储，并且完全避免了\N错误。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1964546|pg_restore+-c+-F+t+-f+your.backup.tar|1964547|开关的解释：|1964548|-f,+--file=FILENAME++++++output+file+name
-F,+--format=c%7Cd%7Ct+++++++backup+file+format+(should+be+automatic)
-c,+--clean++++++++++++++clean+(drop)+database+objects+before+recreating|code-block|syntax|javascript|1964549|entityMap^0|Z|A|1O|2|0|0|11|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|Y|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|Z|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

I received the same error message when trying to restore from a binary pg_dump. I simply used <code>pg_restore</code> to restore my dump and completely avoid the <code>\N</code> errors, e.g.
<code>pg_restore -c -F t -f your.backup.tar</code>
Explanation of switches:
<pre><code>-f, --file=FILENAME output file name
-F, --format=c|d|t backup file format (should be automatic)
-c, --clean clean (drop) database objects before recreating
</code></pre>

blocks|key|1995017|text|我知道这是一个旧帖子，但我遇到了另一个解决方案:+postgis没有安装在我的新版本上，这导致了pg_dump上同样的错误|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1995018|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I know this is an old post but I came across another solution : postgis wasn't installed on my new version, which caused me the same error on pg_dump

blocks|key|995475|text|我过去也遇到过这个错误。帕维尔是正确的，这通常是一个迹象，在脚本中创建的pg_restore正在失败。由于所有的"/N“错误，您没有在输出的最上面看到真正的问题。我建议：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995476|插入一个小表(例如，pg_restore+--table=orders+full_database.dump+>+orders.dump+)|ordered-list-item|offset|length|style|CODE|995477|如果没有小记录，那么从还原脚本中删除一堆记录--我只是确保./是加载的最后一行(例如，打开orders.dump并删除一堆记录)|995478|查看标准输出，一旦发现问题，就可以随时删除表并重新加载。|995479|995480|995481|在我的例子中，我还没有安装"hstore“扩展，所以脚本在最上面失败了。我在目标数据库上安装了hstore，然后我又重新开始工作了。|995482|entityMap^0|0|A|1M|0|19|B|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@$E|U|F|V|G|H]]|9|@]|A|$]]|$1|I|3|J|5|D|7|W|8|@$E|X|F|Y|G|H]]|9|@]|A|$]]|$1|K|3|L|5|D|7|Z|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

I have run into this error in the past as well. Pavel is correct, it is usually a sign that something in the script created by pg_restore is failing. Because of all the "/N" errors, you aren't seeing the real problem at the very top of the output. I suggest:

<ol>
<li>inserting a single, small table (e.g., <code>pg_restore
--table=orders full_database.dump &gt; orders.dump</code> ) </li>
<li>if you don't have a small one, then delete a bunch of records out of the restore script - I just made sure the ./ was the last row to be loaded (e.g., open <code>orders.dump</code> and delete a bunch of records)</li>
<li>watch the standard output, and once you find the problem, you can always drop
the table and reload</li>
</ol>

In my case, I didn't have the "hstore" extension installed yet, so the script was failing at the very top. I installed hstore on the destination database, and I was back in business.

blocks|key|1964618|text|您可以使用INSERTS语句生成转储，并使用--inserts参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1964619|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can generate your dump using INSERTS statements, with the --inserts parameter.

blocks|key|1995088|text|今天我也发生了同样的事。我用--插入命令来处理问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1995089|我所做的是：|1995090|1)插入的pg_dump：|1995091|pg_dump+dbname+--username=usernamehere+--password+--no-owner+--no-privileges+--data-only+--inserts+-t+'schema."Table"'+>+filename.sql|code-block|syntax|javascript|1995092|2)+psql+(恢复转储文件)|1995093|psql+"dbname=dbnamehere+options=--search_path=schemaname"+--host+hostnamehere+--username=usernamehere+-f+filename.sql+>&+outputfile.txt|1995094|注意-1)确保添加输出文件将提高导入速度。|1995095|注意-2)在用psql导入之前，不要忘记使用完全相同的名称和列创建表。|1995096|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Same thing was happened to me today. I handled issue by dumping with --inserts command. 

What I do is: 

1) pg_dump with inserts:

<pre><code>pg_dump dbname --username=usernamehere --password --no-owner --no-privileges --data-only --inserts -t 'schema."Table"' &gt; filename.sql
</code></pre>

2) psql (restore your dumped file)

<pre><code>psql "dbname=dbnamehere options=--search_path=schemaname" --host hostnamehere --username=usernamehere -f filename.sql &gt;&amp; outputfile.txt
</code></pre>

Note-1 ) Make sure that adding outputfile will increase speed of import.

Note-2 ) Do not forget to create table with exact same name and columns before importing with psql.

blocks|key|995558|text|安装postgresql-(您的版本)-postgis-脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995559|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Install postgresql-(your version)-postgis-scripts

blocks|key|1994969|text|根据我最近的经验，当真正的问题与转义字符或换行符无关时，就有可能得到这个错误。在我的例子中，我从数据库A创建了一个转储|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1994970|pg_dump+-a+-t+table_name+>+dump.sql|offset|length|style|CODE|1994971|并试图将其还原到数据库B中。|1994972|psql+<+dump.sql+(当然，在更新正确的env之后)|1994973|最后，我发现转储是模式特定的，尽管它是data-only+(+-a选项，因此表结构不是转储的显式部分)。这意味着，如果不手动修改转储，我就不能使用schema1.table_name生成的转储来填充schema2.table_name。手动修改转储很容易，模式在前15行左右指定。|1994974|entityMap^0|0|0|Z|0|0|0|F|0|J|9|V|2|21|I|2R|I|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]|$D|12|E|13|F|G]|$D|14|E|15|F|G]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|O|$]]

In my recent experience, it's possible to get this error when the real problem has nothing to do with escape characters or newlines. In my case, I had created a dump from database A with 
<code>pg_dump -a -t table_name &gt; dump.sql</code> 
and was trying to restore it to database B with 
<code>psql &lt; dump.sql</code> (after updating the proper env vars, of course) 
What I finally figured out was that the dump, though it was <code>data-only</code> (the <code>-a</code> option, so that the table structure isn't explicitly part of the dump), was schema-specific. That meant that without manually modifying the dump, I couldn't use a dump generated from <code>schema1.table_name</code> to populate <code>schema2.table_name</code>. Manually modifying the dump was easy, the schema is specified in the first 15 lines or so.

blocks|key|1964661|text|大多数情况下，解决方案是安装postgres-contrib包。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1964662|entityMap^0|E|G|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Most times, the solution is to install <code>postgres-contrib</code> package.

blocks|key|998816|text|我的解决办法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|998817|psql+-U+your_user+your_db+<+your.file.here.sql++2>&1%7Cmore|code-block|syntax|javascript|998818|这样我就可以读取错误消息了。|998819|希望这对任何人都有帮助。|998820|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

My solution was this:
<pre><code>psql -U your_user your_db &lt; your.file.here.sql 2&gt;&amp;1|more
</code></pre>
this way I could read the error message
I hope this helps anybody.

blocks|key|1964729|text|我也遇到了同样的问题，我创建了一个新的数据库，并在使用psql进行恢复时获得了invalid+command+\N。我通过设置与旧数据库相同的表空间来解决这个问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1964730|例如，旧的数据库备份有表空间"pg_default"，我为新数据库定义了相同的表空间，而上面的错误已经消失了！|1964731|entityMap^0|13|I|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

I had the same problem, I created a new database and got <code>invalid command \N</code> on restore with psql.
I solved it by setting the same tablespace with the old database.

For example, old database backup had tablespace "pg_default", I defined the same tablespace to the new database, and the above error has gone!

blocks|key|998766|text|对于我在SUSE+12上使用postgreSQL+10，我通过增加磁盘空间来解决invalid+command+\N错误。磁盘空间的缺乏给我造成了错误。如果查看df+-h输出中的数据所在的文件系统，您可以判断是否没有磁盘空间。如果文件系统/挂载的使用率为100%25，那么在执行psql+-f+db.out+postgres+(参见https://www.postgresql.org/docs/current/static/app-pg-dumpall.html)之类的操作后，您可能需要增加可用的磁盘空间。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|998767|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/app-pg-dumpall.html^0|14|I|28|5|3T|N|4K|1U|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

For me using postgreSQL 10 on SUSE 12, I resolved the <code>invalid command \N</code> error by increasing disk space. Lack of disk space was causing the error for me. You can tell if you are out of disk space if you look at the file system your data is going to in the <code>df -h</code> output. If file system/mount is at 100% used, after doing something like <code>psql -f db.out postgres</code> (see <a href="https://www.postgresql.org/docs/current/static/app-pg-dumpall.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/current/static/app-pg-dumpall.html</a>) you likely need to increase the disk space available.

blocks|key|1964755|text|我遵循了所有这些示例，它们都失败了，出现了我们正在讨论的错误：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1964756|Copy+a+table+from+one+database+to+another+in+Postgres|offset|length|1964757|成功的是-C的语法，请参阅这里：|style|BOLD|1964758|pg_dump+-C+-t+tableName+"postgres://$User:$Password@$Host:$Port/$DBName"+%7C+psql+"postgres://$User:$Password@$Host:$Port/$DBName"|code-block|syntax|javascript|1964759|此外，如果两者之间有不同的模式，我发现修改一个dB的模式以匹配其他的模式是表副本工作所必需的，例如：
|blockquote|1964760|1964761|DROP+SCHEMA+public;
ALTER+SCHEMA+originalDBSchema+RENAME+TO+public;|1964762|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3195125/copy-a-table-from-one-database-to-another-in-postgres^0|0|0|1H|0|0|4|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|F|3|G|5|6|7|17|8|@$D|18|E|19|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|1A|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|Q|7|1B|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|S|3|T|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

I followed all these example's and they all failed with the error we are talking about:
<a href="https://stackoverflow.com/questions/3195125/copy-a-table-from-one-database-to-another-in-postgres">Copy a table from one database to another in Postgres</a>
What worked was the syntax with -C, see here:
<pre><code>pg_dump -C -t tableName &quot;postgres://$User:$Password@$Host:$Port/$DBName&quot; | psql &quot;postgres://$User:$Password@$Host:$Port/$DBName&quot;
</code></pre>
<blockquote>
Also if there are differing Schema's between the two, I find altering one dB's schema to match the others is necessary for Table copies to work, eg:
</blockquote>
<pre><code>DROP SCHEMA public;
ALTER SCHEMA originalDBSchema RENAME TO public;
</code></pre>

blocks|key|1964817|text|对我来说，与源数据库不同的是编码和区域设置。一旦我放弃目标DB并重新创建它，它就正常工作了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1964818|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For me it was the ENCODING and LOCALE that differ from the source database.
Once I dropped the target DB and recreated it it was working fine.

blocks|key|998844|text|在我的例子中，问题在于我的目标机器上缺少磁盘空间。只需增加本地存储空间就能帮我解决问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|998845|希望这对某人有帮助;)|998846|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

In my case the problem was a lack of disk space on my target machine. Simply increasing the local storage fixed it for me.
Hope this helps someone ;)

blocks|key|998848|text|加上我的决心，以防万一对任何人都有帮助。我安装了postgis，但错误没有解决。--插入选项是不可行的，因为我必须复制一个包含数千行表的大模式。对于同一个数据库，当pg_dump和psql+(还原)在mac上运行时，我没有看到这个问题。但是，当pg_dump在linux机器上运行时，问题就出现了，转储文件被复制到mac并试图恢复。所以我在VSCode中打开了转储文件。它检测到不寻常的线路终端机，并给出了移除它们的选项。完成此操作后，转储文件还原将运行，而不会出现无效命令\N错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|998849|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Adding my resolution, incase it helps anyone. I installed postgis but the error wasn't resolved. The --inserts option was not feasible as I had to copy a big schema having tables with thousands of rows. For the same database I didn't see this issue when pg_dump and psql (restore) were run on mac. But the issue came when pg_dump was run on linux machine, the dump file copied to mac and tried for restore. So I opened the dump file in VSCode. It detected unusual line terminators and gave option to remove them. After doing that the dump file restore ran without the invalid command \N errors.

blocks|key|998851|text|检查表中的列和备份文件中的列是否合适|type|unstyled|depth|inlineStyleRanges|entityRanges|data|998852|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

check that the columns in the table and the columns in the backup file suitable

I'm trying to restore my dump file, but it caused an error:

<pre><code>psql:psit.sql:27485: invalid command \N
</code></pre>

Is there a solution? I searched, but I didn't get a clear answer.

psql invalid command \N while restore sql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我正在尝试恢复我的转储文件，但是它导致了一个错误：psql:psit.sql:27485: invalid command \N有解决办法吗？我搜索了，但没有得到明确的答案。

问还原sql时psql无效命令\N
EN

回答 17

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问还原sql时psql无效命令\NEN

回答 17

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问还原sql时psql无效命令\N
EN