Friday, October 19, 2012

PostgreSQL: check the core settings of server

SELECT name, context, unit, setting, short_desc

FROM pg_settings

WHERE name in ('listen_addresses' ,'max_connections' ,'shared_buffers' ,'effective_cache_size' , 'work_mem' , 'maintenance_work_mem')

ORDER BY context,name;


If context is set to postmaster, it means changing this parameter requires a restart of the postgresql service. If context is set to user, changes require at least a reload. Furthermore, these settings can be            overridden at the database, user, session, or function levels.

unit tells you the unit of measurement that the setting is reported in. This is very important for memory settings since, as you can see, some are reported in 8 kB and some in kB

Thursday, October 18, 2012

PosgreSQL: get main configuration files

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';


postgresql.conf controls general settings, such as how much memory to allocate, default storage location for new databases, which IPs PostgreSQL listens on, where logs are stored, and so forth.

pg_hba.conf controls security. It manages access to the server, dictating which users can login into which databases, which IPs or groups of IPs are permitted to connect and the authentication scheme expected.

pg_ident.conf is the mapping file that maps an authenticated OS login to a PostgreSQL user. This file is used less often, but allows you to map a server account to a PostgreSQL account. For example, people sometimes map the OS root account to the postgre’s super user account. Each authentication line in pg_hba.conf can use a different pg_ident.conf file

Wednesday, October 17, 2012

PostgreSQL quiz: stored procedure execution

Assume that you have a table contact and a stored procedure called insert_contact as the following screenshots



Which one below is correct?

