Home   Notes   Contact Me

Postgres

Postgres

Unsorted

Database Delete

DROP DATABASE {database name}
Be sure you are connected to another database when you try to drop it.

Show Columns

SELECT column_name FROM information_schema.columns WHERE table_name = 'PUT TABLE NAME HERE';

Show Databases

SELECT datname FROM pg_database;

Show Tables

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Other Databases, querying from one you are not in

private static String linkMainSQL( String tableName, String columnName, String type, int rowNum) { if ( type == null) { return "(SELECT " + "dblink_tok(t1.dblink_" + tableName + ",0) AS " + columnName + " FROM (" + "SELECT dblink('hostaddr=127.0.0.1 port=5432 dbname=smx_main user=postgres'," + "'SELECT " + columnName + " FROM " + tableName + " LIMIT 1 OFFSET " + rowNum + "') AS dblink_" + tableName + ") AS t1" + ")"; } else if ( type.equals("BOOL")) { return "(SELECT " + "textToBoolean(dblink_tok(t1.dblink_" + tableName + ",0)) AS " + columnName + " FROM (" + "SELECT dblink('hostaddr=127.0.0.1 port=5432 dbname=smx_main user=postgres'," + "'SELECT " + columnName + " FROM " + tableName + " LIMIT 1 OFFSET " + rowNum + "') AS dblink_" + tableName + ") AS t1" + ")"; } else { return "(SELECT " + "CAST(dblink_tok(t1.dblink_" + tableName + ",0) AS " + type + ") AS " + columnName + " FROM (" + "SELECT dblink('hostaddr=127.0.0.1 port=5432 dbname=smx_main user=postgres'," + "'SELECT " + columnName + " FROM " + tableName + " LIMIT 1 OFFSET " + rowNum + "') AS dblink_" + tableName + ") AS t1" + ")"; } }

Current Queries, how to show them

SELECT * FROM pg_stat_activity;

JDBC Allowing access from another machine

  1. Edit /usr/local/pgsql/data/pg_hba.conf
  2. Add a line such as:
    host all 172.16.0.146 255.255.255.255 trust
    (host [database] [ip of client] [ip mask] [auth_type]
  3. Restart Postgres

DISTINCT ON (postgres specific)

SELECT DISTINCT ON (name, age) name, address FROM table ;
! Note: There is no comma after the )

Windows, Installing (under cygwin)

