Solved

Migrate this SQL to postgres

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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
query question 12 34
MYSQL responding very slow 3 48
Requesting help with creating an SQL query with 2 tables 6 31
sql update 2 34
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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