Ruby+PostgreSQL on Ubuntu
This extends the earlier post on installing and configuring Ruby 3.3.0 on Ubuntu 22.0.4. Please refer to that earlier post to install Ruby. This post shows you how to install the necessary libraries and Ruby Gems for PostgreSQL.
You need to install the libra-dev package, as shown:
sudo apt install postgresql libpq-dev |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done postgresql is already the newest version (14+238). Suggested packages: postgresql-doc-16 The following NEW packages will be installed: libpq-dev 0 upgraded, 1 newly installed, 0 to remove and 6 not upgraded. Need to get 142 kB of archives. After this operation, 590 kB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy pgadmin4/main amd64 libpq-dev amd64 16.1-1.pgdg22.04+1 [142 kB] Fetched 142 kB in 7s (20.7 kB/s) Selecting previously unselected package libpq-dev. (Reading database ... 247065 files and directories currently installed.) Preparing to unpack .../libpq-dev_16.1-1.pgdg22.04+1_amd64.deb ... Unpacking libpq-dev (16.1-1.pgdg22.04+1) ... Setting up libpq-dev (16.1-1.pgdg22.04+1) ... Processing triggers for man-db (2.10.2-1) ... |
Next, you need to install the PG Gem:
gem install pg |
Display detailed console log →
Fetching pg-1.5.4.gem Building native extensions. This could take a while... Successfully installed pg-1.5.4 Parsing documentation for pg-1.5.4 Installing ri documentation for pg-1.5.4 Done installing documentation for pg after 3 seconds 1 gem installed |
You can now write a postgres_version.rb program to verify a connection to the PostgreSQL database, like:
# Include Ruby Gem libraries. require 'rubygems' require 'pg' # Begin block. begin # Create a new connection resource. db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student') # Create a result set. stmt = db.query('SELECT version() AS version') # Read through the result set hash. stmt.each do | row | puts "#{row['version']}" end # Release the result set resources. stmt.freeze rescue PG::Error => e # Print the error. puts "ERROR #{e.error} (#{e.sqlstate})" puts "Can't connect to the PostgreSQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
Call the postgres_version.rb program with this syntax:
ruby mysql_version.rb |
It should return:
PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit |
The postgres_columns.rb script returns a couple columns concatenated into a single column:
# Include Ruby Gem libraries. require 'rubygems' require 'pg' # Begin block. begin # Create a new connection resource. db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student') # Create a result set. stmt = db.query("SELECT CONCAT(nh.last_name, ', ', nh.first_name) AS name " + \ "FROM new_hire nh " + \ "ORDER BY nh.last_name") # Read through the result set hash. stmt.each do | row | out = "" i = 0 while i < stmt.fields.count() # Check when not last column and use the: # - Hash returned by the result set for the value, and # - String array value returned by the statement object # as the name value of the hash by leveraging its # numeric index. if i < stmt.fields.count() - 1 out += "#{row[stmt.fields[i]]}" out += ", " else out += "#{row[stmt.fields[i]]}" end i += 1 end puts "#{out}" end # Release the result set resources. stmt.freeze rescue PG::Error => e # Print the error. puts "ERROR #{e.error} (#{e.sqlstate})" puts "Can't connect to PostgreSQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
Call the postgres_columns.rb program with this syntax:
ruby mysql_columns.rb |
It should return:
Chabot, Henry Lewis, Malcolm |
As always, I hope this helps those looking to learn and solve a problem. You can find the PG Gem documentation here.
Subscribe via RSS