Category Archives: Database

Export table structure

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

Advertisements

Remove \r \n from database data

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

When was the database created in Postgres

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

Query to delete records between two records

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    |
+—-+—————–+

Query to get a year from Varchar Data type

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 VS Postgres query

MySql                                              Postgres

show database                                  \l

show tables;                                       \d

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