SQL import into PostgreSQL 8.4.20 fails with syntax error

Question

I exported SQL file with data from existing Mysql database. Now I am trying to import it in PostgrSQL database.

I tried to use phpPgAdmin import field. I executed this SQL because I have already created my tables.

INSERT INTO `categories` (`id`, `name`, `created_at`, `updated_at`, `ancestry`, `ancestry_depth`, `priority`) VALUES
(1, 'Treileri', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(2, 'Kravas automašīnas', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(3, 'Teleskopiskie iekrāvēji', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(4, 'Ekskavatori', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(5, 'Iekrāvēji', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(6, 'Pacēlāji', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(7, 'Elektroiekārtas', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(8, 'Ģeneratori', '2014-09-24 17:45:02', '2014-09-24 17:45:02', '7', 1, NULL),
(9, 'Strāvas kārbas', '2014-09-24 17:45:02', '2014-09-24 17:45:02', '7', 1, NULL),
(10, 'Aprīkojums', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(11, 'Vibro blietes', '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(12, 'Ūdens pumpji', '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(13, 'Vinčas', '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(14, 'Gāzes sildītāji', '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(15, 'Plātņu satvērēji', '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(16, 'Celtniecības sastatnes', '2014-09-24 17:45:03', '2014-09-24 17:45:03', NULL, 0, NULL),
(17, 'Citi', '2014-09-24 17:45:03', '2014-09-24 17:45:03', NULL, 0, NULL);

Error:

SQL error:

ERROR:  syntax error at or near "INTO"
LINE 1: SELECT COUNT(*) AS total FROM (INSERT INTO `categories` (`id...
                                              ^

I checked SQL validity here and there was no errors found. I don't understand where is the issue ?

Maybe PostgreSQL version problems?

Based on your suggestions I changed my code to :

INSERT INTO "categories" ("id", "name", "created_at", "updated_at", "ancestry", "ancestry_depth", "priority") VALUES
(1, "Treileri", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(2, "Kravas automašīnas", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(3, "Teleskopiskie iekrāvēji", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(4, "Ekskavatori", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(5, "Iekrāvēji", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(6, "Pacēlāji", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(7, "Elektroiekārtas", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(8, "Ģeneratori", '2014-09-24 17:45:02', '2014-09-24 17:45:02', '7', 1, NULL),
(9, "Strāvas kārbas", '2014-09-24 17:45:02', '2014-09-24 17:45:02', '7', 1, NULL),
(10, "Aprīkojums", '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(11, "Vibro blietes", '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(12, "Ūdens pumpji", '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(13, "Vinčas", '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(14, "Gāzes sildītāji", '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(15, "Plātņu satvērēji", '2014-09-24 17:45:03', '2014-09-24 17:45:03', '10', 1, NULL),
(16, "Celtniecības sastatnes", '2014-09-24 17:45:03', '2014-09-24 17:45:03', NULL, 0, NULL),
(17, "Citi", '2014-09-24 17:45:03', '2014-09-24 17:45:03', NULL, 0, NULL);

Still the same error.

Thanks.

Yeah, you don't need the backticks at all. In some (rare) cases you might need double quotes in postgres. Mysql and SQLite's use of backticks is nonstandard. See this previous post: http://stackoverflow.com/questions/10573922/what-does-the-sql-standard-say-about-usage-of-backtick — beerbajay, Feb 01 '15 at 20:34

score 0 · Accepted Answer · answered Feb 01 '15 at 22:05

Character literals need to be enclosed in single quotes, not double quotes. Double quotes are for identifiers. "Treileri" is an identifier (e.g. a column name), 'Treileri' is a character literal.

So you need to replace all double quotes for the values with single quotes:

INSERT INTO "categories" ("id", "name", "created_at", "updated_at", "ancestry", "ancestry_depth", "priority") VALUES
(1, 'Treileri', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
(2, 'Kravas automašīnas', '2014-09-24 17:45:02', '2014-09-24 17:45:02', NULL, 0, NULL),
.....

More details about identifiers and literals can be found in the manual:

Identifiers: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
Character literals: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Working SQLFiddle example: http://sqlfiddle.com/#!15/87c23/1

If this is a new installation you should not use a version that is no longer maintained (=supported). 8.4 is really old. For a new installation you should go for 9.3 or 9.4

SQL import into PostgreSQL 8.4.20 fails with syntax error

1 Answers1