This is what window functions are for:

    select year, 
           month,
           fixes,
           fixes - lag(fixes) over (order by year, month) as increase,
    from the_table;

For more details please see the manual: &lt;br/&gt;
http://www.postgresql.org/docs/current/static/tutorial-window.html


I&#39;m trying out developing for Swift, it&#39;s going pretty well. One of the issues I&#39;m having is finding out how to stylize fonts programmatically in the language. 

For example in this label I wrote the code below for, how can I make it Helvetica Neue Ultralight for example?

    label.font = UIFont (name: &quot;Helvetica Neue&quot;, size: 30)

How do you stylize a font in Swift?

Consider:

    $(&#39;.b&#39;).click(function(){
        var link = &#39;www.yahoo.com&#39;;
        window.location.href(link);
    });

I expect it to open www.yahoo.com, but it says &quot;string is not a function&quot;. Why?

jsFiddle: http://jsfiddle.net/V9Xat/


String is not a function on window location href

I tried to calculate difference between rows in a field using a query:

&lt;pre&gt;
Illustrations:
input:year,month,fixes
output:increase

     year | month | fixes    | increase
    ------+-------+----------+-----------
     2006 | 04    |       1  | 0
     2006 | 05    |       4  | 3
     2006 | 06    |       3  | -1
&lt;/pre&gt;

Increase column as output by difference between adjacent rows in fixes.

PostgreSQL calculate difference between rows

I tried to calculate difference between rows in a field using a query:
<pre>Illustrations:
input:year,month,fixes
output:increase
<pre><code class="hljs language-sql"> year | month | fixes | increase
------+-------+----------+-----------
 2006 | 04 | 1 | 0
 2006 | 05 | 4 | 3
 2006 | 06 | 3 | -1
</code></pre>
</pre>
Increase column as output by difference between adjacent rows in fixes.

Am very new in *Database development* so I have some doubts regarding my following example:

Function f1() - ***language sql***

     create or replace function f1(istr  varchar)
     returns text as $$ 
     select &#39;hello! &#39;::varchar || istr;
     $$ language sql;

Function f2() - ***language plpgsql***

     create  or replace function f2(istr  varchar)
     returns text as $$ 
     begin select &#39;hello! &#39;::varchar || istr; end;
     $$ language plpgsql;

 - Both *functions* can be called like `select f1(&#39;world&#39;)` or `select f2(&#39;world&#39;)`.

 - If I call `select f1(&#39;world&#39;)` the *output* will be:

        `hello! world`

 - And *output* for `select f2(&#39;world&#39;)`:

    &gt;  ERROR:  query has no destination for result data
    &gt;        HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
    &gt;        CONTEXT:  PL/pgSQL function f11(character varying) line 2 at SQL statement
    &gt;        ********** Error **********

 - I wish to know the difference and in which situations I should use `language sql` or `language plpgsql`.

Any useful link or answers regarding functions will much appreciated.

Difference between language sql and language plpgsql in PostgreSQL functions

MySQL provides 2 ways to check truth value of `boolean` columns, those are `column_variable = true` and `column_variable is true`. I created a table, inserted few values &amp; tried a few `select` statements. Here are the results:

First I created this table:

    mysql&gt; create table bool_test (
        -&gt; id int unsigned not null auto_increment primary key,
        -&gt; flag boolean );
    Query OK, 0 rows affected (0.13 sec)

Then I inserted 4 rows:

    mysql&gt; insert into bool_test(flag) values (true),(false),(9),(null);

    mysql&gt; select * from bool_test;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    |  2 |    0 |
    |  3 |    9 |
    |  4 | NULL |

Here are all the `select` queries I fired on this table:

    mysql&gt; select * from bool_test where flag;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    |  3 |    9 |
    +----+------+
    2 rows in set (0.49 sec)

    mysql&gt; select * from bool_test where flag = true;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    +----+------+
    1 row in set (0.02 sec)

    mysql&gt; select * from bool_test where flag is true;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    |  3 |    9 |
    +----+------+
    2 rows in set (0.04 sec)

    mysql&gt; select * from bool_test where flag = false;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    +----+------+
    1 row in set (0.01 sec)

    mysql&gt; select * from bool_test where flag is false;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    +----+------+
    1 row in set (0.00 sec)

    mysql&gt; select * from bool_test where !flag;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    +----+------+
    1 row in set (0.00 sec)

    mysql&gt; select * from bool_test where not flag;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    +----+------+
    1 row in set (0.00 sec)

    mysql&gt; select * from bool_test where flag != true;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    |  3 |    9 |
    +----+------+
    2 rows in set (0.00 sec)

    mysql&gt; select * from bool_test where flag is not true;
    +----+------+
    | id | flag |
    +----+------+
    |  2 |    0 |
    |  4 | NULL |
    +----+------+
    2 rows in set (0.00 sec)

    mysql&gt; select * from bool_test where flag != false;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    |  3 |    9 |
    +----+------+
    2 rows in set (0.04 sec)

    mysql&gt; select * from bool_test where flag is not false;
    +----+------+
    | id | flag |
    +----+------+
    |  1 |    1 |
    |  3 |    9 |
    |  4 | NULL |
    +----+------+
    3 rows in set (0.00 sec)


