#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    11

    postgres many & optional


    I have two tables assigned_ipaddress and device. Device can have one or many IP address. Inside the assigned_ipaddress table I have stored the NAT mapping of the internal and external IP.

    My question here is I would like to create list of device that was owned by customer disregarding whether the device itself have any ip address assgined. If I create a view to join this two tables, those device that was not assigned any IP will not be in the list. Is there anyway to work around with this.

    CREATE TABLE "device" (
    "sid" int4 DEFAULT nextval('public.device_sid_seq') NOT NULL ,
    "hostname" varchar(25),
    "serial_no" varchar(20),
    "state" varchar(1),
    "os_id" int4 NOT NULL ,
    "last_modify" timestamp without time zone,
    "pid" int4 NOT NULL ,
    "monitoring" varchar(15),
    "rack_no" varchar(5),
    "datacenter" varchar(15),
    "device_spec" text,
    "device_type" varchar(15),
    "model_no" varchar(20),
    "service_level" varchar(20),
    "sys_handle_no" varchar(20),
    "notes" text,
    "vendor" varchar(20),
    "alias" varchar(25),
    CONSTRAINT "os_id_delete" FOREIGN KEY ("os_id") REFERENCES os(os_id) MATCH FULL ,
    CONSTRAINT "pid_delete" FOREIGN KEY ("pid") REFERENCES project(pid) MATCH FULL ,
    CONSTRAINT "device_pkey" PRIMARY KEY ("sid")
    );

    CREATE TABLE "assigned_ip" (
    "ip_id" int4 DEFAULT nextval('public.assigned_ip_ip_id_seq') NOT NULL ,
    "sid" int4 NOT NULL ,
    "internal_ip1" varchar(15),
    "external_ip" varchar(15),
    "internal_ip2" varchar(15),
    "notes" varchar(30),
    CONSTRAINT "sid_delete" FOREIGN KEY ("sid") REFERENCES device(sid) MATCH FULL ,
    CONSTRAINT "assigned_ip_pkey" PRIMARY KEY ("ip_id")
    );
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    11

    order by ip address


    One more thing is the order by it doesn't seem to work for IP address

    The order will become like this
    190.170.70.1
    190.170.70.139
    190.170.70.140
    190.170.70.2

    instead of
    190.170.70.1
    190.170.70.2
    190.170.70.139
    190.170.70.140
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    do you have the availability of a procedural language, or other method of interfacing a high-level (not SQL) language with the database?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    if the join is on SID then those with a NULL SID from "device" will not be included. period. I don't understand why you would want them to appear??? This is also why I am suggesting using a PL/pgSQL or PLpython or something so that you can more concisely manipulate the data. This will also allow you to do string parsing and manipulation to do the sort of IP addresses which you described. Order by considers the .2 to be higher than .140 or whatever because it's comparing by ASCII the same way we alphabetize (I am pretty sure; this is a common issue)-- one way to deal with it is to feed it to an awk shell command, using the "." as a delineator then awk {'print $4'} will isolate the fourth number, you can then do an `eval` or $(eval ... ) on it to turn it to a number and then use -ne for comparison. Or in oracle PL/SQL you could just put the ip address into a local variable, step through using a cursor, do a substring and I think you can do a type cast; generally, you want to do string manipulation, then do a typecast to an integer and compare those; keep track of which two IP's you're currently comparing.

IMN logo majestic logo threadwatch logo seochat tools logo