Migration from MYSQL to PostgreSQL

Question

I just want to migrate my application from MYSQL to PostgreSQL. I am stuck at one point,

CREATE TABLE some_table 
(
  sequenceNumOnTarget        BIGINT NOT NULL,
  streamStartTime            BIGINT,
  intervalStartTime          BIGINT NOT NULL,
  intervalNumber             SMALLINT,
  intervalDuration           INTEGER,
  lastReport                 TINYINT,
  macAddr                    VARCHAR(20) NOT NULL,
  directoryNumber            VARCHAR(30),
  subnetMask                 VARCHAR(20),
  subnetAddress              VARCHAR(20),
  ipAddress                  VARCHAR(20),
  icpName                    VARCHAR(20),
  udpPort                    INTEGER NOT NULL,
  tcpPort                    INTEGER,
  endpointContext            SMALLINT,
  endpointType               SMALLINT,
  farEndIpAddress            VARCHAR(20),
  farEndMacAddr              VARCHAR(20),
  farEndDirectoryNumber      VARCHAR(30),
  farEndUdpPort              INTEGER,
  farEndTcpPort              INTEGER,
  farEndType                 SMALLINT,
  farEndSubnet               VARCHAR(20),
  farEndIcpName              VARCHAR(20),
  codec                      SMALLINT,
  packetsReceived            BIGINT,
  DELAY INTEGER,
  jitterRfc1889              INTEGER,
  averageJitter              INTEGER,
  jitterHist0                INTEGER,
  jitterHist1                INTEGER,
  jitterHist2                INTEGER,
  jitterHist3                INTEGER,
  jitterHist4                INTEGER,
  jitterHist5                INTEGER,
  jitterHist6                INTEGER,
  jitterHist7                INTEGER,
  jitterBufferOverflow       BIGINT,
  jitterBufferUnderflow      BIGINT,
  jitterBufferAverageDepth   INTEGER,
  jitterBufferMaxDepth       INTEGER,
  packetLoss                 BIGINT,
  packetLossMaxBurst         BIGINT,
  packetLossHist0            INTEGER,
  packetLossHist1            INTEGER,
  packetLossHist2            INTEGER,
  packetLossHist3            INTEGER,
  packetLossHist4            INTEGER,
  packetLossHist5            INTEGER,
  packetLossHist6            INTEGER,
  packetLossHist7            INTEGER,
  packetsOutOfOrder          BIGINT,
  maxJitter                  BIGINT,
  networkMos                 INTEGER,
  userMos                    INTEGER,
  pollId                     BIGINT,
  instance                   VARCHAR(100),
  ttime                      BIGINT NOT NULL,
  PRIMARY KEY (sequenceNumOnTarget,macAddr,udpPort), 
  INDEX vq_subnet(subnetAddress),
  INDEX vq_ttime(ttime), 
  INDEX vq_pollid(pollId),
  INDEX vq_sequence(sequenceNumOnTarget),
  INDEX vq_icp(icpName)
);

This is my table definition in MySQL and now I want to convert it to PostgreSQL.

But can't find a DDL statement suitable to 'create table and indexing both with one single query' in PostgreSQL.

Can anyone help..

PostgreSQL not PostGreSQL ;)

veritas
– veritas

2011-10-11 11:31:06 +00:00
Commented Oct 11, 2011 at 11:31 — veritas
– veritas, Commented Oct 11, 2011 at 11:31

TomH · Accepted Answer · 2011-10-11 11:20:43Z

5

I don't believe you can add indexes (other than those created implicitly to enforce UNIQUE and PRIMARY KEY constraints) as part of a CREATE TABLE statement in PostgreSQL.

There is no real need to do so however. If you want to make sure that the indexes are created before anybody can see (and hence use) the table then just create the table and the indexes all inside a single transaction and only commit the transaction when you are ready for the table to be used.

That won't work in MySQL because schema changes happen outside any transactions, even if using an engine that normally supports them. It should work in PostgreSQL though.

answered Oct 11, 2011 at 11:20

TomH

9,3003 gold badges34 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Good point about creating the table and the indexes in a single transaction

obr · Accepted Answer · 2011-10-11 11:26:20Z

0

If you can split the expression into separate expressions for creation and index definition the migration should be straightforward.

answered Oct 11, 2011 at 11:26

obr

1317 bronze badges

1 Comment

Stuti Over a year ago

No I can't as in application, only one parameter can be passed in functions, this parameter is a string containing this query and many more other dependencies are there.

Collectives™ on Stack Overflow

Migration from MYSQL to PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related