pgAdmin如何使用postgresql应该怎么设置

21 pgAdmin3的启动

您可以在应用程序---系统工具中找到pgAdmin3的启动项;

也可以在命令行下输入：

xiaop@xiaop-laptop:~$ /usr/bin/pgadmin3 start

622 连接已创建的数据库mydb ;

点击档案-----新增服务器，然后在跳出的窗口下输入：

地址：localhost

描述：服务器名称（随意填写）

维护数据库：postgres

用户名：自己创建一个（详情参见创建用户）

密码：和用户名对应（创建用户时自己创建）

点击确定后大家便可以查看postsql已有的数据库了;

注：pgAdmin3的数据库和终端下创建的数据库是完全同步的（可以用刷新查看效果）， pgAdmin3是比较方便的图形化管理工具，它可以创建图表，管理数据库等，有关pgAdmin3的详细介绍我们在以后讨论，本文主要介绍命令行下的 *** 作。图形化管理工具能做到的命令行都可以做到，您可以在命令行下创建表，在pgAdmin3上查看是否同步：

7 创建和删除表;

71 创建新表;

创建完数据库之后，您就可以创建新表了，可以通过声明表的名字和所有字段的名字及其类型来创建表，例如：

mydb#CREATE TABLE weather (

city varchar(80),

temp_lo int, -- 最低气温

temp_hi int, -- 最高气温

prcp real, -- 降水量

date date

);

注：您可以在 psql 里连换行符一起键入这些东西。 psql 可以识别该命令直到分号才结束,不要忘记“;”

您可以在 SQL 命令中自由使用空白（也就是空格，tab，和换行符）。 这就意味着您可以用和上面不同的对齐方式键入命令。 两个划线("--") 引入注释。 任何跟在它后面的东西直到该行的结尾都被忽略。 SQL 是对关键字和标识符大小写不敏感的语言，只有在标识符用双引号包围时才能保留它们的大小写属性。

72 数据类型;

上面例子中的varchar(80) 声明一个可以存储最长 80 个字符的任意字符串的数据类型。 int 是普通的整数类型。 real 是一种用于存储单精度浮点数的类型。 date 类型应该可以自解释。

PostgresSQL 支持标准的 SQL 类型 int，smallint， real，double precision， char(N)， varchar(N)，date， time，timestamp 和 interval，还支持其他的通用类型和丰富的几何类型。 PostgreSQL 可以客户化为定制任意的用户定义的数据类型，您可以参考PostgreSQL的中文文档来查询;

73 删除表;

如果您不再需要某个表，或者您想创建一个不同的表，那么您可以用下面的命令删除它：

mydb#DROP TABLE tablename

8 向表中添加行;

81 INSERT;

INSERT 用于向表中添加行,您可以输入（在数据库中 *** 作）：

mydb#INSERT INTO weather VALUES ('San Francisco', 46, 50, 025, '1994-11-27');

