Use `\timing` as explained by [&quot;How can I time SQL-queries using psql?&quot;](https://dba.stackexchange.com/q/3148/7788).

See also [the manual for `psql`](http://www.postgresql.org/docs/current/static/app-psql.html).

If you want *server-side* execution times that don&#39;t include the time to transfer the result to the client, you can set `log_min_duration_statement = 0` in the configuration, then `SET client_min_messages = log` so you get the log info in the console.

You can also use `EXPLAIN ANALYZE` to get detailed execution timings. There&#39;s some timing overhead for this unless you use `EXPLAIN (ANALYZE TRUE, TIMING FALSE)`, which is only in newer versions, and disables detailed timing to give only an aggregate execution time instead.

[PgBadger](http://dalibo.github.io/pgbadger/), especially when combined with [the `auto_explain` module](http://www.postgresql.org/docs/current/static/auto-explain.html), can provide useful aggregate statistics from log analysis.

Finally, there&#39;s [`pg_stat_statements`](http://www.postgresql.org/docs/current/static/pgstatstatements.html), which can collect handy aggregate information on the running system.

I think that `EXPLAIN ANALYSE` might be helpful to you

Syntax:

    EXPLAIN ANALYSE query;

Example;

    EXPLAIN ANALYSE 
    SELECT  * 
    FROM    demotable;

Output:

    &quot;Seq Scan on demotable  (cost=0.00..12.10 rows=210 width=356) 
                            (actual time=0.020..0.021 rows=8 loops=1)&quot;
    &quot;Planning time: 18.477 ms&quot;
    &quot;Execution time: 0.042 ms&quot;





You can use `\timing` in `pgsql` as follows to have the execution time printed to standard output:

```sh
psql -d db -c &#39;\timing&#39; -c &#39;select 1&#39;
```

Taken from [this](https://dba.stackexchange.com/questions/3148/how-can-i-time-sql-queries-using-psql#comment356069_3149) comment.

I&#39;ve been searching for 4 hours now, and have not found a solution to get the difference between two dates in years, months, and days in JavaScript, like: 10th of April 2010 was 3 years, x month and y days ago.

There are lots of solutions, but they only offer the difference in the format of either days OR months OR years, or they are not correct (meaning not taking care of actual number of days in a month or leap years, etc). Is it really that difficult to do that? 


I&#39;ve had a look at: 

 - http://momentjs.com/ -&gt; can only output the difference in either years, months, OR days
 - http://www.javascriptkit.com/javatutors/datedifference.shtml
 - http://www.javascriptkit.com/jsref/date.shtml
 - http://timeago.yarp.com/
 - www.stackoverflow.com -&gt; Search function

In php it is easy, but unfortunately I can only use client-side script on that project. Any library or framework that can do it would be fine, too.

Here are a list of expected outputs for date differences:

    //Expected output should be: &quot;1 year, 5 months&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2015-10-10&#39;));
    
    //Expected output should be: &quot;1 year, 4 months, 29 days&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2015-10-09&#39;));
    
    //Expected output should be: &quot;1 year, 3 months, 30 days&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2015-09-09&#39;));
    
    //Expected output should be: &quot;9 months, 27 days&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2015-03-09&#39;));
    
    //Expected output should be: &quot;1 year, 9 months, 28 days&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2016-03-09&#39;));
    
    //Expected output should be: &quot;1 year, 10 months, 1 days&quot;.
    diffDate(new Date(&#39;2014-05-10&#39;), new Date(&#39;2016-03-11&#39;));

Difference between two dates in years, months, days in JavaScript

I am getting **Database is locked** exception from **SQLite** for some queries only.  

Below is my code:
When I execute any select statement it works fine.  
When I am executing any write statement on `Jobs` Table it also works fine.  

This works fine:  

    ExecuteNonQuery(&quot;DELETE FROM Jobs WHERE id=1&quot;);

 
But the same way if I am executing queries for `Employees` table it is throwing an exception that **database is locked**.  
This throws Exception:
  
    ExecuteNonQuery(&quot;DELETE FROM Employees WHERE id=1&quot;);
    
Below are my functions:

    public bool OpenConnection()
    {
        if (Con == null)
        {
            Con = new SQLiteConnection(ConnectionString);
        }
        if (Con.State == ConnectionState.Closed)
        {
            Con.Open();
            //Cmd = new SQLiteCommand(&quot;PRAGMA FOREIGN_KEYS=ON&quot;, Con);
            //Cmd.ExecuteNonQuery();
            //Cmd.Dispose();
            //Cmd=null;
            return true;
        }
        if (IsConnectionBusy())
        {
            Msg.Log(new Exception(&quot;Connection busy&quot;));
        }
        return false;
    }

    public Boolean CloseConnection()
    {
        if (Con != null &amp;&amp; Con.State == ConnectionState.Open)
        {
            if (Cmd != null) Cmd.Dispose();
            Cmd = null;
            Con.Close();
            return true;
        }
        
        return false;
    }

    public Boolean ExecuteNonQuery(string sql)
    {
        if (sql == null) return false;
        try
        {
            if (!OpenConnection())
                return false;
            else
            {
                //Tx = Con.BeginTransaction(IsolationLevel.ReadCommitted);
                Cmd = new SQLiteCommand(sql, Con);
                Cmd.ExecuteNonQuery();
                //Tx.Commit();
                return true;
            }
        }
        catch (Exception exception)
        {
            //Tx.Rollback();
            Msg.Log(exception);
            return false;
        }
        finally
        {
            CloseConnection();
        }
    }

This is the Exception: 
At line 103 : `Cmd.ExecuteNonQuery();`



&gt;Exception Found:
      Type: System.Data.SQLite.SQLiteException
     Message: database is locked
    database is locked
     Source: System.Data.SQLite
    
&gt;Stacktrace:    at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at TimeSheet6.DbOp.ExecuteNonQuery(String sql) in d:\Projects\C# Applications\Completed Projects\TimeSheet6\TimeSheet6\DbOp.cs:line 103


SQLite Database Locked exception

In the `MySQL` command line interface, when you execute a query it will tell you how long the query took to execute after printing out the results.

In the Postgres command line interface (`psql`) it does not tell you. I know how to configure the logging so I can get the information from logs, but it would be more convenient to have it print to standard output like it does in `MySQL`.

Can this be done?

Postgres Query execution time

In the <code>MySQL</code> command line interface, when you execute a query it will tell you how long the query took to execute after printing out the results.
In the Postgres command line interface (<code>psql</code>) it does not tell you. I know how to configure the logging so I can get the information from logs, but it would be more convenient to have it print to standard output like it does in <code>MySQL</code>.
Can this be done?

I&#39;d like to see which queries are being executed on a live Django application, and how much memory they are taking up. I have read that `pg_stat_activity` can be useful to monitor a Postgres database. 

I have [looked at the Postgres documentation][1], but I have a very simple question that doesn&#39;t seem to be answered there. 

How do I actually get started with `pg_stat_activity`? What do I type to use it, and where do I type it?


  [1]: http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

How to use pg_stat_activity?

I want to copy a CSV file to a Postgres table. There are about 100 columns in this table, so I do not want to rewrite them if I don&#39;t have to.

I am using the `\copy table from &#39;table.csv&#39; delimiter &#39;,&#39; csv;` command but without a table created I get `ERROR: relation &quot;table&quot; does not exist`. If I add a blank table I get no error, but nothing happens. I tried this command two or three times and there was no output or messages, but the table was not updated when I checked it through PGAdmin. 

Is there a way to import a table with headers included like I am trying to do?

How to copy from CSV file to PostgreSQL table with headers in CSV file?

I&#39;d like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method of avoiding the division by zero and null values. 

    (CASE(COALESCE(COUNT(column_name),1)) WHEN 0 THEN 1
    ELSE (COALESCE(COUNT(column_name),1)) END) 

I wonder if there is a better way of doing this?

Avoid division by zero in PostgreSQL

Postgres 9.1 database contains tables yksus1 .. ykssu9 in public schema. pgAdmin shows those definitions as in code below.
How to move  those tables to firma1 schema ?

Other tables in firma1 schema have foreign key references to those table primay keys. Foreign key references to those tables are only from tables in firma1 schema.

Some of those tables contain data.
If tables is moved to firma1 schema, foreign key references shouuld also be updated to firma1.yksusn tables.
Table structures cannot changed.

It looks like primary key sequences are already in firma1 schema so those should not moved.
Version string	PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

    CREATE TABLE yksus1
    (
      yksus character(10) NOT NULL DEFAULT ((nextval(&#39;firma1.yksus1_yksus_seq&#39;::regclass))::text || &#39;_&#39;::text),
      veebis ebool,
      nimetus character(70),
      &quot;timestamp&quot; character(14) DEFAULT to_char(now(), &#39;YYYYMMDDHH24MISS&#39;::text),
      username character(10) DEFAULT &quot;current_user&quot;(),
      klient character(40),
      superinden character(20),
      telefon character(10),
      aadress character(50),
      tlnr character(15),
      rus character(60),
      CONSTRAINT yksus1_pkey PRIMARY KEY (yksus)
    );
    ALTER TABLE yksus1
      OWNER TO mydb_owner;
        
    CREATE TRIGGER yksus1_trig
      BEFORE INSERT OR UPDATE OR DELETE
      ON yksus1
      FOR EACH STATEMENT
      EXECUTE PROCEDURE setlastchange();


other tables are similar:


    CREATE TABLE yksus2
    (
      yksus character(10) NOT NULL DEFAULT ((nextval(&#39;firma1.yksus2_yksus_seq&#39;::regclass))::text || &#39;_&#39;::text),
      nimetus character(70),
      &quot;timestamp&quot; character(14) DEFAULT to_char(now(), &#39;YYYYMMDDHH24MISS&#39;::text),
      osakond character(10),
      username character(10) DEFAULT &quot;current_user&quot;(),
      klient character(40),
      superinden character(20),
      telefon character(10),
      aadress character(50),
      tlnr character(15),
      rus character(60),
      CONSTRAINT yksus2_pkey PRIMARY KEY (yksus),
      CONSTRAINT yksus2_osakond_fkey FOREIGN KEY (osakond)
          REFERENCES yksus2 (yksus) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
    );
    ALTER TABLE yksus2
      OWNER TO mydb_owner;
    
    CREATE TRIGGER yksus2_trig
      BEFORE INSERT OR UPDATE OR DELETE
      ON yksus2
      FOR EACH STATEMENT
      EXECUTE PROCEDURE setlastchange();

how to move tables from public to other schema in Postgres

I ve installed Postgresql and then ran a bunch of rails apps on my local Mac OSX Mountain Lion and created databases etc. Today after a while when I launched pgAdminIII and tried to launch a database server I got this error:

![enter image description here][1]


A quick google showed [this][2] post. More browsing pointed to the fact that there might be some sort of postmaster.pid file lying around that might be the root cause of this. If I delete that things would be fine.

However, before I go deleting stuff on my computer I wanted to make sure Im debugging this in a systematic way which would not result in more problems.

Somewhere I read that before deleting that file, I need to run this command:

      ps auxw | grep post

If I get no results then, its OK to delete the file. Else not. Well, I got this result of that command:

      AM               476   0.0  0.0  2423356    184 s000  R+    9:28pm   0:00.00 grep post



So now of course Im throughly confused. 

So what should I do?

Here is part of my postgres server error log:

     FATAL:  lock file &quot;postmaster.pid&quot; already exists
     HINT:  Is another postmaster (PID 171) running in data directory &quot;/usr/local/var/postgres&quot;?


Postgresql is still not running, still get the same error and nothing has changed. Im too chicken to delete things without checking on SO.

Could some of you experts please guide a noob.




Thanks


  [1]: http://i.stack.imgur.com/hOsJd.png
  [2]: https://stackoverflow.com/questions/8465508/can-not-connect-to-local-postgresql

Content Type	Original Author	Original Content on Stackoverflow
Question	NickJ	View Question on Stackoverflow
Solution 1 - Postgresql	Craig Ringer	View Answer on Stackoverflow
Solution 2 - Postgresql	Vinoth Shankar	View Answer on Stackoverflow
Solution 3 - Postgresql	andschar	View Answer on Stackoverflow

Postgres Query execution time

Postgresql Problem Overview

Postgresql Solutions

Solution 1 - Postgresql

Solution 2 - Postgresql

Solution 3 - Postgresql

SQLite Database Locked exception

Difference between two dates in years, months, days in JavaScript

Attributions