My Question is: when is it advisable to use `is`/`is not` and when is it advisable to use `=`/`!=`  with `true`/`false` ? Which one is vendor independent?

MySQL: &quot;= true&quot; vs &quot;is true&quot; on BOOLEAN. When is it advisable to use which one? And Which one is vendor independent?

I&#39;d like to get value by the following SQL using Eloquent ORM.
 
**- SQL**

     SELECT COUNT(*) FROM 
     (SELECT * FROM abc GROUP BY col1) AS a;

Then I considered the following.

**- Code**

     $sql = Abc::from(&#39;abc AS a&#39;)-&gt;groupBy(&#39;col1&#39;)-&gt;toSql();
     $num = Abc::from(\DB::raw($sql))-&gt;count();
     print $num;



I&#39;m looking for a better solution.

Please tell me simplest solution.



How to select from subquery using Laravel Query Builder?

I&#39;m trying the following query:

    SELECT (json_data-&gt;&#39;position&#39;-&gt;&#39;lat&#39;) + 1.0 AS lat FROM updates LIMIT 5;

(The +1.0 is just there to force conversion to float.  My actual queries are far more complex, this query is just a test case for the problem.)

I get the error:

    ERROR:  operator does not exist: jsonb + numeric

If I add in explicit casting:

    SELECT (json_data-&gt;&#39;position&#39;-&gt;&#39;lat&#39;)::float + 1.0 AS lat FROM updates LIMIT 5;

the error becomes:

    ERROR:  operator does not exist: jsonb + double precesion

I understand that most jsonb values cannot be cast into floats, but in this case I know that the lats are all JSON numbers.

Is there a function which casts jsonb values to floats (or return NULLs for the uncastable)?






How to convert PostgreSQL 9.4&#39;s jsonb type to float

I have a table:


    CREATE TABLE tblproducts
    (
    productid integer,
    product character varying(20)
    )

With the rows:

    INSERT INTO tblproducts(productid, product) VALUES (1, &#39;CANDID POWDER 50 GM&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (2, &#39;SINAREST P SYP 100 ML&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (3, &#39;ESOZ D 20 MG CAP&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (4, &#39;HHDERM CREAM 10 GM&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (5, &#39;CREAM 15 GM&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (6, &#39;KZ LOTION 50 ML&#39;);
    INSERT INTO tblproducts(productid, product) VALUES (7, &#39;BUDECORT 200 Rotocap&#39;);

If I execute `string_agg()` on `tblproducts`:

    SELECT string_agg(product, &#39; | &#39;) FROM &quot;tblproducts&quot;

It will return the following result:

    CANDID POWDER 50 GM | ESOZ D 20 MG CAP | HHDERM CREAM 10 GM | CREAM 15 GM | KZ LOTION 50 ML | BUDECORT 200 Rotocap

How can I sort the aggregated string, in the order I would get using `ORDER BY product`?

I&#39;m using PostgreSQL 9.2.4.

How to sort the result from string_agg()

I&#39;m trying to backup/restore a PostgreSQL database as is explained on the Docker website, but the data is not restored.

The volumes used by the database image are:

    VOLUME  [&quot;/etc/postgresql&quot;, &quot;/var/log/postgresql&quot;, &quot;/var/lib/postgresql&quot;]

and the CMD is:

    CMD [&quot;/usr/lib/postgresql/9.3/bin/postgres&quot;, &quot;-D&quot;, &quot;/var/lib/postgresql/9.3/main&quot;, &quot;-c&quot;, &quot;config_file=/etc/postgresql/9.3/main/postgresql.conf&quot;]

I create the DB container with this command:

    docker run -it --name &quot;$DB_CONTAINER_NAME&quot; -d &quot;$DB_IMAGE_NAME&quot;

Then I connect another container to insert some data manually:

    docker run -it --rm --link &quot;$DB_CONTAINER_NAME&quot;:db &quot;$DB_IMAGE_NAME&quot; sh -c &#39;exec bash&#39;
    psql -d test -h $DB_PORT_5432_TCP_ADDR
    # insert some data in the db
    &lt;CTRL-D&gt;
    &lt;CTRL-D&gt;

The tar archive is then created:

    $ sudo docker run --volumes-from &quot;$DB_CONTAINER_NAME&quot; --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql

Now I remove the container used for the db and create another one, with the same name, and try to restore the data inserted before:

    $ sudo docker run --volumes-from &quot;$DB_CONTAINER_NAME&quot; --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar 

But the tables are empty, why is the data not properly restored ?

Backup/Restore a dockerized PostgreSQL database

I installed Postgres with this command

    sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev

Using `psql --version` on terminal I get `psql (PostgreSQL) 9.3.4`

then I installed ``pgadmin`` with

    sudo apt-get install pgadmin3

Later I opened the UI and create the server with this information

![enter image description here][1]


but this error appear


![enter image description here][2]

how can I fix it?

  [1]: http://i.stack.imgur.com/89CrA.png
  [2]: http://i.stack.imgur.com/ClRbk.png

Content Type	Original Author	Original Content on Stackoverflow
Question	user2177232	View Question on Stackoverflow
Solution 1 - Sql	a_horse_with_no_name	View Answer on Stackoverflow

PostgreSQL calculate difference between rows

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

String is not a function on window location href

How do you stylize a font in Swift?

Attributions