?
Solved

Migrate this SQL to postgres

Posted on 2014-02-01
4
Medium Priority
?
468 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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