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 - 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

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

Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address http://www.postgresql.org/ftp/odbc/versions/msi/

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';

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

GO

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 http://www.microsoft.com/en-us/download/details.aspx?id=13255 

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';

GO

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

GO

3. Run OPENQUERY to get data from your Excel file

SELECT * FROM OPENQUERY(EXCEL_LINKED_SERVER_NAME,'SELECT * FROM [Sheet1$]')

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

How To Create A Postgres Database Using pgAdmin on MAC

Inspecting your postgreSQL connection information

If you want to confirm you've connected to the right place and in the right way, you can execute some or all of the following commands:

SELECT inet_server_port();  
SELECT current_database();  
SELECT current_user;  
SELECT inet_server_addr();  
SELECT version();

Backup/Restore postgresSQL database with pgAdmin

BACKUP

1. Choose database need to backup

2. Save to backup file, choose COMPRESS as default

The GUI actually calls pg_dump command to do the backup

Choose Done to complete. You can see the backup file in selected folder

The backup as COMPRESS, so it cannot be read with text editor

If you want the backup is readable, you can choose backup type is PLAIN

 

RESTORE

1. Create an empty database

2. Choose the created database, and choose Restore

3. Select the backup file

The GUI actually calls pg_restore command to do the restore

4. Click OK to get the task execute. Database is restored to a new one.

Psql Help in PostgreSQL

Unlock an account

From your command prompt, type
SQLPlus "/ as SYSDBA"

SQL> ALTER USER scott ACCOUNT UNLOCK;
Make sure to execute the following command
SQL> COMMIT;

SQL Quiz

You discovered that the schema changes that were recently made to your SQL Server database have caused your applications (website, mobile, or desktop apps) to stop functioning. It is unclear who made the changes.  You need to implement a mechanism that will track schema changes in your database. What should you do?

A. Implement a stored procedure that writes data about schema changes to a log table.
B. Implement DDL AFTER triggers that write user and schema information to a log table.
C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.
D. Implement a DML AFTER trigger that writes data about schema changes to a log table.
E. Both A and C
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Add new column as NOT NULL to existing table

You are modifying a table named tblEmployee in a SQL Server database. You want to add a new column named JobTitle to the tblEmployee table. The table currently contains data. The HR Department has not yet created a job title for each employee. JobTitle is a required value for each employee. You want to add this new column by using the least amount of effort. What should you do?

A. Define the new column as NULL. Update the JobTitle column to the same value as 'Undefined'. Modify the JobTitle column to be NOT NULL.
B. Define the new column as NOT NULL with a default value of 'Undefined.'
C. Define the new column as NULL. Use application logic to enforce the data constraint.
D. Define the new column as NULL with a default value of 'Undefined.'
E. Both A and B
F. None of the above

Answer: [B]
Highlight to find out the answer.
Visit here to practice your SQL Server skills.

Create View Quiz

You are creating a view to join the tblEmployee and tblDepartment tables in a SQL Server database. You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to accomplish this goal by using the least possible amount of overhead. What should you do?

A. Create CHECK constraints on the tables.
B. Create a DDL trigger to roll back any changes to the tables if the changes affect the
columns in the view.
C. Create the view, specifying the WITH SCHEMABINDING option.
D. Create the view, specifying the WITH CHECK option.
E. Both C and D
F. None of the above

Answer: [C]
Highlight to find out the answer.
Come here to know more about CREATE VIEW
Visit here to practice your SQL Server skills.