Question 63866 in PhpMyAdmin Question posted in
The official documentation can be found here.

converting MySQL syntax to PostgreSQL – Phpmyadmin

So I designed a Bank database in MySQL, and now I want to convert some syntax to PostgreSQL
this is my account and branch table :

CREATE TABLE `account` (
  `accountnumber` int(80) NOT NULL,
  `balance` text COLLATE utf8_bin NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `branchID` int(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `branch` (
  `branchID` int(25) NOT NULL,
  `bName` varchar(50) COLLATE utf8_bin NOT NULL,
  `bCity` varchar(50) COLLATE utf8_bin NOT NULL,
  `assests` bigint(20) NOT NULL,
  `mainbankID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I converted it already, but I have problems with these parts:

ALTER TABLE `account`
  ADD PRIMARY KEY (`accountnumber`),
  ADD KEY `branchID` (`branchID`);


ALTER TABLE `account`
  ADD CONSTRAINT `account_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

for example, I tried to write the first one like this, and I got errors:

ALTER TABLE account
  ADD PRIMARY KEY (accountnumber),
  ADD KEY branchID (branchID);

and I got this error :

type "branchid" does not exist

and here’s my piece of code that I converted and working in PostgreSQL :

--
-- Database: `bank`
--

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

--
-- Table structure for table account
--

CREATE TABLE account (
  accountnumber INTEGER NOT NULL,
  balance text  NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  branchID INTEGER NOT NULL
) ;

--
-- Dumping data for table `account`
--

INSERT INTO account (accountnumber, balance, date, time, branchID) VALUES
(1132, 'توضیحات ترازنامه', '2021-07-12', '00:00:03', 516),
(1792, 'توضیحات ترازنامه', '2020-05-29', '11:17:27', 516),
(5130, 'توضیحات ترازنامه', '2016-02-23', '13:18:00', 123),
(7123, 'توضیحات ترازنامه', '2011-11-16', '10:25:00', 124),
(8210, 'توضیحات ترازنامه', '2019-01-11', '16:20:06', 215);

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

--
-- Table structure for table `borrower`
--

CREATE TABLE borrower (
  customerID INTEGER NOT NULL,
  loanID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `borrower`
--

INSERT INTO borrower (customerID, loanID) VALUES
(7951, 357),
(1089, 357);

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

--
-- Table structure for table `branch`
--

CREATE TABLE branch (
  branchID INTEGER NOT NULL,
  bName varchar(50) NOT NULL,
  bCity varchar(50) NOT NULL,
  assests bigint NOT NULL,
  mainbankID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `branch`
--

INSERT INTO branch (branchID, bName, bCity, assests, mainbankID) VALUES
(123, 'سعادت اباد', 'تهران', 250000000, 1111),
(124, 'تجریش', 'همدان ', 5842000, 2222),
(215, 'خیابان قیام', 'یزد', 700000000, 2222),
(391, 'نقش جهان', 'اصفهان', 20100000, 1111),
(516, 'میرداماد', 'تهران', 953200000, 1111);

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

--
-- Table structure for table `customer`
--

CREATE TABLE customer (
  customerID INTEGER NOT NULL,
  CName varchar(78) NOT NULL,
  cphonenumber INTEGER NOT NULL,
  cCity varchar(50) NOT NULL,
  caddress text NOT NULL,
  cage INTEGER NOT NULL
)  ;

--
-- Dumping data for table `customer`
--

INSERT INTO customer (customerID, CName, cphonenumber, cCity, caddress, cage) VALUES
(412, 'معین سپهری', 387496, 'تهران', 'تهران شهرک غرب', 27),
(1089, 'سحر مقدم', 3254896, 'اصفهان', 'اصفهان پل خواجوو', 32),
(7951, 'ددا رضوی', 36232323, 'تهران', 'تهران شهرک اندیشه مجتمع رز', 26),
(463241, 'محمد فلاح', 3456213, 'یزد', 'یزد خیابان کاشانی کوچه علوی', 50);

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

--
-- Table structure for table `depositor`
--

CREATE TABLE depositor (
  customerID INTEGER NOT NULL,
  accountnumber INTEGER NOT NULL
)  ;

--
-- Dumping data for table `depositor`
--

INSERT INTO depositor (customerID, accountnumber) VALUES
(7951, 7123),
(463241, 5130),
(412, 1132),
(463241, 1132),
(1089, 1132);

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

--
-- Table structure for table `employee`
--

CREATE TABLE employee (
  EmployeeID INTEGER NOT NULL,
  Employeework varchar(100) NOT NULL,
  Employeename varchar(25) NOT NULL,
  aphonenumber INTEGER NOT NULL,
  Employmentyear smallint NOT NULL,
  eaddress text NOT NULL,
  esalary INTEGER NOT NULL,
  branchID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `employee`
--

CREATE TABLE mainbank (
  mainbankID INTEGER NOT NULL,
  mainname varchar(25) NOT NULL,
  constructionyear smallint NOT NULL,
  budget INTEGER NOT NULL
)  ;

--
-- Dumping data for table `mainbank`
--

INSERT INTO mainbank (mainbankID, mainname, constructionyear, budget) VALUES
(1111, 'saderat', 1925, 20000000),
(2222, 'melli', 1912, 100000000);

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

--
-- Table structure for table `managment`
--

CREATE TABLE managment (
  managmentID INTEGER NOT NULL,
  managename varchar(25) NOT NULL,
  mphonenumber INTEGER NOT NULL,
  manageaddress text NOT NULL,
  manageage INTEGER NOT NULL,
  manageedu varchar(100) NOT NULL,
  msalary INTEGER NOT NULL,
  employmentyear smallint NOT NULL,
  branchID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `managment`
--

INSERT INTO managment (managmentID, managename, mphonenumber, manageaddress, manageage, manageedu, msalary, employmentyear, branchID) VALUES
(1030, 'akbar fallah', 2146258, 'tehran pol sadr', 59, 'Master of Banking', 2100000000, 2011, 516),
(1046, 'mohammad alavi', 352146, 'yazd blv jomhorii', 63, 'Master of Banking', 18200000, 2016, 215);


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

--
-- Table structure for table `safebox`
--

CREATE TABLE safebox (
  safeboxID INTEGER NOT NULL,
  price INTEGER NOT NULL,
  date date NOT NULL,
  time time(0) NOT NULL,
  branchID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `safebox`
--

INSERT INTO safebox (safeboxID, price, date, time, branchID) VALUES
(162, 75000000, '2020-11-07', '20:03:41', 123),
(888, 28000000, '2020-04-22', '10:00:00', 391),
(1642, 160000, '2019-01-25', '10:25:04', 215);

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

--
-- Table structure for table `servise`
--

CREATE TABLE servise (
  serviseID INTEGER NOT NULL,
  sname varchar(25) NOT NULL,
  Employmentyear smallint NOT NULL,
  sage INTEGER NOT NULL,
  sphone INTEGER NOT NULL,
  saddress text NOT NULL,
  ssalary INTEGER NOT NULL,
  branchID INTEGER NOT NULL
)  ;

--
-- Dumping data for table `servise`
--

INSERT INTO servise (serviseID, sname, Employmentyear, sage, sphone, saddress, ssalary, branchID) VALUES
(13333, 'iman', 1999, 49, 213620, 'tehran andishe', 30000, 516),
(15555, 'hesam', 2015, 38, 216201, 'tehran tajrish', 300000, 124);

and this is my whole code in MySQL:

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 23, 2022 at 11:37 PM
-- Server version: 10.1.38-MariaDB
-- PHP Version: 7.3.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `bank`
--

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

--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `accountnumber` int(80) NOT NULL,
  `balance` text COLLATE utf8_bin NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `branchID` int(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `account`
--

INSERT INTO `account` (`accountnumber`, `balance`, `date`, `time`, `branchID`) VALUES
(1132, 'توضیحات ترازنامه', '2021-07-12', '00:00:03', 516),
(1792, 'توضیحات ترازنامه', '2020-05-29', '11:17:27', 516),
(5130, 'توضیحات ترازنامه', '2016-02-23', '13:18:00', 123),
(7123, 'توضیحات ترازنامه', '2011-11-16', '10:25:00', 124),
(8210, 'توضیحات ترازنامه', '2019-01-11', '16:20:06', 215);

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

--
-- Table structure for table `borrower`
--

CREATE TABLE `borrower` (
  `customerID` int(50) NOT NULL,
  `loanID` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `borrower`
--

INSERT INTO `borrower` (`customerID`, `loanID`) VALUES
(7951, 357),
(1089, 357);

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

--
-- Table structure for table `branch`
--

CREATE TABLE `branch` (
  `branchID` int(25) NOT NULL,
  `bName` varchar(50) COLLATE utf8_bin NOT NULL,
  `bCity` varchar(50) COLLATE utf8_bin NOT NULL,
  `assests` bigint(20) NOT NULL,
  `mainbankID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `branch`
--

INSERT INTO `branch` (`branchID`, `bName`, `bCity`, `assests`, `mainbankID`) VALUES
(123, 'سعادت اباد', 'تهران', 250000000, 1111),
(124, 'تجریش', 'همدان ', 5842000, 2222),
(215, 'خیابان قیام', 'یزد', 700000000, 2222),
(391, 'نقش جهان', 'اصفهان', 20100000, 1111),
(516, 'میرداماد', 'تهران', 953200000, 1111);

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

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `customerID` int(50) NOT NULL,
  `CName` varchar(78) COLLATE utf8_bin NOT NULL,
  `cphone number` int(11) NOT NULL,
  `cCity` varchar(50) COLLATE utf8_bin NOT NULL,
  `caddress` text COLLATE utf8_bin NOT NULL,
  `cage` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`customerID`, `CName`, `cphone number`, `cCity`, `caddress`, `cage`) VALUES
(412, 'معین سپهری', 387496, 'تهران', 'تهران شهرک غرب', 27),
(1089, 'سحر مقدم', 3254896, 'اصفهان', 'اصفهان پل خواجوو', 32),
(7951, 'ددا رضوی', 36232323, 'تهران', 'تهران شهرک اندیشه مجتمع رز', 26),
(463241, 'محمد فلاح', 3456213, 'یزد', 'یزد خیابان کاشانی کوچه علوی', 50);

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

--
-- Table structure for table `depositor`
--

CREATE TABLE `depositor` (
  `customerID` int(50) NOT NULL,
  `accountnumber` int(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `depositor`
--

INSERT INTO `depositor` (`customerID`, `accountnumber`) VALUES
(7951, 7123),
(463241, 5130),
(412, 1132),
(463241, 1132),
(1089, 1132);

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

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `EmployeeID` int(10) NOT NULL,
  `Employeework` varchar(100) COLLATE utf8_bin NOT NULL,
  `Employeename` varchar(25) COLLATE utf8_bin NOT NULL,
  `aphonenumber` int(11) NOT NULL,
  `Employmentyear` year(4) NOT NULL,
  `eaddress` text COLLATE utf8_bin NOT NULL,
  `esalary` int(20) NOT NULL,
  `branchID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`EmployeeID`, `Employeework`, `Employeename`, `aphonenumber`, `Employmentyear`, `eaddress`, `esalary`, `branchID`) VALUES
(1551, 'Official', 'ali akbari', 912512123, 1941, 'tehran andarzgoo', 1400000000, 123),
(1881, 'Official', 'nazi imanii', 91352412, 1998, 'yazd kashani', 80000000, 215);

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

--
-- Table structure for table `loan`
--

CREATE TABLE `loan` (
  `loanID` int(50) NOT NULL,
  `amount` int(50) NOT NULL,
  `branchID` int(25) NOT NULL,
  `startdate` date NOT NULL,
  `enddate` date NOT NULL,
  `time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `loan`
--

INSERT INTO `loan` (`loanID`, `amount`, `branchID`, `startdate`, `enddate`, `time`) VALUES
(357, 300000, 516, '2020-01-02', '2022-01-12', '10:00:00'),
(412, 1230000, 215, '1998-08-25', '2021-11-25', '09:00:00'),
(863, 5400000, 516, '1996-08-13', '2020-12-30', '07:14:00');

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

--
-- Table structure for table `mainbank`
--

CREATE TABLE `mainbank` (
  `mainbankID` int(10) NOT NULL,
  `mainname` varchar(25) COLLATE utf8_bin NOT NULL,
  `constructionyear` year(4) NOT NULL,
  `budget` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `mainbank`
--

INSERT INTO `mainbank` (`mainbankID`, `mainname`, `constructionyear`, `budget`) VALUES
(1111, 'saderat', 1925, 20000000),
(2222, 'melli', 1912, 100000000);

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

--
-- Table structure for table `managment`
--

CREATE TABLE `managment` (
  `managmentID` int(10) NOT NULL,
  `managename` varchar(25) COLLATE utf8_bin NOT NULL,
  `mphonenumber` int(11) NOT NULL,
  `manageaddress` text COLLATE utf8_bin NOT NULL,
  `manageage` int(3) NOT NULL,
  `manageedu` varchar(100) COLLATE utf8_bin NOT NULL,
  `msalary` int(20) NOT NULL,
  `employmentyear` year(4) NOT NULL,
  `branchID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `managment`
--

INSERT INTO `managment` (`managmentID`, `managename`, `mphonenumber`, `manageaddress`, `manageage`, `manageedu`, `msalary`, `employmentyear`, `branchID`) VALUES
(1030, 'akbar fallah', 2146258, 'tehran pol sadr', 59, 'Master of Banking', 2100000000, 2011, 516),
(1046, 'mohammad alavi', 352146, 'yazd blv jomhorii', 63, 'Master of Banking', 18200000, 2016, 215);

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

--
-- Table structure for table `safebox`
--

CREATE TABLE `safebox` (
  `safeboxID` int(10) NOT NULL,
  `price` int(20) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `branchID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `safebox`
--

INSERT INTO `safebox` (`safeboxID`, `price`, `date`, `time`, `branchID`) VALUES
(162, 75000000, '2020-11-07', '20:03:41', 123),
(888, 28000000, '2020-04-22', '10:00:00', 391),
(1642, 160000, '2019-01-25', '10:25:04', 215);

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

--
-- Table structure for table `servise`
--

CREATE TABLE `servise` (
  `serviseID` int(10) NOT NULL,
  `sname` varchar(25) COLLATE utf8_bin NOT NULL,
  `Employmentyear` year(4) NOT NULL,
  `sage` int(3) NOT NULL,
  `sphone` int(11) NOT NULL,
  `saddress` text COLLATE utf8_bin NOT NULL,
  `ssalary` int(20) NOT NULL,
  `branchID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Dumping data for table `servise`
--

INSERT INTO `servise` (`serviseID`, `sname`, `Employmentyear`, `sage`, `sphone`, `saddress`, `ssalary`, `branchID`) VALUES
(13333, 'iman', 1999, 49, 213620, 'tehran andishe', 30000, 516),
(15555, 'hesam', 2015, 38, 216201, 'tehran tajrish', 300000, 124);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account`
--
ALTER TABLE `account`
  ADD PRIMARY KEY (`accountnumber`),
  ADD KEY `branchID` (`branchID`);

--
-- Indexes for table `borrower`
--
ALTER TABLE `borrower`
  ADD KEY `customerID` (`customerID`),
  ADD KEY `loanID` (`loanID`);

--
-- Indexes for table `branch`
--
ALTER TABLE `branch`
  ADD PRIMARY KEY (`branchID`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
  ADD PRIMARY KEY (`customerID`);

--
-- Indexes for table `depositor`
--
ALTER TABLE `depositor`
  ADD KEY `account number` (`accountnumber`),
  ADD KEY `customerID` (`customerID`);

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
  ADD PRIMARY KEY (`EmployeeID`),
  ADD KEY `branchID` (`branchID`);

--
-- Indexes for table `loan`
--
ALTER TABLE `loan`
  ADD PRIMARY KEY (`loanID`),
  ADD KEY `branchID` (`branchID`);

--
-- Indexes for table `mainbank`
--
ALTER TABLE `mainbank`
  ADD PRIMARY KEY (`mainbankID`);

--
-- Indexes for table `managment`
--
ALTER TABLE `managment`
  ADD PRIMARY KEY (`managmentID`),
  ADD KEY `branchID` (`branchID`);

--
-- Indexes for table `safebox`
--
ALTER TABLE `safebox`
  ADD PRIMARY KEY (`safeboxID`),
  ADD KEY `branchID` (`branchID`);

--
-- Indexes for table `servise`
--
ALTER TABLE `servise`
  ADD PRIMARY KEY (`serviseID`),
  ADD KEY `branchID` (`branchID`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `account`
--
ALTER TABLE `account`
  ADD CONSTRAINT `account_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

--
-- Constraints for table `borrower`
--
ALTER TABLE `borrower`
  ADD CONSTRAINT `borrower_ibfk_1` FOREIGN KEY (`customerID`) REFERENCES `customer` (`customerID`),
  ADD CONSTRAINT `borrower_ibfk_2` FOREIGN KEY (`loanID`) REFERENCES `loan` (`loanID`);

--
-- Constraints for table `depositor`
--
ALTER TABLE `depositor`
  ADD CONSTRAINT `depositor_ibfk_1` FOREIGN KEY (`accountnumber`) REFERENCES `account` (`accountnumber`),
  ADD CONSTRAINT `depositor_ibfk_2` FOREIGN KEY (`customerID`) REFERENCES `customer` (`customerID`);

--
-- Constraints for table `employee`
--
ALTER TABLE `employee`
  ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

--
-- Constraints for table `loan`
--
ALTER TABLE `loan`
  ADD CONSTRAINT `loan_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

--
-- Constraints for table `managment`
--
ALTER TABLE `managment`
  ADD CONSTRAINT `managment_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

--
-- Constraints for table `safebox`
--
ALTER TABLE `safebox`
  ADD CONSTRAINT `safebox_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);

--
-- Constraints for table `servise`
--
ALTER TABLE `servise`
  ADD CONSTRAINT `servise_ibfk_1` FOREIGN KEY (`branchID`) REFERENCES `branch` (`branchID`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Tags:

2

Answers


  1. 0

    First, you need to replace the backticks (`) with standard conforming double quotes ("). This will also prevent branchID from getting folded to lower case.

    Second, instead of ALTER TABLE ... ADD KEY (colname) you have to use ALTER TABLE ... ADD UNIQUE (colname). You need a unique constraint as target of foreign key.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search