How to determine the size of a database on disk in PostgreSQL

How to determine the size of a database on disk
SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize;
How to determine the size of a database table on disk

NOTE: There are two functions in PostgreSQL - pg_relation_size and pg_total_relation_size. The pg_relation_size just measures the size of the actual table where as the pg_total_relation_size includes both the table and all its toasted tables and indexes.
SELECT pg_size_pretty(pg_total_relation_size('someschema.sometable')) As fulltblsize, pg_size_pretty(pg_relation_size('someschema.sometable')) As justthetblsize;
How to determine the size of a database schema


When you are doing a lot of data loading for later massaging to dump into your production tables, you will often generate junk tables in the process and worse yet other people may generate junk tables period. As a general practice, we like to create a schema just for junk. The reason for that is that it is extremely easy to exclude schemas from being backed up and to load individual schemas. So with this approach we create or move all tables we are not quite sure if they are needed or we only need temporarily into a schema called junk, scratch, possiblejunk or whatever you want to call the schema and exclude it from our backups.

Something of the form:

CREATE SCHEMA scratch;
ALTER TABLE someschema.joeyplaying SET SCHEMA scratch;

After saving the world from junk, you would like to know how much space you have saved your backups from backing up. So you would employ a query something of the form:


SELECT pg_size_pretty(pg_database_size('mycurrentdb')) As fullprod,
pg_size_pretty(CAST(pg_database_size('mycurrentdb') - (SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables WHERE table_schema = 'scratch') As bigint)) As tobebackedup_size,
pg_size_pretty(CAST((SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables
WHERE table_schema = 'scratch') As bigint) ) As junk_size;

Size of Geometries in PostGIS tables

PostGIS has a companion function for measuring geometry size which is useful when you want to get a sense of how much space your geometries are taking up on disk for a set of records.


SELECT ST_Mem_Size(ST_GeomFromText('LINESTRING(220268 150415,220227 150505,220227 150406)'));

SELECT pg_size_pretty(CAST(SUM(ST_Mem_Size(the_geom)) As bigint) ) as totgeomsum
FROM sometable WHERE state = 'MA';

Create a template_postgis database

#Create a template_postgis database Some might find this useful for
#creating PostGIS databases without having to be PostgreSQL super users.
#The idea is to create a template_postgis database, install plpgsql and postgis into it,
#and then use this database as a template when creating new PostGIS databases.

# Creating the template spatial database
$ createdb -E UTF8 -T template0 template_postgis
# and add PLPGSQL language support.
$ createlang -d template_postgis plpgsql



# Loading the PostGIS SQL routines.
$ psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-1.5/postgis.sql
$ psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-1.5/spatial_ref_sys.sql

# Enabling users to alter spatial tables.
$ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

# Garbage-collect and freeze.
$ psql -d template_postgis -c "VACUUM FULL;"
$ psql -d template_postgis -c "VACUUM FREEZE;"

# Allows non-superusers the ability to create from this template.
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
$ psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_postgis';"

How to do dynamic query on null column with mySQL and php

How to do dynamic query on null column with mySQL and php

If you have to query null able column dynamically you have to use the like keyword

“select  emp_name from emp where emp_name like ‘%$emp_name%’;

How every this works fine if the emp_name column is not null able. But this will return no rows if the emp_name has null values in it. So we have to tune the query more we have to put one more condition.

“select  emp_name from emp where (emp_name like ‘%$emp_name%’ or emp_name is null);”

The above query works fine but will return every row from the table because it is always true.  So we have to use the third option which is using  ?: operator.

So we build the query this way.

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");

So as you can see if the user supply null in the emp_name field the query will be

select  emp_name from emp where 1=1

and this will return every thing in the table

and if the user supply value for emp_name the result will be according the query matching the emp_name.

you may ask what is the difference between this

1- “select  emp_name from emp where emp_name like ‘%%’;

2- “select  emp_name from emp where emp_name like ‘%smith%’;

The first query will not work if the column has null value and the second query will return every match to *smith*.

So I use  this and it is working fine with my dynamic selection form

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");

How to change varchar field to decimal in mysql

To alter column in mySQL from varchar to DECIMAL I used this syntax.

Alter table my_tbl  modify my_col decimal(6,2);

MySQL uses the Round routines of the host.


On a Windows box

16.125 = 16.13

16.135 = 16.14



On a Linux/Unix box

16.125 = 16.12

16.135 = 16.14

Oracle/PLSQL: Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?




Answer: To sort your field numerically, there are two possible solutions:

Solution #1



This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your "select" portion of the SQL.



For example,

You may have a supplier table defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.



select * from supplier

order by supplier_id;



However, you could execute the following SQL, which will return a numerically sorted supplier_id:



select * from supplier

order by to_number(supplier_id);



This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)



We'd like to thank Kamil for suggesting this solution.



This solution will work even if the varchar2 field contains non-numeric values.



Again, we'll demonstrate this solution on the supplier table, defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.



For example,



select * from supplier

order by lpad(supplier_id, 10);



This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.



Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function.

How to DROP an Oracle Database

You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed

However this is not a good way to drop an Oracle database. you should use dbca utility to drop the database. or for Oracle 10g and higher you can use drop database command

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT MOUNT;
DROP DATABASE;