Tony Tony - 5 months ago 51
SQL Question

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

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

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    ...
  )
Comments