Regardless of the installation type, the first step is to install the latest cygipc from:
http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html
The following is the NT services Cygwin PostgreSQL installation procedure (with footnotes designated by "[n]"): 0. (jbp) add to the system path "C:\cygwin\bin (to allow access to the cygwindll) and then _reboot_ (argh) 1. Install the cygipc ipc-daemon as a NT service: # ipc-daemon --install-as-service 2. Create the "postgres" user account: # net user postgres /add /fullname:postgres /comment:'PostgreSQL user account' /homedir:"$(cygpath -w /home/postgres)" # mkpasswd -l -u postgres >>/etc/passwd 3. Grant the "postgres" user the "Log on as a service" user right: # cmd /c secpol.msc # [3] [4] [5] 4. Install postmaster as a NT service: !! You will be prompted to create a postgres user password at this point # cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /usr/share/postgresql/data -i" --dep ipc-daemon --termsig INT --user postgres --shutdown # [6] 5. Create the PostgreSQL data directory: # mkdir /usr/share/postgresql/data 6. Change ownership of the PostgreSQL data directory: # chown postgres /usr/share/postgresql/data # [10] 7. Start the cygipc ipc-daemon: # net start ipc-daemon # [7] 8. Initialize PostgreSQL (*when running under the "postgres" account*): $ initdb -D /usr/share/postgresql/data 9. Start postmaster: # net start postmaster # [7] 10. Connect to PostgreSQL: # psql -U postgres template1 # [8] [9] The following are the notes to the above: [1] The "#" prompt indicates running as a user which is a member of the Local Administrators group. [2] The "$" prompt indicates running as the "postgres" user. Log in as "postgres" or use ssh to emulate Unix's "su" command. [3] Sorry, but I don't know of a generally available command line way of setting user rights. [4] On Windows 2000, this starts the "Local Security Settings" applet. On Windows NT 4.0, do the analogous operation. [5] See http://support.microsoft.com/default.aspx?scid=KB;en-us;q259733 for a Microsoft KB article explaining how to configure user rights. [6] Clean postmaster shutdown will only work with a post Cygwin 1.3.2 snapshot from 2001-Jul-28 or later. [7] Cygwin's bin directory (e.g., C:\Cygwin\bin) must be added to the Windows NT/2000's system PATH and the machine rebooted for the SCM to find cygwin1.dll. [8] Actually, psql can run under any user account. [9] One can use PostgreSQL's createuser command or set PGUSER to obviate the need to specify "-U postgres" on the psql command line. [10] Assumes that ntsec is set via the CYGWIN environment variable.
  1. Verify postgres is running (in a cygwin shell, psql -l)
  2. Connect to one of the dbs (i.e. psql -d template0)
  3. Create a database
    CREATE DATABASE {name}
  4. Create a table
    CREATE TABLE test ( name VARCHAR(32), age INT4 ) ; INSERT INTO test ( name, age ) VALUES ( 'Alan', 20 ) ; INSERT INTO test ( name, age ) VALUES ( 'Betty', 18 ) ;
  5. Get ODBC driver from: ODBC driver
  6. Install it
  7. Use "ODBC Data Source Administrator" to set up a Data Source. (it is located at: Start->Settings->Control Panel->Administrative Tools->Data Sources

Table Creation

CREATE [ TEMPORARY | TEMP ] TABLE tablename ( columnname columntype [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT defvalue ] [ column_constraint | PRIMARY KEY ] [ ,… ] [, PRIMARY KEY ( column [ ,… ]) ] [, CHECK (condition) ] [, table_constraint ] ) [ INHERITS (inheritable [,…]) ]

JDBC

JDBC Faq at http://www.math.eku.edu/tools/jdbc/faq.html
Driver File: postgresql.jar Driver Name: org.postgresql.Driver

What is the syntax of the JDBC URL?

The driver recognises several forms of URL, based around the following template:
jdbc:driver://host:port/database?option=value&user=userid&password=password

Here's an explanation of each field:


 
Field Purpose
driver The JDBC driver name.
Here, this can be either postgres95 or postgresql.
postgres95 indicates that you want to connect without authentication.
postgresql indicates that you want to connect using password authentication.
host The host running the server. If this is absent, then it assumes localhost
port The port that the server is listening to. If absent, it defaults to 5432
database The database name
option=value Options about the database backend. These can be left out
userid The userid. If this is leftout, then you need to pass it through the DriverManager.getConnection(url,userid,password) call.
password The password.

Heres some examples:


 
jdbc:postgres95:pgtest?user=postgres&password=pass Database pgtest on the local machine, using the userid postgres
Because we are using no-authentication, the password pass is ignored.
jdbc:postgresql:pgtest?user=postgres&password=pass As above, but use password authentication.
jdbc:postgresql://myhost/pgtest?user=postgres&password=pass Connect to database pgtest on server myhost, using password authentication with user postgres, password pass.
jdbc:postgres95://localhost:8000/pgtest?user=postgres&password=pass Connect to the server listening on port 8000

Transactions

BEGIN; SQL goes here... COMMIT; or BEGIN; SQL goes here... ROLLBACK;

Indexes

NameDesc
Create
CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_method ] ( column [ ops_name ] [, ...] ) [ WHERE predicate ] CREATE [ UNIQUE ] INDEX index_name ON table [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) [ WHERE predicate ] Example: CREATE INDEX myIndex ON table1 (colname) ;

Connections, setting the max number

  1. Edit /usr/local/psql/data/postgresql.conf
  2. uncomment and set max_connections
  3. uncomment and set shared_buffers

Meta Commands

ActionPosgreSQL
Help list of commands\? or \h
Connect to another Database\c[onnect] [DBNAME|- [USER]]
Listing Tables\dt
Listing Databases\l
Listing Databases and Views\d
Show Users\du