SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: PGSQL Syntax

  1. #1
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PGSQL Syntax

    I have the following schema for a referential table in a MySQL database:

    Code:
    ip varchar(255) NOT NULL,
    value varchar(255) NOT NULL,
    schedule int(11) NOT NULL,
    UNIQUE KEY (ip)
    "ip" is going to be used to record IP addresses.
    "value" will contain 3 string values: "alive", "dead", or "unknown".
    "schedule" will store Unix timestamps.
    Since the table is storing IP addresses, I wanted to set ip to UNIQUE KEY to prohibit duplicates. Doing this would essentially make this table a referential table. Correct so far?

    I'm not sure if the above schema for MySQL is as optimized as it should be, so please feel free to educate me further about what might be done to make it better.

    That aside, I need the above to be translated into PGSQL. Is it as simple as copying over the MySQL schema into PGSQL or is there more to it? I read that the keys need to be executed separately from the SQL that creates the tables... Is this true? Because in MySQL, you don't have to do this...

    Any insight is appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Wolf_22 View Post
    Doing this would essentially make this table a referential table. Correct so far?
    depends on what you mean by a "referential" table

    you should probably make ip the PRIMARY KEY rather than UNIQUE KEY, because UNIQUE KEY will allow a null value

    your definitions are fine for postgresql

    as for declaring postgresql keys separately, why don't you test it and see?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So basically it sounds like PRIMARY KEY is usually the preferred route because it also has the UNIQUE constraint... Yes?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes... and this is true in all database systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •