postgresql中字符串的数字形式查询

在数据库执行sql查询时,需要在where部分限制执行范围为某字段为数字字符串形式。在使用LIKE语句时遇到了困难。LIKE只支持%和_两种通配符(前者为不限长度的字符串,后者为单个字符)。

其解决方案是使用~函数符号,这是支持正则形式的like功能代替。

因此解决方案就是:

select * from my_table where my_field ~ '^[0-9]+?$'

这样其含义就是查找所有纯数字形式的字符串。

postgresql: ‘not in’ subquery

在使用sql语句进行查询时突然发现一个语句不能返回合适结果:

select count(*) from weibo.poi where poiid not in (select poiid from weibo.url) and poiid not in (select poiid from weibo.url_bad);

正常应该有结果,但此语句返回空集。
搜索后发现后面的not in语句中存在null值,导致返回空集。因此应该这样修改:

select count(*) from weibo.poi where poiid not in (select poiid from weibo.url) and poiid not in (select poiid from weibo.url_bad where poiid is not null);

使用window函数处理表中相邻行间类似数据

出租车轨迹数据中,有时候同一辆车会在同一个空间位置发回来很多数据,这时除了时间字段外其他字段内容都相同。为了更好的进行数据清洗,需要处理这样的数据,即将其进行缩减。

数据表定义为:

CREATE TABLE public.taxi_2010
(
tid integer NOT NULL,
isempty smallint,
gpstime timestamp without time zone,
lon real,
lat real
);

比如下面的第1条和第2条就是重复的数据。

 tid  | isempty |   lon   |   lat   |       gpstime       

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

 10037 |       1 | 121.486 | 31.3117 | 2015-04-01 00:31:07

 10037 |       1 | 121.486 | 31.3117 | 2015-04-01 00:31:15

 10037 |       1 | 121.486 | 31.3118 | 2015-04-01 00:31:17

 10037 |       1 | 121.486 | 31.3121 | 2015-04-01 00:31:22

要剔除这样重复的点,可以使用PostgreSQL中的window函数进行处理:

select t1.tid,t1.isempty,t1.lon,t1.lat,t1.gpstime from
(select tid,isempty,lon,lat,gpstime,row_number() over (order by tid,gpstime) as seq1 from taxi_2010 t
order by tid,gpstime) t1
inner join
(select tid,lon,lat,gpstime,row_number() over (order by tid,gpstime) as seq2 from taxi_2010 t
order by tid,gpstime) t2
on t2.seq2=t1.seq1+1 and t1.tid=t2.tid and (not (t1.lat::numeric=t2.lat::numeric and t1.lon::numeric=t2.lon::numeric));

其中,row_number()函数提取当期行号作为唯一id,inner join提取两表中共有的要素,通过行号+1提取邻近行。

参考:

https://blog.csdn.net/u013986802/article/details/74315870

Install psycopg2 on mac sierra

需要在我的mbp上进行数据入库测试,因此就需要安装psycopg2,但安装的时候一直有提示:

ld: library not found for -lssl

clang: error: linker command failed with exit code 1 (use -v to see invocation)

开始搜索以为库的位置不对,比如之前类似的问题:http://stackoverflow.com/questions/27264574/import-psycopg2-library-not-loaded-libssl-1-0-0-dylib

后来感觉不太对,又加上sierra关键词找,果然找到一个类似问题:
http://stackoverflow.com/questions/39767810/cant-install-psycopg2-package-through-pip-install-is-this-because-of-sierra
发现解决方法非常简单,就是安装xcode-select工具。

xcode-select –install

然后再安装psycopg2就没有任何问题了:

sudo pip install psycopg2

postgresql中字符串与数字的混合排序

数据中心部分数据的标题是混合字符串与数组的,比如:

… site no. 1
… site no. 10
… site no. 2

之前,是按照title的字符进行升序排序了,排序结果就如上所示。这里和我们常规理解有所区别的就在于其排序是按照1、10、11、…、2、3进行排序的,而我们通常想要的结果是希望按照站点编号的自然数大小进行排序,即1、2、…、9、10…。

之前的sql语句是这样的:

… order by title

而要满足这个要求,第一个改造的结果是这样的:

… order by substring(title, ‘[^0-9]*’), (substring(title, ‘No.([0-9]+)’))::int

这样可以满足上面的需求,即No.1之类的标题可以排序,但若数字在中间就难以处理了,比如:

…10 July, 2012
…9 August, 2012

因此又继续改造:

