There is a column named "ap" in table "session", but it cannot be referenced from this part of the query

Question

I've got a table "session":

--------------------+-----------------------------+------------------------------------------------------+-----------+---------------------+----------
 id                 | integer                     | NOT NULL DEFAULT nextval('session_id_seq'::regclass) | plain     |                     |
 ssid               | integer                     |                                                      | plain     |                     |
 ap                 | integer                     |                                                      | plain     |                     |
 associationtime    | timestamp without time zone |                                                      | plain     |                     |
 disassociationtime | timestamp without time zone |                                                      | plain     |                     |
 sessionduration    | character varying(100)      |                                                      | extended  |                     |
 clientip           | character varying(100)      |                                                      | extended  |                     |
 clientmac          | character varying(100)      |                                                      | extended  |                     |
 devicename         | character varying(100)      |                                                      | extended  |                     |
 tx                 | character varying(100)      |                                                      | extended  |                     |
 rx                 | character varying(100)      |                                                      | extended  |                     |
 protocol           | character varying(100)      |                                                      | extended  |                     |
 snr                | character varying(100)      |                                                      | extended  |                     |
 rssi               | character varying(100)      |                                                      | extended  |                     |
 dataretries        | character varying(100)      |                                                      | extended  |                     |
 rtsretries         | character varying(100)      |                                                      | extended  |                     |

I want to add new record, but only if it doesn't exist. This is how I did it using NOT IN:

INSERT INTO session (ssid, ap, associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries) 
SELECT 26, 951, 'Mon Jun 16 17:39:35 MSK 2014', 'Mon Jun 16 17:44:35 MSK 2014', '5 min 0 sec', '172.24.6.198', '00:3a:9a:86:7d:20', 'KZN-5508', '0', '0', '802.11g', '10', '-76', '191', '0'                                                                 
WHERE (26, 951, 'Mon Jun 16 17:39:35 MSK 2014', 'Mon Jun 16 17:44:35 MSK 2014', '5 min 0 sec', '172.24.6.198', '00:3a:9a:86:7d:20', 'KZN-5508', '0', '0', '802.11g', '10', '-76', '191', '0') 
NOT IN (
        SELECT ssid, ap, associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries 
        FROM session)

But it works extremely slow. And now I want to test NOT EXISTS:

INSERT INTO SESSION (ssid, ap, associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries)
SELECT 24, 968, 'Mon Jun 16 17:39:35 MSK 2014', 'Mon Jun 16 17:44:35 MSK 2014', '5 min 0 sec', '10.96.44.22', '00:3a:9a:86:6b:30', 'KZN-5508', '0', '0', '802.11g', '0', '-128', '0', '0'
WHERE
    NOT EXISTS (
        SELECT (ssid, ap, associationtime, disassociationtime, sessionduration, clientip, clientmac, devicename, tx, rx, protocol, snr, rssi, dataretries, rtsretries) 
        FROM SSID 
        WHERE ssid=24 AND ap=968 
        AND associationtime='Mon Jun 16 17:39:35 MSK 2014' 
        AND disassociationtime='Mon Jun 16 17:44:35 MSK 2014' 
        AND sessionduration='5 min 0 sec' AND clientip='10.96.44.22' 
        AND clientmac='00:3a:9a:86:6b:30' AND devicename='KZN-5508' 
        AND tx='0' AND rx='0'AND protocol='802.11g' AND snr='0' 
        AND rssi='-128' AND dataretries='0' AND rtsretries='0');

But console says:

There is a column named "ap" in table "session", but it cannot be referenced from this part of the query.

What is wrong?

Answer 1

Is SSID the name of the table?

INSERT INTO SESSION (ssid, ap,  ...)
SELECT 24, 968, ...
WHERE  NOT EXISTS (
   SELECT 1         -- irrelevant what you put here
   FROM  session   -- assuming you want this instead of SSID
   WHERE  ssid=24
   AND    ap=968 
   AND    ...
  )