377
edits
Changes
Jump to navigation
Jump to search
Redesigning Patent Database (view source)
Revision as of 11:27, 20 April 2017
, 11:27, 20 April 2017→For the table called "citation"
where "citations" is the name of the table containing the data from the RDP update and "citations_merge" is the name of the table we want to insert the new data into.
=====for the table called assignee=====
INSERT statement (copies data from table we loaded from RDP into existing table in patent database
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, NULL, NULL FROM assignees );
Equivalent COPY statemnets:
\COPY assignees (SELECT assignees.lastname, assignees.firstname, assignees.address, assignees.postcode, assignees.orgname, assignees.city, assignees.country, assignees.patentnumber, assignees.state, assignees.patentcountry,
NULL, NULL, NULL, NULL FROM assignees) TO '/tmp/merged_assignees_export.txt' DELIMITER AS E'\t' HEADER NULL AS CSV;
--COPY 1646225
Then copy CSV file into table:
\COPY assignees_merge FROM '/tmp/assignees_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS CSV; --1607724
where "assignees" is the name of the table containing the data from the RDP update and "assignees_merge" is the name of the table we want to insert the new data into.
Table that we want to insert in patent is called assigneeinfo. So you would replace "assignees_merge" with "assigneeinfo".
Missing information (represented by NULL):
nationality (char sequence)
residence (char sequence)
asgseq (int)
asgtype (int)
Schema of assignees data when loaded into RDP (based on exisitng docmentation - have not checked schema on RDP postgres server):
lastname | text |
firstname | text |
orgname | text |
city | text |
country | text |
patentcountry | text |
patentnumber | integer |
state | text |
address | text |
postcode | text |
Schema of assignees table in patent (named assigneeinfo)
lastname | character varying(200) | | extended | |
firstname | character varying(200) | | extended | |
address | character varying(200) | | extended | |
postcode | character varying(200) | | extended | |
orgname | character varying(500) | | extended | |
city | character varying(200) | | extended | |
country | character varying(200) | | extended | |
patent | character varying(200) | | extended | |
state | character varying(200) | | extended | |
patentcountry | character varying(200) | | extended | |
nationality | character varying(200) | | extended | |
residence | character varying(200) | | extended | |
asgseq | integer | | plain | |
asgtype | integer | | plain | |
===For the USPTO Assignment Data===
