表“session”中有一个名为“ap”的列，但不能从查询的这一部分引用它

Question

我有一张“会议”桌：

--------------------+-----------------------------+------------------------------------------------------+-----------+---------------------+----------
 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  |                     |

我想添加新记录，但前提是它不存在。这就是我使用而不是的方式：

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)

但它的工作速度非常慢。现在我想测试不存在：

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
   FROM session   -- assuming you want this instead of SSID
   WHERE  ssid=24
   AND    ap=968 
   AND    ...
  )