sql - 表 "ap"中有一个名为 "session"的列，但无法从这部分查询中引用它

Question

我有一个表“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  |                     |

我想添加新记录，但前提是它不存在。这就是我使用 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)

但是它运行起来非常慢。现在我想测试 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');

但是控制台说:

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

怎么了？

Answer 1

SSID是表名吗？

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