Solved

Migrate this SQL to postgres

Posted on 2014-02-01
4
445 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 13

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 13

Accepted Solution

by:
Phil Phillips earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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