Differencebetween '->>' and '->' in Postgres SQL

PostgreSQL calculate difference between rows

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:

http://www.postgresql.org/docs/current/static/tutorial-window.html

What is the difference between `-` and `-` in Postgres SQL?

-> returns json (or jsonb) and ->> returns text:

with t (jo, ja) as (values
    ('{"a":"b"}'::jsonb,('[1,2]')::jsonb)
)
select
    pg_typeof(jo -> 'a'), pg_typeof(jo ->> 'a'),
    pg_typeof(ja -> 1), pg_typeof(ja ->> 1)
from t
;
 pg_typeof | pg_typeof | pg_typeof | pg_typeof 
-----------+-----------+-----------+-----------
 jsonb     | text      | jsonb     | text

what is the difference between - and = when connecting to postgresql?

The question is not clear to me , may be I got it wrong .

=> Means , you are good to go for new command.

Where as

-> means the continuation of the active command.

You can check this ask ubuntu link: https://askubuntu.com/questions/806477/difference-between-postgres-and-postgres

Now there is another ->> and ->

The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.

You can check this link : https://stackoverflow.com/questions/38777535/what-is-the-difference-between-and-in-postgres-sql#:~:text=3%20Answers&text=PostgreSQL%20provides%20two%20native%20operators,JSON%20object%20field%20as%20text.

Feel free to reply or comment , if I understood the question wrong.

What is the difference between != and operators?

From the manual:

Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things.

So no, there is no difference between the two.

postgres Finding the difference between numbers of the same column

How about simply doing conditional aggregation?

select date,
       sum(case when key in ('a', 'b') then value 
                when key in ('c') then - value
           end) as daily_calc
from a
group by date;

A join doesn't seem very helpful for this calculation.

Get the difference between two values

Join the tables and select the difference:

select a.name, b.value - a.value, a.date 
from a inner join b on a.name = b.name and a.date = b.date

What's the difference between WHERE and FILTER (WHERE) in PostgreSQL?

The subqueries are not equivalent:

The first one

SELECT
  id, 
  max(treatment) AS ever_treated, 
  count(*) FILTER (WHERE outcome = 1) AS outcome_ct
FROM t
GROUP BY 1

1. Reads all the rows from t.
2. Computes the max treatment for every id in the whole table t.
3. Count the rows with outcome = 1 for every id.

The second one

select
  id, 
  max(treatment) as ever_treated, 
  count(*) as count 
from t
where outcome = 1 
group by id

1. Reads the subset of rows from t where outcome = 1.
2. Computes the max treatment for every id in the subset of the table t.
3. Count the rows for every id in the subset (already filtered).

In terms of the result only #2 and #3 are significant. As you can see #3 will be equivalent, but #2 won't be.

---

Related Topics