Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Migrate this SQL to postgres

Posted on 2014-02-01
4
Medium Priority
?
464 Views
Last Modified: 2014-02-02
I am trying to create the following table, the sql is from mysql, however I have chnaged a lot in the table. I am still getting an error in the KEY part, any help please, the SQL is :

CREATE TABLE wp_visitor_maps_wo  (
       session_id      varchar(128) NOT NULL default '',
       ip_address      varchar(20) NOT NULL default '',
       user_id         bigserial NOT NULL,
       name            varchar(64) NOT NULL default '',
       nickname        varchar(20) default NULL,
       country_name    varchar(50) default NULL,
       country_code    char(2) default NULL,
       city_name       varchar(50) default NULL,
       state_name      varchar(50) default NULL,
       state_code      char(2) default NULL,
       latitude        decimal(10,4) default '0.0000',
       longitude       decimal(10,4) default '0.0000',
       last_page_url   text NOT NULL,
       http_referer    varchar(255) default NULL,
       user_agent      varchar(255) NOT NULL default '',
       hostname        varchar(255) default NULL,
       provider        varchar(255) default NULL,
       time_entry      int NOT NULL default '0',
       time_last_click int NOT NULL default '0',
       num_visits      int NOT NULL default '0',
        PRIMARY KEY  (session_id),
        KEY 'nickname_time_last_click' (nickname,time_last_click) );
alter table has_visitor_maps_wo add index(time_last_click);

Open in new window

0
Comment
Question by:Ashraf Hassanein
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Phil Phillips
ID: 39827189
First create the table:
CREATE TABLE wp_visitor_maps_wo  (
       session_id      varchar(128) NOT NULL default '',
       ip_address      varchar(20) NOT NULL default '',
       user_id         bigserial NOT NULL,
       name            varchar(64) NOT NULL default '',
       nickname        varchar(20) default NULL,
       country_name    varchar(50) default NULL,
       country_code    char(2) default NULL,
       city_name       varchar(50) default NULL,
       state_name      varchar(50) default NULL,
       state_code      char(2) default NULL,
       latitude        decimal(10,4) default '0.0000',
       longitude       decimal(10,4) default '0.0000',
       last_page_url   text NOT NULL,
       http_referer    varchar(255) default NULL,
       user_agent      varchar(255) NOT NULL default '',
       hostname        varchar(255) default NULL,
       provider        varchar(255) default NULL,
       time_entry      int NOT NULL default '0',
       time_last_click int NOT NULL default '0',
       num_visits      int NOT NULL default '0',
        PRIMARY KEY  (session_id));

Open in new window


Then, add the indexes:

create index wp_visitor_maps_wo__n_tlc on wp_visitor_maps_wo(nickname, time_last_click);
create index wp_visitor_maps_wo__tlc on wp_visitor_maps_wo(time_last_click);

Open in new window

0
 

Author Comment

by:Ashraf Hassanein
ID: 39827348
Thank you so much it worked pretty well However I need to convert the following SQL if you do not mind please
INSERT IGNORE INTO wp_visitor_maps_wo
(session_id,
ip_address,
user_id,
name,
country_name,
country_code,
city_name,
state_name,
state_code,
latitude,
longitude,
last_page_url,
http_referer,
user_agent,
hostname,
time_entry,
time_last_click,
num_visits)
values (
'00.00.00.00',
'00.00.00.00',
'1',
'Me',
'Home',
'HO',
'HOME',
'Home',
'11',
'000',
'000',
'WP',
'https://www.mydomain.com/',
'Headers',
'ISP',
'00000',
'00000',
'1')

Open in new window


This is giving ERROR:
ERROR: syntax error at or near "IGNORE" LINE 1: INSERT IGNORE INTO wp_visitor_maps_wo ^

Open in new window



And Statement
UPDATE has_visitor_maps_st
SET
count = '0',
time = '2014-02-02 08:40:33'
WHERE (day('2014-02-02 08:40:33') != day(time) AND type = 'day')
OR (count < '0' AND type = 'day')

Open in new window


This is giving error:
ERROR: syntax error at or near "SET" LINE 6: OR (count < '0' AND type = 'day') SET ^

Open in new window

0
 
LVL 15

Accepted Solution

by:
Phil Phillips earned 2000 total points
ID: 39827395
There is no direct "INSERT IGNORE" for postgres.  What you can do instead is check for a record, then insert if it doesn't exist.  Or, you could create a rule like:

CREATE RULE "wp_visitor_maps_wo__dup_ignore" AS ON INSERT TO "wp_visitor_maps_wo"
  WHERE EXISTS(SELECT 1 FROM wp_visitor_maps_wo 
                WHERE session_id=NEW.session_id)
  DO INSTEAD NOTHING;

Open in new window


Then, you can just do the insert:
INSERT INTO wp_visitor_maps_wo
(session_id,
ip_address,
user_id,
name,
country_name,
country_code,
city_name,
state_name,
state_code,
latitude,
longitude,
last_page_url,
http_referer,
user_agent,
hostname,
time_entry,
time_last_click,
num_visits)
values (
'00.00.00.00',
'00.00.00.00',
'1',
'Me',
'Home',
'HO',
'HOME',
'Home',
'11',
'000',
'000',
'WP',
'https://www.mydomain.com/',
'Headers',
'ISP',
'00000',
'00000',
'1');

Open in new window



For the other query, try:
UPDATE has_visitor_maps_st
  SET count = 0, time = '2014-02-02 08:40:33'
  WHERE (time != '2014-02-02 08:40:33' OR count < 0)
    AND type = 'day';

Open in new window

0
 

Author Comment

by:Ashraf Hassanein
ID: 39827557
Thank you so much for your help that has solved the problem
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question