将 PostgreSQL 表中的特定行导出为 INSERT SQL 脚本 [英] Export specific rows from a PostgreSQL table as INSERT SQL script

问题描述

我有一个名为 nyummy 的数据库架构和一个名为 cimory:

I have a database schema named: nyummy and a table named cimory:

create table nyummy.cimory (
  id numeric(10,0) not null,
  name character varying(60) not null,
  city character varying(50) not null,
  CONSTRAINT cimory_pkey PRIMARY KEY (id)
);

我想将 cimory 表的数据导出为插入 SQL 脚本文件.但是，我只想导出城市等于东京"的记录/数据(假设城市数据都是小写的).

I want to export the cimory table's data as insert SQL script file. However, I only want to export records/data where the city is equal to 'tokyo' (assume city data are all lowercase).

怎么做?

解决方案是在免费软件 GUI 工具还是命令行中都没有关系(尽管 GUI 工具解决方案更好).我曾尝试过 pgAdmin III，但找不到执行此操作的选项.

It doesn't matter whether the solution is in freeware GUI tools or command line (although GUI tools solution is better). I had tried pgAdmin III, but I can't find an option to do this.

推荐答案

使用要导出的集合创建表，然后使用命令行实用程序 pg_dump 导出到文件:

Create a table with the set you want to export and then use the command line utility pg_dump to export to a file:

create table export_table as 
select id, name, city
from nyummy.cimory
where city = 'tokyo'

$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

--column-inserts 将转储为带有列名的插入命令.

--column-inserts will dump as insert commands with column names.

--data-only 不转储架构.

如下所述，创建视图而不是表将避免在需要新导出时创建表.

As commented below, creating a view in instead of a table will obviate the table creation whenever a new export is necessary.

这篇关于将 PostgreSQL 表中的特定行导出为 INSERT SQL 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！