Changes

Jump to navigation Jump to search
no edit summary
** Assignees Data:
CREATE TABLE assignees_temp2 (
 
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar, --assignee varchar,
city varchar, --city2 varchar,
country varchar, --country2 varchar,
patentnumber int, --patent integer,
state varchar, --state2 varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
 
);
 
CREATE TABLE assignees
(
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar,
city varchar,
country varchar,
patentnumber int,
state varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
);
 
 
PatentData:
Column | Type | Modifiers
-------------+-------------------+-----------
patent | integer |
asgtype | integer |
assignee | character varying |
city | character varying |
state | character varying |
country | character varying |
nationality | character varying |
residence | character varying |
asgseq | integer |
 
(
patent int,
asgtype int,
assignee varchar ,
city varchar ,
state varchar ,
country varchar ,
nationality varchar ,
residence varchar ,
asgseq int
)
 
INSERT INTO assignees_merge
(
SELECT
'null',
'null',
'null',
'null',
a.assignee,
a.city,
a.country,
a.patent,
a.state,
'null',
a.nationality,
a.residence,
a.asgseq,
a.asgtype
FROM assignees a
);
 
/// Reference:
CREATE TABLE assignees_temp2 (
 
lastname varchar,
firstname varchar,
address varchar,
postcode varchar,
orgname varchar, --assignee varchar,
city varchar, --city2 varchar,
country varchar, --country2 varchar,
patentnumber int, --patent integer,
state varchar, --state2 varchar,
patentcountry varchar,
nationality2 varchar,
residence varchar,
asgseq int,
asgtype integer
 
);
//////
 
 
 
Patent_2015:
Column | Type | Modifiers
---------------+---------+-----------
lastname | text |
firstname | text |
orgname | text |
city | text |
country | text |
patentcountry | text |
patentnumber | integer |
state | text |
address | text |
postcode | text |
 
 
(
lastname varchar,
firstname varchar,
orgname varchar,
city varchar,
country varchar,
patentcountry varchar,
patentnumber int,
state varchar,
address varchar,
postcode varchar
)
 
INSERT INTO assignees_merge
(
SELECT
assignees.lastname,
assignees.firstname,
assignees.address,
assignees.postcode,
assignees.orgname,
assignees.city,
assignees.country,
assignees.patentnumber,
assignees.state,
assignees.patentcountry,
'null',
'null',
-1,
-1
FROM assignees
);
 
 
\COPY assignees_merge TO '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
\COPY assignees FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
--1607724
 
\COPY assignees_merge TO '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
\COPY assignees FROM '/tmp/assignees_merge_export1.txt' DELIMITER AS E'\t' HEADER NULL AS '' CSV;
--3818842
 
 
 
 
SELECT count(*), *
from assignees
GROUP BY lastname, firstname, address, postcode, orgname, city, country, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtype
HAVING COUNT(*) > 1
;
 
CREATE TABLE Assignees2 AS
SELECT *
from assignees
GROUP BY lastname, firstname, address, postcode, orgname, city, country, patentnumber, state, patentcountry, nationality2, residence, asgseq, asgtype
HAVING COUNT(*) = 1
;
 
DROP TABLE Assignees;
ALTER TABLE Assignees2 RENAME TO Assignees;
 
--
 
 
 
 
Patentdata:

Navigation menu