Solved

Migrate this SQL to postgres

Posted on 2014-02-01
4
427 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 12

Expert Comment

by:Phil Phillips
Comment Utility
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
Comment Utility
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 12

Accepted Solution

by:
Phil Phillips earned 500 total points
Comment Utility
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
Comment Utility
Thank you so much for your help that has solved the problem
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now