3

I moved a mysql DB to Postgres, but some of the tables had a varbinary(16) field (to store an ip address).

Postgres does not support varbinary fields, and after some research, I found that the Postgres equivalent is bytea.

So, I went ahead and made tables with bytea as the field type for the ip columns.

Now, I am not sure, but this might be causing me problems.

So, now my question is if bytea and varbinary can be treated totally equally or not. For example, if the original MySQL query is:

 INSERT INTO messages(userID, userName, userRole, dateTime, ip, text)
 Values('21212111','bot','4',NOW(), inet_pton($ip), 'hi');

Note: I included some php in there, because my logs don't show what the inserted ip address is (It's a blob/something)

Now, would this query store the exact same data if it was a bytea field instead? I would presume yes, but does the same hold true for a select statement?
i.e. Would, this query return the same data regardless of whether the ip field was varbinary or bytea?

SELECT userID, userName, userRole, channel ,ip FROM table

Thanks for your time, have a good day.

zermy
  • 611
  • 1
  • 11
  • 25

3 Answers3

6

Yes, bytea is an equivalent to varbinary/image/blob et al. Having said that, PostgreSQL does come with an inet type as standard, which will take an IPv4 address in textual representation and store it as a 32-bit integer.

araqnid
  • 127,052
  • 24
  • 157
  • 134
1

IIRC, you have to use pg_escape_bytea() and pg_unescape_bytea() functions.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
  • So, you use `pg_escape_bytea()` when you are inserting a query, and when `pg_unescape_bytea` when you are parsing a query? – zermy Nov 08 '11 at 21:58
  • You don't need pg_escape_bytea() when using the correct datatype, INET in this case. Use pg_query_params() or pg_send_query_params() for your SQL and parameters to avoid SQL injection to keep things simple. – Frank Heikens Nov 09 '11 at 06:31
  • @FrankHeikens, the question was about handling `bytea` field, not about which type to use. And IIRC the `bytea` handling in php is somewhat awkward, there were some difficulties with using it with parametrized queries, but I don't remember the details. – Michael Krelin - hacker Nov 09 '11 at 06:54
  • @ Michael Krelin - hacker: Correct, but the question came up because the wrong datatype is used. Fix the real problem, not the work around. – Frank Heikens Nov 09 '11 at 07:13
  • Frank, I find your suggestion entirely valid, but I do not know enough of OP circumstances and prospects to leave him without an answer to the question he actually asked ;-) – Michael Krelin - hacker Nov 09 '11 at 09:04
1

Postgres does not support varbinary fields, and after some research, I found that the Postgres equivalent is bytea.

So, I went ahead and made tables with bytea as the field type for the ip columns.

INET is made for ip-addresses, IPv4 and IPv6, bytea is made for binary data. Always use the best matching datatype, PostgreSQL has many great datatypes. And you can create your own datatypes (TYPE or DOMAIN), if you want to. But never abuse bytea just because you can.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135