Encore's Blog

Web 开发者 | 专注于WEB应用程序的软件开发人员
PostgreSQL - 如何根据一组已知结果集进行排序
数据库 2019-01-12 邵壮

前提:  已知的一组结果集待解决问题:在公司列表页面,把一些特定公司的显示在最前面给定一批公司编号,需要按照给定编号进行排序经过 Google 和查看 PostgreSQL Document 后,找到一个比较好的解决方案,可以使用 array_position function 来轻松完成此项需求. 接下来,我们来看看具体如何使用 ARRAY_POSITION function 场景一: 将公司 (100,43,13,89) 排在列表最前面SELECT id, ARRAY_POSITION(ARRAY[100, 43, 13, 89], id::integer) AS position from companies ORDER BY position ASC NULLS LAST; ------------------------------------ | id | p...

PostgreSQL - 窗口函数 (OVER) 的使用介绍
数据库 2018-12-31 邵壮

关于: PostgreSQL的窗口函数OVER窗口函数窗口函数是基于结果进行计算,于聚合函数不同的是窗口函数不会将结果集进行分组计算并输出一行,而是将计算出的结果合并到输出的结果集上,并返回多行。使用窗口函数能大幅度简化SQL代码。使用说明OVER 表示窗口函数的关键字PARTITIOIN BY 属性对查询返回的结果进行分组,之后窗口函数处理分组的数据ORDER BY 属性设定结果集的分子数据排序- OVER()聚合函数后接OVER属性的窗口函数表示在一个查询结果集上应用聚合函数。接下来,我们将看看 avg() 聚合函数后接 OVER属性的窗口函数, 此窗口函数用来计算分组后数据的平均值.首先,我们创建新表score并插入对应数据, 如下所示:CREATE TABLE score (id serial primary key,                     subject...

标签: PostgreSQL
PostgreSQL - 在大数据表中进行随机数据抽样
数据库 2018-10-09 Encore Shao

PostgreSQL - 数据抽样 (RANDOM vs. TABLESAMPLE) 首先我们来看看数据总量: (共计300W+)(encore@localhost) [pg10] > select COUNT(*) from big_data_table; count --------- 3212557 (1 row) Time: 242.859 ms1. 使用 RANDOM() 函数,这可能也是我们最常用的一种随机查询方式(encore@localhost) [pg10] > EXPLAIN ANALYZE select * from big_data_table ORDER BY random() limit 1; QU...

如何在PostgreSQL中将用户更改为超级用户
数据库 2018-09-13 邵壮

在PostgreSQL中修改用户权限的范围可以从简单到极其复杂,具体取决于实际需要的许可粒度。  但是,在大多数情况下,强大的ALTER USER命令应该用于执行从允许用户登录,创建数据库,管理角色甚至成为 SUPERUSER 帐户的所有操作。 我们将简要探讨ALTER USER命令的强大功能,以便您可以根据需要轻松执行各种权限分配和删除。 创建新用户 在我们改变用户权限之前,我们应该建立一个新的用户帐户(通常称为ROLE)。 首先,我们将列出所有现有用户: =# SELECT usename FROM pg_user; usename ---------- postgres (1 row) 默认情况下,postgres通常是唯一存在的用户,因此我们要创建一个新的encore用户来管理我们的数据库。 这...

标签: PostgreSQL
PostgreSQL - 为终端交互式psql提供密码
数据库 2018-09-08 邵壮

前提:我们拥有一个远程PostgreSQL数据库的访问权限。 当我们在本地使用psql连接时,每次都需要提供密码,才可以继续操作,我想,可能会有人和我的想法一样,有点麻烦,有没有什么方便的方式来连接我们已拥有权限的PG呢? 以下我们来介绍两种方法为非交互式地为PostgreSQL的交互式终端psql命令提供密码, 每种方法都允许使用基于终端的PostgreSQL实用程序轻松编写shell脚本,因为您可以从密码文件或环境变量中提供用户凭据。 1. 使用密码文件提供密码 要使用此方法,请在主目录中创建~/.pgpass文件并限制其权限,以便实用程序不会忽略它。$ touch ~/.pgpass $ chmod 0600 ~/.pgpass然后使用以下结构定义用户凭据。server:port:database:username:password需要注意的是,密码以外的每个字段都可以替...

标签: PostgreSQL
如何知道我们的数据库有多大?
数据库 2018-08-06 邵壮

很多时候我们需要关注数据库增长,以确保它不会超出你的磁盘,或者你可以提前做好准备。 接下来我们会说下很常见的问题 - 您的数据库有多大。 很幸运的是PostgreSQL已经有函数很快来帮助您轻松回答这个问题。  如何知道我们的数据库有多大? 使用: pg_database_size()函数,它接受数据库集群中数据库的名称并返回以字节为单位的大小。 当大多数数据库的大小为兆字节,千兆字节甚至太字节时,尝试破译字节可能会非常难看。 幸运的是,有一个名为pg_size_pretty()的漂亮函数,它使这些数字更具人性化, 可读性会更高些。 另外,还有一个在PG中有一个current_database()函数,它告诉我们当前连接到哪个数据库。  最后当我们把这些函数结合起来你就得到了想要的答案: SELECT pg_size_pretty(pg_data...

标签: PostgreSQL
在PostgreSQL上创建用户和数据库 - 并添加访问权限
数据库 2018-07-18 Encore Shao

注意:在Ubuntu 16.04.2上运行,PostgreSQL 10.0 关于PGSQL的一个好处是它带有一些实用程序二进制文件,如createuser和createdb。 所以我们将利用它。 作为Postgres的默认配置,启用了一个名为postgres的用户,用户postgres对操作系统上运行的整个PostgreSQL实例具有完全的superadmin访问权限。 $ sudo -u postgres psql 上面的命令以完全管理模式获取psql命令行界面。 创建用户 $ sudo -u postgres createuser <username> 创建数据库 $ sudo -u postgres createdb <dbname> 为用户添加密码 $ sudo ...

标签: PostgreSQL
Rails 中关于 Datetime 字段日期查询问题
工作 2018-06-04 邵壮

服务端信息: API Server: Rails 5 and PostgreSQL IOS:  SQLite3 问题:  时间查询问题. 需求:获取更新文章计数然后把新更新的数据同步到IOS,所以我们将最新文章的更新时间到同步到IOS中.  然后IOS通过发送此时间到API Server, 继而检测服务器端是否有新数据更新. 然而服务器端当无文章更新时, 我们API总是给我们返回 (1). 具体示例代码如下: pry(main)> updated_at = Article.order('updated_at DESC').first.updated_at => Sun, 03 Dec 2017 03:41:07 UTC +00:00 pry(main)> date_str = u...

标签: Rails PostgreSQL
Preload, Includes, Joins and Eagerload
数据库 2018-05-18 邵壮

Rails提供了四种不同的方式来加载关联数据。 接下面,我们将看看它们分别是如何工作的。 > Rails Version - 5.1.2 Preload -预加载 预加载在单独的查询中加载关联数据。 pry(main)> User.preload([:articles]).to_a # >> User Load (0.3ms) SELECT "users".* FROM "users" Article Load (4.0ms) SELECT "articles".* FROM "articles" WHERE "articles"."user_id" IN ($1, $2) [["user_id", 3], ["user_id",2]] 这是包含在默认情况下加载数据的方式。由于preload总是会生成两...

标签: Rails PostgreSQL
This website uses cookies to ensure you get the best experience on our website.
Got it!