Posts

Showing posts with the label postgresql

Swap primary keys between two records in the same table in Postgres

What if we need to swap two primary keys on the same table? What's the best and efficient way to do the same? let's check Now:- Let's assume we have a user table which has 3 columns among all the rows I have taken two sample rows which are the target rows for us. Id Name Email 101 Sabyasachi XX@gmail.com 105 Saghosh xxx@gmail.com We need to swap the primary keys (which are unique by nature) between those two records so our desired result will be something like   Id Name Email 105 Sabyasachi XX@gmail.com 101 Saghosh xxx@gmail.com Now lets buield a query using CTE(common table expression) to solve the problem:- with source_user as (   select     id   from     users   where     id = 101 ), destination_user as (   select     id   from     users   where     id

Postgres database is not running after OSX crash

Every one might have face the problem that Postgres id not running after OSX is crashed. Every time you try to start the Postgres server it will say : - another server might be running; trying to start server anyway You can follow the below steps to get rid of the problem. Step 1: Open your terminal and run   pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start   It will Say   pg_ctl: another server might be running; trying to start server anyway Step 2:  Check the log $ tail -f /usr/local/var/postgres/server.log It will print something like this FATAL:  lock file "postmaster.pid" already exists HINT:  Is another postmaster (PID 326) running in data directory "/usr/local/var/postgres"? FATAL:  lock file "postmaster.pid" already exists HINT:  Is another postmaster (PID 326) running in data directory "/usr/local/var/postgres"? Step 3: Delete the PID file. $ rm /usr/local/var/postgres/postmaster.pid S

How To Setup Ruby on Rails with Postgres

Postgres is a great open source database management system. It's pretty easy to use postgres with Ruby on Rails. Step 1: Install postgress in your system. If you already have postgres installed in your system then skip it else follow the  link . Step 2: Create new project rails new pg_demo You can also create using rails new pg_demo --database=postgresql Step 3: include the pg gem in your gem file gem 'pg' Step 4: run bundle command bundle Step 5: It will give you some error like An error occurred while installing pg (0.17.1), and Bundler cannot continue. Make sure that `gem install pg -v '0.17.1'` succeeds before bundling. So let's try to install the  pg gem first gem install pg It will again throw an error  :( Building native extensions.  This could take a while... ERROR:  Error installing pg: ERROR: Failed to build gem native extension.     /home/sabyasachi/.rvm/rubies/ruby-2.1.0/bin/ruby extconf.rb checking for pg_config... y

How To Install PostgreSQL on Ubuntu 12.04

Step 1: Before postgres install, we should quick perform a quick update. apt-get update Step 2: Now install Postgres using the command line.   sudo apt-get install postgresql postgresql-contrib This will install the latest version available in your Ubuntu. Step 3: Install the client   sudo apt-get install postgresql-client Step 4:  For a handy GUI for PostgreSQL install pgAdmin III.  sudo apt-get install pgadmin3 Step 5:  We need to change the PostgreSQL postgres user password; sudo -u postgres psql postgres Set a password for the "postgres" database role using the command: \password postgres Step 6: Exit from Postgres   \q That's it we are done with the installation.