PostgreSQL : Java Glossary

* 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z (all)

A free SQL (Standard Query Language) database. The current version is 9.6.1 Last revised/verified: 2016-10-27 It is also known as Postgres, but never Postgre.

Advantages of PostgreSQL over MySQL

Postgres is a mature product with its roots in Berkeley.
Postgres has many data types.
You can add your own data tapes by defining them with a little C code.
You can write triggers in Oracle stored procedure language PLpg/SQL and in some other languages, unfortunately not in SQL itself.
Postgres is highly compatible with Oracle. It handles triggers the same way.
Postgres does not crash under heavy loads.
Postgres has remote SSL (Secure Sockets Layer) access.
Postgres has PGADMIN3 for a graphical way of administration from a PC (Personal Computer).
Postgres allows inheritance in the SQL definitions, e. g. a capitals row definition can be derived from a cities row definition.
Postgres uses multiversion concurrency control, rather than locks. Each transaction sees a snapshot of the database at a logical instant in time. Reading does not block writing and vice versa.
More standard compliant than MySQL.
Supports windowing functions.
Supports CTEs (Common Table Expressions) including writable CTEs .
Supports recursive queries.
No limits on the usage of subselect in DML (Data Manipulation Language) statements.
Supports table functions (select * from my_function()).
Supports transactional DDL (Data Definition Language).
Supports user defined aggregate functions.
Supports partial indexes.

Advantages of MySQL over PostgreSQL

MySQL is easier to set up.
MySQL support is more widespread on ISP (Internet Service Provider) webservers.
There more books and tutorials on MySQL.

PostgreSQL Supported Types

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit		fixed-length bit string
bit varying(n)	varbit(n)	variable-length bit string
Boolean	bool	logical Boolean (true/false)
box		rectangular box in the plane
bytea		binary data
character varying(n)	varchar(n)	variable-length character string
character(n)	char(n)	fixed-length character string
cidr		IPv4 (Internet Protocol Version 4) or IPv6 (Internet Protocol Version 6) network address
circle		circle in the plane
date		calendar date (year, month, day). Stored internally as 4-byte binary.
double precision	float8	double precision floating-point number
ENUM		define the enum like this CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); then use it as if it were a built-in type. It sorts in ordinal order. You set the values with ordinary Strings. The total length of all the possible values must be ≤ 63, a severe restriction. Postgre enums behave both like java enums and EnumSets. There are set SET('fragile', 'oversize', 'express')
inet		IPv4 or IPv6 host address
integer	int, int4	signed four-byte integer
interval(p)		time span
line		infinite line in the plane (not fully implemented)
lseg		line segment in the plane
macaddr		MAC (Media Access Control) address
money		currency amount
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric with selectable precision
path		open and closed geometric path in the plane
point		geometric point in the plane
polygon		closed geometric path in the plane
real	float4	single precision floating-point number
smallint	int2	signed two-byte integer
serial	serial4	autoincrementing four-byte integer
text		variable-length character string
time [ (p) ] [ without time zone ]		time of day
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] without time zone	timestamp	date and time
timestamp [ (p) ] [ with time zone ]	timestamptz	date and time, including time zone
tsquery		text search query
tsvector		text search document
txid_snapshot		user-level transaction ID snapshot
uuid		universally unique identifier
xml		XML (extensible Markup Language) data

Unfortunately, the names are not the same names as Java uses. Postgres predates Java.

Gotchas

The biggest gotchas is that you must manually install some set environment parameters. If you don’t, PostgreSQL will reject your password.
File names must use /s not \s. e.g. \i E:/com/mindprod/mother/mboards.sql to run a script. If you forget, you will get a permission denied message.
There is no SQL USE command, so you must enter the dbname on the dbcreate or psql command line, or the DriverManager.getConnection statement. You can enter SQL commands at the psql.exe command line switch:
psql -d dbname -U username --help
You can get it to explain the syntax with --help, \h and \? help commands.
To create a database.

createdb -E UTF-8 -e quotedb

Createdb then sends the following command to the database server:

CREATE DATABASE quotedb ENCODING 'UTF-8';

To import SQL command files:

psql -f some.sql

You can import data from a CSV (Comma-Separated Value) file with the following SQL command:

COPY tablename FROM 'somefile.csv' CSV NULL AS '--' ;

There are many other COPY options. By default the delimiters are , and " . . Like Java, SQL distiguishes between a null field and an empty field.
The SHOW command does almost nothing. Use \d instead to get information about tables.
When you install a new version, it may tell you the new format is incompatible with the old one. It is up to you to somehow get the data out of your old database and reload it into you new one.
The command you need to exit the interactive command processor is not obvious \q.
Postgre is royal pain when it comes to passwords.
- You must key them blind.
- You may not paste them in.
- You may not insert them with PassWord Safe.
- You may not specify them on the command line, inserting from SET parameters.
- It is unclear how the SET parameters and what you type during the install interact to set the passwords.
- There does not appear to be a way to change the password or change the userid other than uninstalling and reinstalling.
- There are no password hints.
So you have have to pick a short, insecure password.
It offer to install a dozen extra packages, but gives you no clue what they are for.

Tips

If the install freezes:
1. Hit Alt-Esc to see if there is a hidden prompt.
2. If that fails, uninstall.
3. Manually remove the X:\Program Files\PostgreSQL\ directory tree and the data directory.
4. Reboot.
5. Use Ace Utilities or similar utility to clean the registry.
6. Retry the install, using the default username (postgres) and default database name (postgres).

Ace Utilities
Database Journal comparison
DevX comparison
JDBC
LAMP
MySQL gotchas
Postgre arrays
Postgre Date and Time types
PostgreSQL JDBC driver
SQL
SQL vendors

standard footer
	This page is posted on the web at:	http://mindprod.com/jgloss/postgresql.html
	Optional Replicator mirror of mindprod.com on local hard disk J:	J:\mindprod\jgloss\postgresql.html
	Please read the feedback from other visitors, or send your own feedback about the site. Contact Roedy. Please feel free to link to this page without explicit permission.
	Canadian Mind Products IP:[65.110.21.43] Your face IP:[216.73.216.14]
Feedback	You are visitor number