Category: Database


Export table structure

pg_dump -U username -d db_name -t table_name –exclude-table-data table_name > table_structure.sql

Backuping a single table from a database

pg_dump -U username -d database1 -t table_name > /home/backups/table_name.sql

Restoring the table into another database

psql -U username -d database2 < /home/backups/table_name.sql

test_db=# select address from test_table;

58/171-G, RAJAPURAM,\r                                                                                                                                                                                                                                                                                                                                                                                                              +
PALANIAPPA NAGAR,\r                                                                                                                                                                                                                                                                                                                                                                                                                 +
SALEM
TS.No.5823,SANTHANATHAPURAM FOURTH STREET\r                                                                                                                                                                                                                                                                                                                                                                                         +

 

solution
test_db=# update test_table set address= regexp_replace(address, E'[\\n\\r]+’, ‘ ‘, ‘g’);

test_db=# select address from test_table;

58/171-G, RAJAPURAM,

PALANIAPPA NAGAR,

SALEM TS.No.5823,SANTHANATHAPURAM FOURTH STREET

Grant Privileges Postgres

Database:

GRANT ALL PRIVILEGES ON DATABASE DATABASENAME to USER;

Table:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO USER;

Id Seq Tables:

GRANT ALL PRIVILEGES  ON ALL SEQUENCES IN SCHEMA public TO USER;

postgres=#SELECT datname, (pg_stat_file(‘base/’||oid||’/PG_VERSION’)).modification AS datcreated
FROM pg_database;

datname | datcreated
——————-+—————————
template1 | 2014-05-06 15:44:37+05:30
template0 | 2014-05-06 15:44:38+05:30
postgres | 2014-05-06 15:44:38+05:30
abc | 2014-07-01 12:58:31+05:30
xyz | 2014-07-03 11:01:38+05:30

Example:

mysql> select id,name from student_detail limit 20;
+—-+—————–+
| id | name            |
+—-+—————–+
|  1 | SIVASANKARAN.S. |
|  2 | SELVAM P.       |
|  3 | MANIKANDAN  A   |
|  4 | KIRUTHIKA K     |
|  5 | PERIYANNAN K    |
|  6 | ANBU CHOZHAN    |
|  7 | DIVYA R         |
|  8 | ANJALAI M       |
|  9 | VASANTHI S      |
| 10 | BHUVANESHWARI M |
| 11 | SATHYA S        |
| 12 | KANAGA M        |
| 13 | JHON PALRAJ.A   |
| 14 | AMSAVALLI K     |
| 15 | RENGANATHAN M   |
| 16 | ANTHONI MANOJ.F |
| 17 | MANJU S         |
| 18 | VIJAYAKUMAR     |
| 19 | SELVAM S        |
| 20 | MARIYAPPAN P    |
+—-+—————–+
If you want to delete the records from 11 to 15 use the following query

mysql> delete from student_detail where id between 11 and 15;

mysql> select id,name from student_detail limit 20;
+—-+—————–+
| id | name            |
+—-+—————–+
|  1 | SIVASANKARAN.S. |
|  2 | SELVAM P.       |
|  3 | MANIKANDAN  A   |
|  4 | KIRUTHIKA K     |
|  5 | PERIYANNAN K    |
|  6 | ANBU CHOZHAN    |
|  7 | DIVYA R         |
|  8 | ANJALAI M       |
|  9 | VASANTHI S      |
| 10 | BHUVANESHWARI M |
| 16 | ANTHONI MANOJ.F |
| 17 | MANJU S         |
| 18 | VIJAYAKUMAR     |
| 19 | SELVAM S        |
| 20 | MARIYAPPAN P    |
+—-+—————–+

If a field contains date values with varchar data type, the following is the query to get year from that field

mysql> select identification_date from table1;

+———————+
| identification_date |
+———————+
| 11/05/2013          |
| 12/04/2012          |
| 02/06/2011          |
| 11/04/2012          |
| 11/04/2010          |
+———————+
5 rows in set (0.00 sec)

mysql > select YEAR(STR_TO_DATE(identification_date,’%d/%m/%Y’)) from table1;

+—————————————————+
| YEAR(STR_TO_DATE(identification_date,’%d/%m/%Y’)) |
+—————————————————+
|                                              2013 |
|                                              2012 |
|                                              2011 |
|                                              2012 |
|                                              2010 |
+—————————————————+
5 rows in set (0.03 sec)

MySql                                              Postgres

show database                                  \l

show tables;                                       \d

desc table table_name;                    \d+  table_name    or   \d  table_name

If you want to search column name as last_modified you will give the following query in postgres

select table_name from information_schema.columns where column_name = 
'last_modified

Backuping a single table from a database

mysqldump -u -p database1 table_name > /home/backups/table_name.sql

Restoring the table into another database

mysql -u -p database2 < /home/backups/table_name.sql