PostgreSQL: Replace multiple spaces with one

If you want to use a single query to replace multiple spaces with one on SQL Server, you can read the articles here http://www.sqlservercentral.com/articles/T-SQL/68378/
However, it's very simple in PostgreSQL by using Regular Expression
SELECT regexp_replace('This    sentence     contains             multiple                 spaces', '\s+', ' ', 'g');

PostgreSQL - Copy CSV to table with psql - part 2

CREATE TABLE tbl_test
(
 a text,
 b text,
 c text
)

Content of test.csv, and it put in /Database folder
a1,b1,c1
a2,b2,c2
a3,b3,c3

Instead of using COPY command directly, you can modify the test.cvs file to include it, as
COPY tbl_test FROM STDIN WITH CSV;
a1,b1,c1
a2,b2,c2
a3,b3,c3
\.

Note that you have to include a carriage return in the last line of the file (after '\.')
Then, run the following command to import

MyComputer$ psql -h localhost -p 5432 -U postgres testdb -f ~/Database/test.csv

PostgreSQL - Copy database

Sometimes, instead of back up and restore to a new db and wait your time if you only want to copy a database and put in the same server, you can use the following query:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Note that this query only successfully if no sessions existing in originaldb. So, you can check originaldb sessions and kill them with this function pg_terminate_backend(procpid)

PostgreSQL - Kill idle processes

Sometimes, you need to kill all unnecessary processes running on your database server. You can do that by using the following query:

SELECT           
   procpid,
   (
       SELECT pg_terminate_backend(procpid)
   ) AS killed
 FROM pg_stat_activity
 WHERE current_query LIKE '%idle%' AND datname = 'YourDBName';

PostgreSQL - Copy csv file to table with psql

CREATE TABLE tbl_test
(
 a text,
 b text,
 c text
)

Content of test.csv
a1,b1,c1
a2,b2,c2
a3,b3,c3

psql to COPY
psql -h localhost -U postgres testdb
testdb=# COPY tbl_test FROM 'D:\Temp\test.csv' WITH DELIMITER ',' CSV; 

PostgreSQL - VACUUM ANALYZE EXPLAIN NOTIFY

Select the correct statement that records the space occupied by deleted or updated rows for later reuse, and also updates statistics.

A. VACUUM
B. VACUUM ANALYZE
C. EXPLAIN
D. EXPLAIN ANALYZE
E. NOTIFY
F. None of the above

Answer: [B]
Highlight to find out the answer.

PostgreSQL - ANALYZE quiz

What does the following command do?
Choose the correct answer below.
Note: "psql=#" is the command prompt for psql.
psql=# ANALYZE xyz;

A. Collects statistical information related to the content of the database xyz.
B. Collects statistical information related to the content of the table xyz.
C. Outputs statistical information related to the content of the table xyz.
D. No ANALYZE command in PostgreSQL, error will occur.
E. None of the above
F. Both B & C

Answer: [B]
Highlight to find out the answer.

PostgreSQL – Convert rows to string

SELECT array_to_string(
    array(
        SELECT name
        FROM pg_settings
        WHERE setting = 'off' AND context = 'postmaster'
    ), ',');

SELECT setting, array_to_string(array_agg(name), ',')
FROM pg_settings
WHERE setting IN ('on', 'off')
GROUP BY setting;

PostgreSQL - Query data from another database/server

Assume that, you have two databases:
1. db1 with two schemas: public and sch1
2. db2 with two schemas: public and sch2

In PostgreSQL, you can access a table from a different schema by using SELECT * FROM schema_name.table_name such as SELECT * FROM db1_sch.employee;
However, you cannot access a table on the different database with explicit way like SELECT * FROM db2.sch2.employee;

The question is how can we access like SQL Server or some other DBMS.
Fortunately, PostgreSQL support dblink to do that.

First, install extension dblink on the database you want to be able to access to other
CREATE EXTENSION dblink;

If db2 is in the same server, you can query table in db2 like that:
SELECT *
FROM dblink('dbname=db2', 'SELECT employee_id, employee_name FROM sch2.employee')
           AS employee(employee_id integer, employee_name text);

If db2 is in the different server, you can query:
SELECT *
FROM dblink('hostaddr=172.x.x.x dbname=db2 user=admin password=pwd port=5432', 'SELECT employee_id, employee_name FROM sch2.employee')
           AS employee(employee_id integer, employee_name text);

PostgreSQL - get table structure

SELECT ordinal_position
, column_name
, CASE data_type WHEN 'character varying' THEN data_type || '('||character_maximum_length || ')' ELSE data_type END data_type
, column_default
, is_nullable
, character_maximum_length
, numeric_precision
FROM information_schema.columns
WHERE table_catalog = 'database_name'
AND table_schema = 'schema_name'
AND table_name = 'table_name'
ORDER BY ordinal_position;

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