実行環境
••
•
•
エクスポート対象のデータ
8# \d kenpg_seesaa_list Table "public.kenpg_seesaa_list" +-----------+------+-----------+ | Column | Type | Modifiers | +-----------+------+-----------+ | ymd | date | not null |<= | url | text | not null | | title | text | not null |<= | tags | text | not null |<= | html_file | text | not null | | html | text | not null | | body | text | |<= +-----------+------+-----------+ Indexes: "kenpg_seesaa_list_pkey" PRIMARY KEY, btree (ymd) "kenpg_seesaa_list_url_idx" UNIQUE, btree (url)
列
# select body2 from kenpg_seesaa_list order by nullif(position('''' in body2), 0);
改行や引用符のない「普通の」データなら、CSV
pg_dump でテーブルデータだけ、INSERT 文でダンプ
pg_dump# create unlogged table tmp select ymd, title, tags, body2 from kenpg_seesaa_list; # \! pg_dump -d works08 -h ::1 -U postgres -t tmp -f r:/tmp.sql -a --inserts -- check output file # \! ls -l r:/tmp.sql -rw-r--r-- 1 Administrator None 3476959 Aug 31 16:53 r:/tmp.sql
上では
SQL ファイルを SQLite 用に編集し .read コマンドで実行
ダンプ結果(SQL-- -- PostgreSQL database dump -- -- Dumped from database version 9.5.3 -- Dumped by pg_dump version 9.5.4 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; SET search_path = public, pg_catalog; -- -- Data for Name: tmp; Type: TABLE DATA; Schema: public; Owner: postgres -- INSERT INTO tmp VALUES (...); INSERT INTO tmp VALUES (...); ... -- -- PostgreSQL database dump complete --
これを
create table tmp (ymd date, title text, tags text, body2 blob); INSERT INTO tmp VALUES (...); INSERT INTO tmp VALUES (...); ...
シェルに移り、適当なファイル名を指定して
$ sqlite3 tmp.db SQLite version 3.14.1 2016-08-11 18:53:32 Enter ".help" for usage hints. sqlite> .read tmp2.sql sqlite> .schema CREATE TABLE tmp (ymd date, title text, tags text, body2 blob); sqlite> select count(*) from tmp; 693 sqlite> select min(length(body2)) from tmp; 178 sqlite> select max(length(body2)) from tmp; 20649
この後、SQLite
実行コマンドまとめ
テンプレート的に書くと ↓ わずか# create unlogged table tmp as select column1, column2, ... from table ; $ pg_dump -d database -h host -U user -t tmp -f tmp.sql -a --inserts (edit tmp.sql) $ sqlite3 file.db sqlite> .read tmp.sql