377
edits
Changes
Jump to navigation
Jump to search
Redesigning Patent Database (view source)
Revision as of 10:55, 20 April 2017
, 10:55, 20 April 2017→For the table called "patent"
"patents_pkey" PRIMARY KEY, btree (patent)
"patent_idx" UNIQUE, btree (patent)
=====For the table called "citation"=====
Schema of citations table in patent
Table "public.citation"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------+-----------+---------+--------------+-------------
patent | bigint | | plain | |
cited | bigint | | plain | |
We just need citingpatentnumber and citedpatentnumber where citedpatentnumber is an seven digit int from the RDP database.
Use this script to replace textual patent numbers as ints (from existing documentation)
CREATE OR REPLACE FUNCTION cleanpatno (text) RETURNS bigint AS $$ if ($_[0]) { my $var=$_[0]; if ($var=~/^\d*$/) {return $var;} return undef; } return undef; $$ LANGUAGE plperl;
INSERT statement
INSERT INTO citation ( SELECT new_citations.citingpatentnumber, new_citations.citedpatent number FROM patents );
Equivalent COPY statement:
\COPY citations (SELECT new_citations.citingpatentnumber, new_citations.citedpatent number FROM patents) TO '/tmp/citations_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS CSV;
--COPY 1646225
Then copy CSV file into table:
\COPY citations_merged FROM '/tmp/citations_merge_export.txt' DELIMITER AS E'\t' HEADER NULL AS CSV; --1607724
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 USPTO Assignment Data===
