Can't copy table to another database using pg_dump
I am trying to copy a table from one database to another database (NOT). The code I used in terminal is below:
pg_dump -U postgres -t OldSchema.TableToCopy OldDatabase | psql -U postgres -d NewDatabase
When I press Enter, it asks for the postgres password. I enter my password and then psql asks for my password. I enter it and press Enter. I am getting a lot:
invalid command \N
ERROR: relation "TableToCopy" does not exist
Both tables are UTF8 encoded. Am I doing something wrong? OS: Windows XP
Error output:
psql:TblToCopy.sql:39236: invalid command \N
psql:TblToCopy.sql:39237: invalid command \N
psql:TblToCopy.sql:39238: invalid command \N
.
.
.
After hundreds of the above errors, terminal echo:
psql:TblToCopy.sql:39245: ERROR: syntax error at or near "509"
LINE 1: 509 some gibberish words and letters here
Finally:
sql:TblToCopy.sql:39245: ERROR: relation "TableToCopy" does not exist
EDIT I read this answer on the same problem \ N error with psql , it says that instead of INSERT, instead of COPY, it uses INSERT, but COPY is generated in the pg_dump file. How do I tell pg_dump to use INSERT instead of COPY?
I converted the file with iconv to utf-8. Now this error is gone, but I got a new error. In this particular case, when I use psql to import data into the database, something new happens. The table is created, but no data. It says:
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:tblNew.sql:39610: ERROR: value too long for type character(3)
CONTEXT: COPY words, line 1, column first_two_letters: "سر"
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
source to share
I tried to create a UTF8 encoded database with a table and insert two UTF-8 encoded characters that the COPY command is trying to insert and it works when using INSERT.
CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
CONNECTION LIMIT = -1;
CREATE TABLE x
(
first_two_letters character(3)
)
WITH (
OIDS=FALSE
);
ALTER TABLE x
OWNER TO postgres;
INSERT INTO x(
first_two_letters)
VALUES ('سر');
According to http://rishida.net/tools/conversion/ for a failed COPY the Unicode code points are:
U + 0633 U + 0631
which are two characters , which means you should be able to store them in a column defined as character (3), which stores strings up to 3 characters (not bytes) in length.
and if we try to type INSERT it succeeds:
INSERT INTO x(
first_two_letters)
VALUES (U&'\0633\0631');
From pgdump documentation, you can use INSERT instead of COPY with --inserts option
- Inserts Dump data as INSERT commands (not COPY). This will make recovery very slow; this is mainly useful for creating dumps that can be loaded into databases without PostgreSQL. However, since this parameter generates a separate command for each row, only the row is lost, not the entire contents of the table, when reloading the row. Note that recovery may fail if you reorder the columns. The --column-inserts option is safe for column order changes, albeit slower.
Try using this instead for step 1:
pg_dump -U postgres -t OldSchema."TableToCopy" --inserts OldDatabase > Table.sql
I also tried to use COPY from table to file and use COPY to import and it works for me.
Are you sure your client and server database encoding is UTF8?
First, export the table named "x" from the "public" schema to the "test" database in a SQL text file:
pg_dump -U postgres -t public."x" test > x.sql
which creates an x.sql file containing:
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE x (
first_two_letters character(3)
);
ALTER TABLE public.x OWNER TO postgres;
--
-- Data for Name: x; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY x (first_two_letters) FROM stdin;
سر
\.
--
-- PostgreSQL database dump complete
--
Secondly, import using:
psql -U postgres -d test -f x.sql
source to share
The table name must be listed as following
pg_dump -U postgres -t OldSchema."TableToCopy" OldDatabase | psql -U postgres -d NewDatabase
And I suggest you do this job in two steps
Step 1
pg_dump -U postgres -t OldSchema."TableToCopy" OldDatabase > Table.sql
If step 1 is ok, follow step 2.
Step 2
psql -U postgres -d NewDatabase -f Table.sql
source to share