Category Archives: PostgreSQL

Rename table and column name for Oracle, SQL Server, Mysql & POSTGRESQL

Rename table and column name for Oracle, SQL Server, Mysql & POSTGRESQL

Following command for rename the Table Name

SQL Server

EXEC sp_rename 'oldtablename', 'newtablename';

Oracle

alter table tablename rename to  newtablename;

MYSQL

RENAME TABLE table_name TO new_table_name;

POSTGRESQL

ALTER TABLE table_name RENAME TO newtablename;

Following command for Rename Column Name in table

SQL Server

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Oracle

alter table tablename columname rename to newcolumname

MYSQL

alter table change oldcoumnname newcoklumnaate

POSTGRESQL

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

Advertisements

General commands for PostgreSQL database

General Commands for PostgreSQL database

Start, Stop and Restart PostgreSQL database

# service postgresql stop
Stopping PostgreSQL: server stopped
ok# service postgresql start
Starting PostgreSQL: ok

# service postgresql restart
Restarting PostgreSQL: server stopped
ok

 
Verify PostgreSQL Server is up and running

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

Connect the database in PostgreSQL

$ /usr/local/pgsql/bin/psql
\ connect DBNAME

 
Edit PostgreSQL queries in your favorite editor

\e

 
List the tables in PostgreSQL

\dt

List the database in PostgreSQL

\d

List of all database present in PostgreSQL

# \l  [Note: This is backslash followed by lower-case L]

List of databases
Name      | Owner    |Encoding
----------+----------+----------
backup    | postgres | UTF8
mydb      | ramesh   | UTF8
postgres  | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

Create a PostgreSQL Database

Method 1:
CREATE DATABASE mydb WITH OWNER ramesh;Method 2:
$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE

 
Delete/Drop an existing PostgreSQL database

# \l
List of databases

Name      | Owner    | Encoding
—---------+---———----+———------
backup    | postgres | UTF8
mydb      | ramesh   | UTF8
postgres  | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb:

 
Audit and History in PostgreSQL

Similar to the Linux ~/.bash_history file, postgreSQL stores all the SQL command that was executed in a history filed called ~/.psql_history as shown below

Create a PostgreSQL User

Method 1:
# CREATE USER username WITH password 'password';
Method 2:
$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Check version of PostgreSQL

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
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 quittest=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)