命令:
\copy 表名 to 文件名|标准输出 [delimiter as ‘分隔符’] [null as ‘null表示的字符串’ ] [csv quote as ‘引号的类型’]
解释:
可以将表中的数据输出到 文件或标准输出。
delimiter as ‘分隔符’ :说明输出设备的文本中表的每个字段用什么字符分割,默认是tab;
null as ‘null表示的字符串’:说明输出设备的文本中表的NULL值的表示方法,默认为“\N”;
csv quote as ‘引号类型’ :说明导出的csv文件中的引号类型是什么,
对于Postgres7.4以前的版本,不支持csv的导入与导出,这时不要使用这个可选项。
例子:
aa=#\copy testtable to data.csv delimiter as ‘,’ csv quote as ‘”‘
这条命令将testtable表中的内容作为SQL语句,导出data.csv文件中。
delimiter as ‘,’ :说明data.txt文本中表的每个字段用“逗号”分割;
csv quote as ‘”‘ :说明csv中的引号类型是“双引号”。
像前面说的:这个例子不适用在Postgres7.4以前的版本。
注意文件保存路径默认在:
C:\Documents and Settings\zhuyaopeng\目录下 而且只保存数据,不保存表结构
4. 从 文件标识符 导入数据到数据库
命令:
\copy 表名 form 文件名|标准输入 [delimiter as ‘分隔符’] [null as ‘null表示的字符串’ ] [csv quote as ‘引号的类型’]
例子:
aa=#\copy testtable from data.csv delimiter as ‘,’ csv quote as ‘”‘
这条命令将data.csv文件中的文本,作为sql语句导入到testtable表,
delimiter as ‘,’ :说明data.txt文本中表的每个字段用“逗号”分割;
csv quote as ‘”‘ :说明csv中的引号类型是“双引号”。
5.如何只选择一个查询结果的头几行?或是随机的一行?
如果你只是要提取几行数据,并且你在执行查询中知道确切的行数,你可以使用LIMIT功能。如果有一个索引与 ORDER BY中的条件匹配,PostgreSQL 可能就只处理要求的头几条记录,(否则将对整个查询进行处理直到生成需要的行)。如果在执行查询功能时不知道确切的记录数,可使用游标(cursor)和FETCH功能。
可使用以下方法提取一行随机记录的:
SELECT cols
FROM tab
ORDER BY random()
LIMIT 1 ;
在8.0版本里更改一个字段的数据类型很容易,可使用 ALTER TABLE ALTER COLUMN TYPE 。
在以前的版本中,可以这样做:
BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;
你然后可以使用VACUUM FULL tab 指令来使系统收回无效数据所占用的空间。
并非每个查询都会自动使用索引。只有在表的大小超过一个最小值,并且查询只会选中表中较小比例的记录时才会采用索引。这是因为索引扫描引起的随即磁盘存取可能比直接地读取表(顺序扫描)更慢。
为了判断是否使用索引,PostgreSQL必须获得有关表的统计值。这些统计值可以使用 VACUUM ANALYZE,或 ANALYZE 获得。使用统计值,优化器知道表中有多少行,就能够更好地判断是否利用索引。统计值对确定优化的连接顺序和连接方法也很有用。在表的内容发生变化时,应定期进行统计值的更新收集。
索引通常不用于 ORDER BY 或执行连接。对一个大表的一次顺序扫描,再做一个显式的排序通常比索引扫描要快。
但是,在 LIMIT 和 ORDER BY 结合使用时经常会使用索引,因为这只会返回表的一小部分。 实际上,虽然 MAX() 和 MIN() 并不使用索引,通过对 ORDER BY 和 LLIMIT 使用索引取得最大值和最小值也是可以的:
SELECT col
FROM tab
ORDER BY col [ DESC ]
LIMIT 1;
如果你确信PostgreSQL的优化器使用顺序扫描是不正确的,你可以使用SET enable_seqscan TO 'off'指令,然后再次运行查询,你就可以看出使用一个索引扫描是否确实要快一些。
当使用通配符操作,例如 LIKE 或 ~ 时,索引只能在特定的情况下使用:
字符串的开始部分必须是普通字符串,也就是说:
LIKE 模式不能以 % 打头。
~ (正则表达式)模式必须以 ^ 打头。
字符串不能以匹配多个字符的模式类打头,例如 [a-e]。
大小写无关的查找,如 ILIKE 和 ~* 等不使用索引,但可以用 4.8 节描述的函数索引。
在做 initdb 时必须采用缺省的本地设置 C locale,因为系统不可能知道在非C locale情况时下一个最大字符是什么。在这种情况下,你可以创建一个特殊的text_pattern_ops索引来用于LIKE的索引。
在8.0之前的版本中,除非要查询的数据类型和索引的数据类型相匹配,否则索引经常是未被用到,特别是对int2,int8和数值型的索引。
11.我怎样做正则表达式搜索和大小写无关的正则表达式查找?怎样利用索引进行大小写无关查找?
操作符 ~ 处理正则表达式匹配,而 ~* 处理大小写无关的正则表达式匹配。大写些无关的 LIKE 变种成为 ILIKE。
大小写无关的等式比较通常写做:
SELECT *
FROM tab
WHERE lower(col) = 'abc';
这样将不会使用标准的索引。但是可以创建一个可被利用的函数索引:
CREATE INDEX tabindex ON tab (lower(col));
12.在一个查询里,我怎样检测一个字段是否为 NULL ?我如何才能准确排序而不论某字段是否含 NULL 值?
用 IS NULL 和 IS NOT NULL 测试这个字段,具体方法如下:
SELECT *
FROM tab
WHERE col IS NULL;
为了能对含 NULL字段排序,可在 ORDER BY 条件中使用 IS NULL和 IS NOT NULL 修饰符,条件为真 true 将比条件为假false 排在前面,下面的例子就会将含 NULL 的记录排在结果的上面部分:
SELECT *
FROM tab
ORDER BY (col IS NOT NULL)