when to use = and := in postgreSQL?

Question

Im using plpgsql to write triggers n Im wondering when to use = and when to use := in postgreSQL, what is the difference???

for example:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
DECLARE
t_ix real;
n int;

BEGIN
IF NEW.time_type = 'Start' THEN
    SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix;
      GET DIAGNOSTICS n = ROW_COUNT;
        IF (n = 0) THEN 
        t_ix = 1;
        ELSE 
        t_ix = t_ix + 1;
        END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;

http://stackoverflow.com/questions/7462322/the-forgotten-assignment-operator-and-the-commonplace — Nick Barnes, Jun 20 '15 at 16:47

score 13 · Accepted Answer · answered Jun 22 '15 at 13:18

13

In version 9.4, the documentation was updated to make it clear that there is no difference.

Version 9.4:

40.5.1. Assignment

An assignment of a value to a PL/pgSQL variable is written as:

variable { := | = } expression;

[...]

Equal (=) can be used instead of PL/SQL-compliant :=

In previous versions, := alone was mentioned as the assignment operator, but = has been working since the beginning.

answered Jun 22 '15 at 13:18

Daniel Vérité

58,074
15
129
156

Can we use := for comparisons? – Tim3880 Jun 23 '15 at 03:54

score 5 · Answer 2 · edited Jun 20 '15 at 17:38

5

= is for comparison. := is for assignment.

edited Jun 20 '15 at 17:38

answered Jun 20 '15 at 16:31

Ed Heal

59,252
17
87
127

so the example above is incorrect, it should say... IF (n=0) THEN t_ix := 1; ELSE t_ix := t_ix + 1; END IF; ain't it???? – Natysiu16 Jun 20 '15 at 16:43
`=` also works for assignment. Which is a little confusing. Better to stick with `:=`. – Nick Barnes Jun 20 '15 at 16:50
2

@NickBarnes: `=` for assignment is deprecated and should not be used. – Jun 20 '15 at 17:38
1

@a_horse: That's what I thought, but [as of 9.4](https://github.com/postgres/postgres/commit/7e1955b861a1be9ef2dfd6acdd50d0c6b5a75794), it seems that it's officially supported. I agree that it should not be used, though. – Nick Barnes Jun 21 '15 at 01:10

when to use = and := in postgreSQL?

2 Answers2