注：所有数据类型都使用了相当明了的输入格式。 那些不是简单数字值的常量必需用单引号(')包围， 就象在例子里一样。

82 point类型输入;

point 类型要求一个座标对作为输入，如下：

mydb#INSERT INTO cities VALUES ('San Francisco', '(-1940, 530)');

83 COPY;

您还可以使用 COPY 从文本文件中装载大量数据。 这么干通常更快，因为 COPY 命令就是为这类应用优化的， 只是比 INSERT 少一些灵活性．比如：

mydb#COPY weather FROM '/home/user/weathertxt';

注：weathertxt是您提前写好的符合格式标准的表格内容文档;

9 查询一个表;

91 SELECT;

要从一个表中检索数据就是查询这个表。 SQL 的 SELECT 就是做这个用途的。 该语句分为选择列表（列出要返回的字段部分），表列表（列出从中检索数据的表的部分）， 以及可选的条件（声明任意限制的部分）。比如，要检索表 weather 的所有行，键入：

SELECT FROM weather;

<code>

输出结果：

<code>

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 025 | 1994-11-27

San Francisco | 43 | 57 | 0 | 1994-11-29

Hayward | 37 | 54 | | 1994-11-29

(3 rows)

您可以在选择列表中写任意表达式，而不仅仅是字段列表。比如，您可以：

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

这样应该得出：

city | temp_avg | date

---------------+----------+------------

San Francisco | 48 | 1994-11-27

San Francisco | 50 | 1994-11-29

Hayward | 45 | 1994-11-29

(3 rows)

请注意这里的 AS 子句是如何给输出字段重新命名的。（AS 子句是可选的。）

92 WHERE;

一个查询可以使用 WHERE 子句"修饰"，声明需要哪些行。 WHERE 子句包含一个布尔（真值）表达式，只有那些布尔表达式为真的行才会被返回。 允许您在条件中使用常用的布尔 *** 作符（AND，OR， 和 NOT）。 比如，下面的查询检索旧金山的下雨天的天气：

mydb#SELECT FROM weather

WHERE city = 'San Francisco' AND prcp > 00;

结果：

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 025 | 1994-11-27

(1 row)

93 排序;

您可以要求返回的查询是排好序的：

mydb#SELECT FROM weather

ORDER BY city;

得出结果：

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

Hayward | 37 | 54 | | 1994-11-29

San Francisco | 43 | 57 | 0 | 1994-11-29

San Francisco | 46 | 50 | 025 | 1994-11-27

在这个例子里，排序的顺序并非绝对清晰的，因此您可能看到 San Francisco 行随机的排序。 但是如果您使用下面的语句，那么就总是会得到上面的结果

SELECT FROM weather

ORDER BY city, temp_lo;

您可以要求查询的结果按照某种顺序排序， 并且消除重复的行输出：

mydb#SELECT DISTINCT city

FROM weather;

得出结果：

city

---------------

Hayward

San Francisco

(2 rows)

创建一张志愿者的数据表,记录每批参加志愿活动的人员名单。其中人员信息保存在json字段中。

知识点 : (1)postgresql中自增长的id创建。 (2)修改表字段语句。 (3)标准sql中table name ,column name双引号。

查询年龄大于等于25岁以上的志愿者

知识点 : (1)查询结果的的row number生成。 (2)获取json对象中的子对象。 (3)转换json对象属性的数据类型。

PostgreSQL 中 Page 是一个磁盘 Block 上的一个抽象结构，用于描述 Block 内部的数据结构与组织形式。

所有数据块在读写时，必须按 Page 格式进行访问 *** 作。

PostgreSQL 11 的 Page 格式（包含 3 行数据）如下：

行指针之前的 Page Header 总空间消耗为： (64 + 16 6 + 32) bit / 8 = 24 Byte

以下分别对这些结构以及对应的标志位的值进行说明：

Tuple 类型和行中各列数据的头部信息共享相同的数据结构，所以可以用相同的方法来构建和检查。但需求略有不同，数据不需要事务可见性信息，它需要一个长度字段和一些嵌入式类型信息。我们可以通过覆盖 Heap Tuple 上的 xmin/cmin/xmax/cmax/xvac 字段来实现数据上的需求。

Heap tuple 的头部信息，为了避免空间浪费，应该将字段以一种避免结构扩充的方式来布局。

通常，内存中所有的 tuples 都会使用数据字段进行初始化，当一个 tuple 需要写入表中时，事务相关的字段将会被写入，并覆盖数据字段。

Heap tuple 的整体结构包括：

通过 pageinspect 扩展模块，可以在低层次观察 page 中的实际数据，而不用考虑事务及相关可见性限制，这通常用于 DEBUG 目的的数据研究。

其常用函数说明如下：

创建模块

创建测试表

查看 Page Header

数据含义解析：

查看 Page 中的记录（Tuple）

数据含义解析：

解析 Tuple 数据

尝试多次更新同一条一条数据

再次查看页面数据

数据含义解析：

删除一条数据

再次查看页面数据

数据含义解析：

通过跟踪 t_xmin, t_xmax, t_ctid 三个字段的变化，可以得到 Tuple 数据的多版本变化历史，这也是 PostgreSQL 的 MVCC 实现原理

PostgreSQL 的多版本（MVCC）与 Oracle 有很大的不同，在于其将多版本信息与表数据存储在一起，这种多版本实现方式有其优势与局限性。

优势

劣势

select pg_constraintconname as pk_name from pg_constraint inner join pg_class

on pg_constraintconrelid = pg_classoid where pg_classrelname = 'yourtablename' and pg_constraintcontype='p'

这个可以显示yourtablename表的主键

select pg_constraintconname as pk_name,pg_attributeattname as colname,pg_typetypname as typename from

pg_constraint inner join pg_class

on pg_constraintconrelid = pg_classoid

inner join pg_attribute on pg_attributeattrelid = pg_classoid

and pg_attributeattnum = pg_constraintconkey[1]

inner join pg_type on pg_typeoid = pg_attributeatttypid

where pg_classrelname = 'yourtablename'

and pg_constraintcontype='p'

这个可以显示出主键名，和主键关联的字段名,和字段名类型

一、索引的类型：

PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1 B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含 *** 作符" <、=和>"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配 *** 作符的查询，如LIKE、ILIKE、~和 ~，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2 Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于 *** 作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引 *** 作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3 GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的 *** 作符类型。

4 GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的 *** 作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN *** 作符类型，如：、=、&&等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1 B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。

2 GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3 GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三、组合多个索引：

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND *** 作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的 *** 作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

CREATE UNIQUE INDEX name ON table (column [, ]);

五、表达式索引：

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

SELECT FROM test1 WHERE lower(col1) = 'value';

此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

SELECT FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。

从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。

六、部分索引：

部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：

1 索引字段和谓词条件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip > inet '1921681000' AND client_ip < inet '192168100255');

