psql invalid command \N while restore sql

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

What I do is:

1) pg_dump with inserts:

pg_dump dbname --username=usernamehere --password --no-owner --no-privileges --data-only --inserts -t 'schema."Table"' > filename.sql

2) psql (restore your dumped file)

psql "dbname=dbnamehere options=--search_path=schemaname" --host hostnamehere --username=usernamehere -f filename.sql >& outputfile.txt

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.

Most times, the solution is to install postgres-contrib package.

My solution was this:

psql -U your_user your_db < your.file.here.sql  2>&1|more

this way I could read the error message

I hope this helps anybody.

I had the same problem, I created a new database and got invalid command \N 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!

I followed all these example's and they all failed with the error we are talking about:

Copy a table from one database to another in Postgres

What worked was the syntax with -C, see here:

pg_dump -C -t tableName "postgres://$User:$Password@$Host:$Port/$DBName" | psql "postgres://$User:$Password@$Host:$Port/$DBName"

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:

DROP SCHEMA public;
ALTER SCHEMA originalDBSchema RENAME TO public;

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.

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 ;)

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.

The @farshid-ashuri's answer works fine for me.

I'm using Postgres 14 on Almalinux and messages like its appear. After installing the postgresql14-contrib package, I restored a full dump and no more messages.

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