A. CALL insert_contact(‘John’,'Tran’);

B. EXECUTE insert_contact(‘John’,’Tran’);

C. SELECT insert_contact(‘John’,’Tran’)

D. Both A & B

E. Error in the store procedure code

F. None of the above


Answer: [C]

Highlight to find out the answer.

Tuesday, October 16, 2012

PostgreSQL license

Select the most suitable statement about the PostgreSQL license from below.

A. PostgreSQL is distributed under the GPL license.
B. PostgreSQL is distributed under the PostgreSQL license.
C. PostgreSQL is distributed under the LGPL license.
D. PostgreSQL is distributed under the BSD license.
E. PostgreSQL is distributed under the X11(MIT) license.


Answer: [D]

Highlight to find out the answer.

Monday, October 15, 2012

Convert date time values to string in PostgreSQL

This is a view extracted from OpenERP.

CREATE OR REPLACE VIEW crm_claim_report
SELECT min( AS id
, to_char(, 'YYYY'::text) AS name
, to_char(, 'MM'::text) AS month
, to_char(, 'YYYY-MM-DD'::text) AS day
, to_char(c.date_closed, 'YYYY-MM-DD'::text) AS date_closed
, to_char(c.date_deadline::timestamp with time zone, 'YYYY-MM-DD'::text) AS date_deadline, c.state, c.user_id
, c.stage_id, c.section_id, c.partner_id, c.company_id, c.categ_id, count(*) AS nbr, c.priority
, c.type_action, date_trunc('day'::text, c.create_date) AS create_date
, avg(date_part('epoch'::text, c.date_closed - c.create_date)) / (3600 * 24)::double precision AS delay_close
, ( SELECT count( AS count FROM mailgate_message
WHERE mailgate_message.model::text = 'crm.claim'::text AND mailgate_message.res_id = AND mailgate_message.history = true) AS email
, ( SELECT avg(crm_case_stage.probability) AS avg FROM crm_case_stage
WHERE crm_case_stage.type::text = 'claim'::text AND = c.stage_id) AS probability
, date_part('epoch'::text, c.date_deadline::timestamp without time zone - c.date_closed) / (3600 * 24)::double precision AS delay_expected
FROM crm_claim c
GROUP BY to_char(, 'YYYY'::text), to_char(, 'MM'::text), to_char(, 'YYYY-MM-DD'::text), c.state, c.user_id, c.section_id
, c.stage_id, c.categ_id, c.partner_id, c.company_id, c.create_date, c.priority, c.type_action, c.date_deadline, c.date_closed,;

Sunday, October 14, 2012

PostgreSQL Contactenation

Which query is correct in PostgreSQL?

A. SELECT 'Test' . 'query';
B. SELECT cat('Test','query') FROM dual;
C. SELECT 'Test' + 'query';
D. SELECT 'Test' + 'query' FROM dual;
E. SELECT 'Test' || 'query';

Highlight to find out the answer.

Friday, October 12, 2012

Same query for Fiscal and Normal calendar



Assume that you have a mapping table between Fiscal and Normal calendar above.

Sometimes, you need to write one stored procedure with a param @IsFiscal to determine when to return order info on Fiscal year or Normal calendar year.

You can implement as the following


IF ISNULL(@IsFiscal,0) = 0
    SELECT @StartMonth = CAST(CAST(@Year AS VARCHAR) + '-01-01' AS SMALLDATETIME);
    SELECT @StartMonth = CAST(CAST([Year] AS VARCHAR) + '-' + CAST(NormalMonth AS VARCHAR) + '-01' AS SMALLDATETIME)
    FROM dbo.tblFiscalMapping
    WHERE FiscalYear = @Year;

SET @EndMonth = DATEADD(m, 11, @StartMonth);

SELECT OrderID, OrderNumber, OrderDate, CustomerID, TotalAmount

FROM dbo.tblOrder

WHERE DATEDIFF(m, @StartMonth, OrderDate) >= 0
                                AND DATEDIFF(m, @EndMonth, OrderDate) <= 0

Create a Virtual Active Directory Domain Controller with VirtualBox

I need to query Active Directory by using SQL Server, so I created virtual Active Directory Domain Controller to do that.

1. Install Windows Server virtual machine, such as Windows Server 2003

2. Install Client virtual machine, such as Windows XP

3. Set up network



4. Set up new Adapter for each machine, use the adapter set up on Step 3


5. Run Windows Server machine

6. Install Active Directory by following steps after running dcpromo


7. Set up IP Address on Windows Server, note that IP address must be in the range set up in Step 3


8. Set up IP Address on Client machine


9. Create a domain user for client machine in Domain Controller

To allow domain user as local admin, please visit this link

10. Register client machine in domain controller which is defined in Step 6, then input user name/pwd defined in Step 9


Tuesday, October 9, 2012

Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address

Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)


2. Install ODBC Driver for PostgreSQL



3. Create connection to PostgreSQL database with ODBC Driver





4. Now, it’s time to create linked server to PostgreSQL database:

You can create with GUI


Or can create with scripts

EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;


Finally, you can query PostgreSQL database inside SQL Server


5. Verify the linked server


Query Excel spreadsheet on SQL Server 2008 64 bit with Linked Server

For SQL Server 64 bit, if you use Microsoft.Jet.OLEDB.4.0, you may experience this issue:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix this, please follow the following:

1. Download and install Microsoft Access Database Engine 2010 Redistributable at this address 


2. After installing, create a linked server to your Excel file

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_LINKED_SERVER_NAME'
                    , @srvproduct=N'Excel'
                    , @provider=N'Microsoft.ACE.OLEDB.12.0'
                    , @datasrc=N'C:\Your_Excel_File.xls'
                    , @provstr=N'Excel 12.0';


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_LINKED_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;


3. Run OPENQUERY to get data from your Excel file


Monday, October 8, 2012

Get current queries executing on PostgreSQL

SELECT datname "Database Name"
 , usename "User Name"
 , procpid "Process ID"
 , client_addr "Client IP Address"
 , waiting "Is waiting"
 , query_start "Start Time"
 , current_query "Query Text"
FROM pg_stat_activity;

Get current queries executing on SQL Server

SELECT t1.session_id
 , t2.text
, t1.[status]
, t1.blocking_session_id
, t1.cpu_time
, t1.total_elapsed_time  
FROM sys.dm_exec_requests t1  
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS t2

Oracle Query