PostgreSQL file import to table eror-CodePudding

I wanted to transfer the file I created for testing to the postgresql file, and I did this with the command:psql -h localhost -d test -U postgres -f C:\test1.sql it gave me an error like this:

`ERROR: Syntax error in place " ' "
LINE 1: INSERT INTO `test1` (`idcustomer`, `testid`, `customername`, `custlastname`, `birthid...`

MY CODE:

`-- phpMyAdmin SQL 
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 22, 2022 at 04:31 PM
-- Server version: 10.4.25-MariaDB
-- PHP Version: 8.1.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = " 00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `data`
--

-- --------------------------------------------------------

--
-- Table structure for table `test1`
--

CREATE TABLE `test1` (
  `idcustomer` int(5) NOT NULL,
  `testid` varchar(250) DEFAULT NULL,
  `customername` varchar(250) DEFAULT NULL,
  `custlastname` varchar(250) DEFAULT NULL,
  `birthid` varchar(250) DEFAULT NULL,
  `name` varchar(250) DEFAULT NULL,
  `namee` varchar(250) DEFAULT NULL,
  `named` varchar(250) DEFAULT NULL,
  `phonenumber` varchar(250) DEFAULT NULL,
  `testname` varchar(250) DEFAULT NULL,
  `testnumber` varchar(250) DEFAULT NULL,
  `testnamee` varchar(250) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
--  data for table `test1`
--

INSERT INTO 'test1' (`idcustomer`, `testid`, `customername`, `custlastname`, `birthid`, `name`, `namee`, `named`, `phonenumber`, `testname`, `testnumber`, `testnamee`) VALUES
(1, '45645', 'jack', 'xxx', '151', 'nameeeee', 'testtt', 'xname', '585', 'xdname', '985', 'xs'),
(2, '44524', 'adam', 'testt', '525', 'nameee', 'testttt', 'yname', '568', 'xdname', '854', 'xb'),`

how can i import this file into postgresql? I'm crazy from getting such error.

how can i solve this problem?

CodePudding user response：

Well, In MySQL, name seems to be a reserved word because MySQL needs them and uses a yacc parser.

Ref: https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-N:~:text=N-,NAME,-NAMES

UPDATE: just try renaming column name called name to something else like mainname in CREATE and INSERT both of queries in your code

CodePudding user response：

Rules to convert such a script:

remove the SET commands at the beginning
remove all `
remove ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
remove the "display hint" for integer columns (int(x) --> int)
don't use single quotes for table names 'test1' -> test1
read the PostgreSQL manual

CREATE TABLE test1 (
  idcustomer int NOT NULL,
  testid varchar(250) DEFAULT NULL,
  customername varchar(250) DEFAULT NULL,
  custlastname varchar(250) DEFAULT NULL,
  birthid varchar(250) DEFAULT NULL,
  name varchar(250) DEFAULT NULL,
  namee varchar(250) DEFAULT NULL,
  named varchar(250) DEFAULT NULL,
  phonenumber varchar(250) DEFAULT NULL,
  testname varchar(250) DEFAULT NULL,
  testnumber varchar(250) DEFAULT NULL,
  testnamee varchar(250) DEFAULT NULL
);

INSERT INTO test1 (idcustomer, testid, customername, custlastname, birthid, name, namee, named, phonenumber, testname, testnumber, testnamee) VALUES
(1, '45645', 'jack', 'xxx', '151', 'nameeeee', 'testtt', 'xname', '585', 'xdname', '985', 'xs'),
(2, '44524', 'adam', 'testt', '525', 'nameee', 'testttt', 'yname', '568', 'xdname', '854', 'xb'); --<< replace , with ;