Rails : Import CSV data to database

Actually there are various ways to achieve this. Firstly you can use the PostGreSQL’s copy┬ácommand. Secondly you can choose to use Ruby to load data.

Using copy command in PG

Create your table:

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

Copy data from your CSV file to the table: Continue reading

Note: Open (PG)PostGreSQL in command line and do some stuffs

To access postgres console:

$ sudo -u postgres -i

postgres@host:~$ psql

or

$ sudo -u postgres psql

[sudo] password for john: 
psql (9.3.12)
Type "help" for help.

postgres=# 

 

# For help
postgres=# help
 You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
 \h for help with SQL commands
 \? for help with psql commands
 \g or terminate with semicolon to execute query
 \q to quit
# For help
postgres=# \h
 Available help:
 ABORT CLUSTER DEALLOCATE END
 ALTER AGGREGATE COMMENT DECLARE EXECUTE
 ALTER COLLATION COMMIT DELETE EXPLAIN

Create database

# database command has to end with semicolon
postgres=# create database july_prod_dump1;
CREATE DATABASE

Alter permission

ALTER USER new_user CREATEDB

 

Ubuntu: Open localhost database in pgadmin

Make sure you have installed PGAdmin

sudo apt-get install pgadmin3

now set your database

$ sudo -u postgres psql

it will ask you password, get inside and alter the user’s password if you have not already set it

postgres=# alter user john with password 'mypassword';

and then open pg admin, fill the form and get inside

pgadmin-09 13:04:25

some helpful links

http://www.indjango.com/ubuntu-install-postgresql-and-pgadmin/