postgresql console interface

Here is the command to connect to postgresql through console,

> sudo -u postgres psql

The above command will connect to psql.

>  sudo -u postgres psql
psql (9.1.9)
Type "help" for help.

postgres=# 


Now you can run all the sql queries through this interface.



Thank You,
Uma Mahesh.




 

ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint

ActiveRecord::RecordNotUnique (PG::Error: ERROR:  duplicate key value violates unique constraint

ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(143) already exists.

The above issue will raise when you dump data to database and start creating records after that. You need to set the sequence value manually to over come this issue. Below is the command to set the value for 'users_id_seq'


SELECT setval('users_id_seq', (SELECT max(id) FROM users));

In the above query 'users' is the tale and you are manually changing the 'users_id_seq' value to max(id) of users.


Thank You,
Uma Mahesh.

PG ERROR While creating GIN Index

Hi,

I am getting below error while creating GIN Index in rails through migration

rake aborted!
An error has occurred, this and all later migrations cancelled:

PG::Error: ERROR:  data type text has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
: CREATE INDEX discussions_gin_question ON discussions USING GIN(question)

Tasks: TOP => db:migrate
(See full trace by running task with --trace)


After goggling I came to see its due to not making that column as to_tsvector

I have changed my migration to execute as below

CREATE INDEX products_gin_question ON products USING GIN(to_tsvector('english', question))


Thank You,
Uma Mahesh.

Creating GIN Index for database columns in rails Migration.

Hi,

Indexing the database columns plays an important role in database query optimisation. Indexing also increases the application performance.

While working with full text search, I recommend to use the GIN Index. It will speed-up the full-text search.


GIN (Generalised Inverted Index)-based index. While creating Column as GIN Index we need to make that column as tsvector type.

Here is the full stack view of GIN Index : http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html


Coming rails migration we can create GIN Index by simple commands as below

Create migration file:

> rails g migration add_ginindex_products

class AddGinindexProductions < ActiveRecord::Migration
  def up
    execute "CREATE INDEX products_gin_question ON products USING GIN(to_tsvector('english', question))"
  end

  def down
    execute "DROP INDEX products_gin_question"
  end
end


> rake db:migrate

The above migration will create the GIN Index for the products table.

Thank You,
Uma Mahesh.

Full text search in postgres

Hi,

Here is the few things while working with full text search in postgres sql.

The first preference is by indexing the columns. The second one is by using lexema search

To implement full text search we have two functions in postgres tsvector from a document and a tsquery from a user query

PostgreSQL provides the function to_tsvector for converting a document to the tsvector data type.

SELECT to_tsvector('english', 'a man  bat rat on a doll - it took the ball');

> "'ball':11 'bat':3 'doll':7 'man':2 'rat':4 'took':9"


In the above result the tsvector does not contain a,on ..

 tsvector internally do parsing by breaking up to tokens and using some logic internally. To know more about what's tsvector actually doing please go through the postgres site

The next function tsquery:

PostgreSQL provides the functions to_tsquery for converting a query to the tsquery data type.

to_tsquery creates a tsquery value from querytext, which must consist of single tokens separated by the Boolean operators & (AND), | (OR) and ! (NOT).

 SELECT to_tsquery('english', 'The & mat & bats');
> "'mat' & 'bat'"


By using the above tow functions we can implement text search in postgres. Below is the sql query to implement it

select * from users where to_tsvector('english', description) @@ to_tsquery('english', 'good | person');


the above query will do the full text search in the description column in the user table.

Thank You,
Uma Mahesh.

postgres_ext gem native postgres datatypes in rails

Hi,

We are using different datatypes depending on the requirement of data that is being saved in database.

Coming to postgres database, it supports many datatypes and we can't use all those datatypes directly in rails.

To over come this problem, we had a new gem postgres_ext


postgres_ext supports 3.2 and above version of rails.

postgres_ext adds migration and schema.rb support for the following PostgresSQL type:

    INET
    CIDR
    MACADDR
    UUID
    Arrays


example:

create_table :users do |t|
  t.inet :myip


  t.cidr :mysubip


  t.macaddr :ip_address


  t.uuid :member_id

  t.integer :friend_ids, :array => true

end


postgres_ext converts INET and CIDR values to IPAddr instances.

example:

create_table :inet_examples do |t|
  t.inet :ip_address
end

class MyExample < ActiveRecord::Base
end

my_example = MyExample.new
my_example.ip_address = '127.0.0.0/34'
my_example.ip_address
# => #<IPAddr: IPv4:127.0.0.0/255.255.255.0>
my_example.save

my_example_u = MyExample.first
my_example_u.ip_address

# => #<IPAddr: IPv4:127.0.0.0/255.255.255.0>



array type:

example;

create_table :people do |t|
  t.integer :favorite_numbers, :array => true
end

class User < ActiveRecord::Base
end

user = User.new
user.like_numbers = [1,2,3]
user.flike_numbers
# => [1,2,3]
user.save

user_2 = user.first
user_2.like_numbers
# => [1,2,3]
user_2.like_numbers.first.class
# => Fixnum



here is the git url: https://github.com/umamahesh/pg_array_parser

Thank You,
Uma Mahesh.

command to get database dump file from postgres database

Hi,

Here is the command to get database dump in postgres

sudo -H -u postgres pg_dump <databasename> > <dumfilename>

Thank You,
Uma Mahesh