Posts match “ PostgreSQL ” tag:

如何在rails中使用hstore模块

首先解释一下什么是 hstore

hstorepostgresql 自带的一个模块,可以在字段内用 key/value 格式存储文档数据。

rails4 开始已经在自带的PostgreSQLAdapter中加入了hstore的支持。使用方式如下。

class CreateProfiles < ActiveRecord::Migration
  #启用hstore模块

  enable_extension 'hstore' unless extension_enabled?('hstore')
  def change
    create_table :profiles do |t|
      t.hstore 'settings'
      t.timestamps
    end
    #可以给hstore字段加索引,, 类型有 gin 和 gist

    add_index :profiles, :settings, using: :gin
  end
end

关于 gingist 索引格式的选择, 请看官方文档。

In choosing which index type to use, GiST or GIN, consider these performance differences:

GIN index lookups are about three times faster than GiST

GIN indexes take about three times longer to build than GiST

GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 54.3.1 for details)

GIN indexes are two-to-three times larger than GiST indexes

As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast if the number of unique words (lexemes) is under 100,000, while GIN indexes will handle 100,000+ lexemes better but are slower to update.

Note that GIN index build time can often be improved by increasing maintenance_work_mem, while GiST index build time is not sensitive to that parameter.

GINGiST 索引快三倍,但也多花了三倍时间。

postgresql 的其他模块用法可以在官方文档找到

其中注意,保存完 hstore 字段后的数据, 如果要修改时, 需要执行字段名_will_change! 这个方法

## you need to call _will_change! if you are editing the store in place

profile.settings["color"] = "green"
profile.settings_will_change!
profile.save!

ubuntu安装Postgresql

安装

sudo apt-get install postgresql

输入密码,确认。下载安装完毕之后会创建一个postgres用户,默认密码为空。

配置

修改postgres用户密码

sudo passwd postgres

切换到postgres用户

sudo su postgres

登录到postgres数据库

psql postgres

会看到一下文字

psql (8.4.5)
Type "help" for help.

postgres=#

然后输入下面的命令,password为你要设置的密码

ALTER USER postgres with PASSWORD 'password'

输入\q返回到终端

创建用户和数据库

输入

createuser -drSP blog  #创建一个blog用户,但不是超级管理员


createdb -O blog blogdb  #创建一个属于blog用户的blogdb数据库

安装pgadmin

输入

sudo apt-get install pgadmin3

安装完毕,运行

pgadmin3

设置其他网络连接数据库

修改/etc/postgresql/8.4/main/pg_hba.conf:

# Database administrative login by UNIX sockets

# local   all         postgres                          ident


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD


# "local" is for Unix domain socket connections only

local   all         all                               md5 #ident

# IPv4 local connections: 

host    all         all         127.0.0.1/32          md5
# IPv6 local connections:

host    all         all         ::1/128               md5  

修改/etc/postgresql/8.4/main/postgresql.conf,

listen_address = '*' #允许其他机器访问

重启数据库

sudo /etc/init.d/postgresql-8.4 restart

安装完成

ubuntu下gem安装pg出错

在安装pg时,系统报了如下的错误

/home/zool/.rvm/rubies/ruby-1.8.7-p302/bin/ruby extconf.rb 
checking for pg_config... no
checking for libpq-fe.h... no
Can't find the 'libpq-fe.h header
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Provided configuration options:
    --with-opt-dir
--without-opt-dir
--with-opt-include
--without-opt-include=${opt-dir}/include
--with-opt-lib
--without-opt-lib=${opt-dir}/lib
--with-make-prog
--without-make-prog
--srcdir=.
--curdir
--ruby=/home/zool/.rvm/rubies/ruby-1.8.7-p302/bin/ruby
--with-pg
--without-pg
--with-pg-config
--without-pg-config
--with-pg-dir
--without-pg-dir
--with-pg-include
--without-pg-include=${pg-dir}/include
--with-pg-lib
--without-pg-lib=${pg-dir}/lib

请教GOOGLE大神后,找到如下解决方案

sudo apt-get install libpq-dev libpq5

安装更新后解决问题

在rails4与postgresql中使用uuid作为主键

安装步骤

先确保已经安装了 ruby on rails 和 postgresql

rails new uuids —database postgresql
rails generate migration enable_uuid_ossp_extension
rails generate model document title:string author:string

打开postgresql的uuid-ossp插件

class EnableUuidOsspExtension < ActiveRecord::Migration
  def change
    enable_extension uuid-ossp
  end
end

设置migration

class CreateDocuments < ActiveRecord::Migration
  def change
   create_table :documents, id: :uuid, default: 'uuid_generate_v4()'  do |t|
      t.string :title
      t.string :author
      t.timestamps
    end
  end
end

运行migration

rake db:create
rake db:migrate

安装完成

在console里测试一下

rails c

irb(main):011:0> Document.create(title: “PostgreSQL UUID!”, author: “Fox ZoOL”)
=> #<Document id: “ec31b888-a491-49e7-afa9-ab59e9131d78”, title: “PostgreSQL rocks!”, author: “Fox ZoOL”, created_at: “2015-01-10 21:02:17”, updated_at: “2015-01-10 21:02:17”>

Tips

由于主键已经是uuid了, Document.firstDocument.last 的返回值会出错,需要重写一下scope

class Document < ActiveRecord::Base
  scope :first, -> { order(created_at).first }
  scope :last, -> { order(created_at DESC).first }
end