…order by regexp_replace(title,'[0-9]+’,”,’g’),(substring(title, ‘([0-9]+)’))::int

这样可以解决上面的问题,但还是不完美,即有多个间断的数字,排序还是有问题的。
比如:

…10 July, 2012
…9 July, 2013

这种问题留待以后解决吧。

postgresql: 提取同类别的第一个排序记录

在数据中心中,我们记录了元数据的多个版本,现在需要提取所有元数据的最新版本,如何用一个sql来表达呢?

CREATE TABLE mdversion
(
  id serial NOT NULL,
  xml text NOT NULL DEFAULT ''::text,
  ts_created timestamp without time zone NOT NULL DEFAULT now(),
  uuid uuid NOT NULL,
  CONSTRAINT mdversion_pkey PRIMARY KEY (id)
)

 

第一感觉,是用group功能,但group不能对同类下的行进行排序,所以解决不了这个问题。

 

用这几个关键词搜索:postgresql group  get first,找到了一个直接相关的解决方案:

Select first row in each GROUP BY group?

解决方案很简单,就是用distinct on方法:

select distinct on (uuid) id from en.mdversion
order by uuid,ts_created desc

 

 

用SQL的正则处理人名的缩写问题

数据中心的数据在向期刊进行投稿时,通常需要对数据引用有一定的格式变换。比如,Nature Scientific Data期刊,对Data Citation部分的建议是:

1. Lastname1, Initial1., Lastname2, Initial2., … & LastnameN. InitialN. Repository_name Dataset_accession_number_or_DOI (YYYY).

数据中心的数据作者在数据库中是有记录的,其通过一个表存储:

CREATE TABLE datadoi
(
id serial NOT NULL,
doi character varying(250) NOT NULL DEFAULT ''::character varying,
authors text[],
uuid uuid,
author_en text[],
CONSTRAINT datadoi_pkey PRIMARY KEY (id),
CONSTRAINT datadoi_doi_key UNIQUE (doi)
)

 

可以看出,作者是通过一个字符串数据存储。在PostgreSQL中,数组是通过如下形式记录的:

{value1,value2,…}

对于数据的作者信息,数据中心则要求其通过如下的形式记录到数据库中:

Wang Yijia, Zhang Eryi,…

而为了满足期刊的投稿要求,则需要将格式转换为:

Wang Y., Zhang E.,…

在数据库里,最终通过这样的形式进行处理:

select translate(regexp_replace(datadoi.author_en::text,'\s+(\w)\w*"',' \1.','g'),'{}"','')

 

即,将数组转换为字符串,剔除大括号,用逗号区分不同作者,用空格区分作者的姓和名,提取名的首字母,然后全局替换即可。

ubuntu 14.04重新构建postgresql数据库

一台服务器,在升级过程中数据库发生了损坏,已经恢复不了,因此决定将旧版本数据备份后删除,直接创建一个新的数据库。

sudo pg_dropcluster --stop 9.1 main

sudo apt-get remove postgresql-9.1

sudo pg_createcluster 9.3 main

然后修改postgresql配置文件,修改ip绑定地址,将localhost修改为*,

sudo nano /etc/postgresql/9.3/main/postgresql.conf

加入远程访问的ip地址控制,

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

然后重新启动数据库服务:

sudo service postgresql start

添加一个新的超级用户:

sudo createuser -P -s username

然后就通过pgadmin3进行远程控制访问。

ubuntu 14.04下升级postgresql 9.1到9.3

一台服务器从12.04升级到14.04,在升级过程中ssh中断了,重新连接后只能直接升级了,在升级过程中遇到了postgresql问题。

1. 升级错误

正在设置 postgresql-client-9.3 (9.3.6-0ubuntu0.14.04) …
update-alternatives: 错误: 候选项 pg_basebackup.1.gz 不能作 psql.1.gz 的次要项:它是 postmaster.1.gz 的次要项
dpkg: 处理软件包 postgresql-client-9.3 (–configure)时出错:
子进程 已安装 post-installation 脚本 返回了错误号 2

这个错误在serverfault上找到了答案:

http://serverfault.com/questions/563073/postgresql-9-3-installation-fails

解决方法是:

sudo update-alternatives --remove postmaster.1.gz /usr/share/postgresql/9.1/man/man1/postmaster.1.gz

 

然后继续升级:

sudo apt-get install -f

 

2. 数据库升级

升级完成后,默认的9.1版数据并没有升级,需要手动升级。可以采用这种方案进行快速升级(做好备份工作):

sudo service postgresql stop
sudo pg_dropcluster --stop 9.3 main
sudo pg_upgradecluster 9.1 main

升级完成后,启动9.3服务:

sudo service postgresql start 9.3

 

使用后确认升级成功,可以删除9.1的数据:

sudo pg_dropcluster --stop 9.1 main

 

多表的合并查询SQL

数据中心的专题文献,涉及到多个表。前期是保存到mdref表,通过类型字段定义,现在又增加了一个新表,专门用来管理专题文献。而有的时候,可能两个表的专题都会涉及到。因此SQL查询比较复杂。

需求:

1. 保留两个表的数据

2. 保持文献的排序

最终SQL如下:

select  r.* from reference r right join
(
select refid,place from (
(
select sr.refid,sr.place from sourceref sr left join datasource ds on ds.sourceid=sr.sourceid
where ds.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e'
order by sr.place
)  
union
(
 select mr.refid,mr.place from mdref mr where  mr.reftype=4 and mr.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e' and mr.refid not in (
select sr.refid from sourceref sr left join datasource ds on ds.sourceid=sr.sourceid
where ds.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e'
)
)
) a
) p on r.id=p.refid order by p.place

用到了union、临时表。