377
edits
Changes
Jump to navigation
Jump to search
INSERT INTO patents_merged ( SELECT patents.patentnumber, patents.grantdate, patents.prioritydate, patents.prioritycountry, patents.prioritypatentnumber, patents.cpcsubgroup, patents.pctpatentnumber, patents.numberofclaims, patents.applicationnumber, NULL, patents.filingdate, NULL, NULL, NULL, NULL FROM patents );
\COPY (SELECT patents.patentnumber, patents.grantdate, patents.prioritydate, patents.prioritycountry, patents.prioritypatentnumber, patents.cpcsubgroup, patents.pctpatentnumber, patents.numberofclaims, patents.applicationnumber, NULL, patents.filingdate, NULL, NULL, NULL, NULL FROM patents) TO '/tmp/merged_patents_export.txt' DELIMITER AS E'\t' HEADER NULL AS CSV; --COPY 1646225
UPDATE patent p SET nber = hp.nber, uspc = hp.uspc, uspc_sub = hp.uspc FROM historicalpatentdata hp WHERE hp.patentnumber = CAST(p.patent AS varchar);
Redesigning Patent Database (view source)
Revision as of 10:52, 20 April 2017
, 10:52, 20 April 2017→For the table called "patents
====Cleaning up patent data and inserting it into the existing patent data table====
=====For the table called "patentspatent"=====
INSERT statement:
Equivalent COPY statements:
First copy data from RDP to a CSV file:
Then copy CSV file into table:
The data in columns nber, uspc, and uspc_sub is taken from historicalpatentdata. Here is the SQL code to do that after the new data from the RDP has been inserted into the table "patent".
Missing information (represented by NULL):