下面的查询将会用到该部分索引：

SELECT FROM access_log WHERE url = '/indexhtml' AND client_ip = inet '212781032';

下面的查询将不会用该部分索引：

一个不能使用这个索引的查询可以是

SELECT FROM access_log WHERE client_ip = inet '19216810023';

2 索引字段和谓词条件字段不一致：

PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查询一定会用到该部分索引：

SELECT FROM orders WHERE billed is not true AND order_nr < 10000;

那么对于如下查询呢？

SELECT FROM orders WHERE billed is not true AND amount > 500000;

这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。

下面的查询将不会用到部分索引。

SELECT FROM orders WHERE order_nr = 3501;

3 数据表子集的唯一性约束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。

七、检查索引的使用：

见以下四条建议：

1 总是先运行ANALYZE。

该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。

2 使用真实的数据做实验。

用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。

3 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。

4 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

Tools -> Dispaly Preferences -> General Settings ->

General标签 -> Diagram ->

取消勾选Show page delimiter，

点击OK，

这样就能去掉背景中的黑色网格线了。

多个表创建同一个字段的时候，

比如都创建id属性，会报冲突，

不允许创建同一个字段，

Tools -> ModelOptions

取消勾选Model Settings中的Data Item下的

Unique code

Allow reuse

项目的表注释字数很多时，

自动生成表的时候，

注释就会出现字数显示不全的问题。

表的注释：

设置dbms的属性，找到mysql50-->script-->objects-->Table-->TableComment:

value内容如下：

60代表生成注解的最大字符数，修改这个数值就搞定了,

可以修改为600等。

新建字段时，

需要选择字段的类型和长度，

可以设置一个常用的默认值，

这样每次新建一个字段时，

只需要修改不一样的字段，

而不用每次都设置类型和长度了。

PowerDesigner提供了很多字段的属性，

不需要使用全部的属性，

可以过滤一下，只显示需要的属性。

在概念数据模型转换到物理数据模型时，

字段的类型也会自动对应到物理数据类型，

如果觉得映射关系不满意，

可以修改内部类型对应的物理类型，

比如下面的Internal的DT类型，

对应PostgreSQL的物理类型为TIMESTAMP。

创建字段时，

如果Name和Code不相同，

可以关闭自动映射。

在表的位置发生移动后，

连接线会变得很乱，

可以重新布局连接线。

生成报表时，

确认表展示的顺序，

可以按照创建时间排序。

生成报表时，

报表的Boolean字段,

显示的是True和False,

可以修改为"是"和"否"。

生成报表时，

可以设置表格需要显示的属性，

以及对应的宽度，

宽度可以设置为比例或者绝对值，

从而让报表更美观。

原因是表设计时把Generate属性设为取消了，

勾选Generate即可解决：

数据库中空字段分为 NULL '' 判断是否为NULL时用 IS NULL 判断是否为'' 用!='' 比如 select from table where value !=''; select from table where date IS NOT NULL;

以上就是关于pgAdmin如何使用postgresql应该怎么设置全部的内容，包括:pgAdmin如何使用postgresql应该怎么设置、PostGreSQL Json数据存储和条件查询、AntDB/PostgreSQL内部原理：表Page结构解析等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！