Useful PostgreSQL Queries Compared with MySQL

Recently while creating an SQL script for PostgreSQL, I found that some of the queries where not straightforward, specially if you are used to MySQL. I'm listing some of them here in case someone finds it useful.

Auto Increment

In MySQL you usually use AUTO_INCREMENT attribute as follows.

CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
Name varchar(255)
)

In PostgreSQL a similar result can be obtained by using SERIAL data type.

CREATE TABLE users (
id SERIAL
);

SERIAL is not an actual type, it is just a keyword used for convenience which is equivalent to specifying:

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id integer DEFAULT nextval('users_id_seq') NOT NULL
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL

Easily Enable/Disable SVN Proxy

To configure SVN proxy settings you need to edit the servers file in svn. In Linux, this can be found at ~/.subversion/servers.

Open this file in a text editor and change the settings as following by including the proxy host and port under the [global] section. Uncomment those two lines and remove any leading spaces.

[global]
# http-proxy-exceptions = *.exception.com, www.internal-site.org
http-proxy-host = proxy.host.com
http-proxy-port = 1234
# http-proxy-username = defaultusername
# http-proxy-password = defaultpassword
# http-compression = no
# http-auth-types = basic;digest;negotiate
# No http-timeout, so just use the builtin default.
# No neon-debug-mask, so neon debugging is disabled.
# ssl-authority-files = /path/to/CAcert.pem;/path/to/CAcert2.pem

If you get any errors, make sure that there are no spaces at the start of those lines.

For most of us, proxy is used only at school/university/work. Everywhere else you don't use proxy. So Most of the time you need configure the settings once. But you will have to enable/disable proxy every time. That means opening this file in an editor and commenting/uncommenting manually every single time.

After getting fed up with doing this every time I move to and from university, I came up with a better way to do this.