Set PostgreSQL environment variables

If we dont set PostgreSQL environmental variable, We cannot access PostgreSQL through the command line in Windows.But, we can access PostgreSQL using pgAdmin or SQL Shell(psql). To access the PostgreSQL through command line in window, follow the below steps.

Set PostgreSQL environment variables

Set PostgreSQL environment variables

Select numeric columns from a Data frame in R

We can get the list of columns in a data frame using sapply function.

In this article, we are going extract the numeric columns of diamonds dataset.

1. Diamonds data frame contains numeric and noon-numeric columns.


Load data from a text file into PostgreSQL Database

We can load data from text(flat) file,CSV(comma separated values), excel or from other database engines(Oracle,MySQL,..). Follow steps covers Load data from a text file into PostgreSQL Database.


COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ]

Where option can be:

Export PostgreSQL query result to file

We can take a backup file of database using pg_dump command, similarly can we take backup of a select query result.

Syntax to Export PostgreSQL query result to file:

copy(select * from table_name) to 'path';


How to copy or clone list in Python

Lists are ordered collection object type. Lists can contain any sort of object: numbers, strings, tuple,dictionary and even other lists. Lists are mutable objects.

How to copy or clone list in Python

1. Create List

>>> L=['Python','R','SAS','SPSS','Java','Data Analytics',[1,2,3,4],10,3.14]

Drop PostgreSQL database if there are active connections to it

Scenario: We want to drop database(assume database name as company_new) in PostgreSQL. But, the database contains more active sessions(idle). When we tried to drop database, we will receive following error message.

postgres=# drop database company; ERROR: database "company" is being accessed by other users DETAIL: There is 1 other session using the database.


Step1: Kill(terminate) connections from that particular database using query.

Kill PostgreSQL Session or Connection

Why we will session or connection?
If connection is in hung state.
If connection consumes more resources.
If connection(user pid) blocks other resources.

Solution: Kill the culprit process id. To kill any connection, the user who is going to kill pid(connection) must be super user in PostgreSQL.

R vs Python

R Programming Language Python Programming Language
R favor for Statisticians and Mathematicians Python favor for computer scientists and software engineers
R is Statistical tool Python is general purpose language

Switch database in PostgreSQL

To switch database in Sybase, we run command:

use database_name go

To switch database in SQL Serve, MySQL or Oracle, we run command:

use database_name;

But, when comes to PostgreSQL, we use \connect database_name; or \c database_name;


Create a copy of database in PostgreSQL

PostgreSQL allows us to use of any existing database on the server as a template when creating a new database.

Syntax to Create a copy of database in PostgreSQL


If we run the above command, it take all objects of originaldb to newdb.


Powered by k2schools