PostFAQ - an Unofficial PostgreSQL FAQ Addendum

Table of Contents

1. General
1.1. PostgreSQL, Pg, PgSQL, Postgres, psql - which is which?
1.2. Why are there so many websites with PostgreSQL or variants in their domain name which are not officially part of the PostgreSQL project?
2. SQL
2.1. What are the advantages of datatype BOOLEAN over e.g. CHAR(1)?
2.2. Why don't casts such as SELECT bar::INT4 FROM foo work when bar is defined as VARCHAR?
2.3. How can I delete some but not all identical rows?
2.4. How can I transfer text in a BYTEA column to a TEXT or VARCHAR column?
2.5. Why do seperate invocations of NOW() return the same timestamp?
2.6. How can I emulate MySQL's use of NULL to obtain the next value of an AUTO_INCREMENT pseudo-sequence?
2.7. Where can I obtain the SQL standards?
3. Administration
3.1. How does PostgreSQL structure its data files? What are all the numbered files and directories in my $PG_DATA/base/ directory?
3.2. How can I view the definition of stored VIEWs, procedures etc.?
3.3. How can I get PostgreSQL to automatically start on boot / stop on shutdown?
3.4. How can I assign database ownership to a different user?
3.5. How can I see which users are currently connected to a database?
3.6. PostgreSQL causes my Intel Linux system to crash
3.7. How do I restore template1?
4. psql
4.1. How do I get query results formatted in the same style as in MySQL or Oracle (SQL*PLUS)?
4.2. How do I force null values to be displayed as NULL?
4.3. How can I insert character codes using psql?
4.4. A \connect to a non-existent user causes psql to disconnect completely
5. Localization / Internationalization
5.1. Do I need to specify --enable-locale to get locale support when building PostgreSQL?
5.2. Why are my locale settings causing errors?
5.3. How do I convert an accented (or whatever) character to its ASCII equivalent?
5.4. How do I ensure correct sort order in a particular language, e.g. German?
6. Applications and Hosting
6.1. Is there a web-based GUI tool for PostgreSQL?
6.2. Where can I find a list of web hosters offering PostgreSQL support?
6.3. What GUI tools does PostgreSQL have?

This is a miscellaneous collection of solutions to questions and problems encountered using PostgreSQL. It complements the PostgreSQL FAQ located at http://www.postgresql.org/docs/faqs/FAQ.html.

Comments, corrections etc. to barwick@gmx.net

1. General

1.1. PostgreSQL, Pg, PgSQL, Postgres, psql - which is which?

PostgreSQL is the official name and should be used wherever possible, especially in documentation.

Pg is a widely used abbreviation particularly when used as part as an application or product name to indicate PostgreSQL compatibility, e.g. phpPgAdmin, pgAdmin. Postgres also occurs frequently, particularly as it is easier to pronounce. It is also often the name of the system user in UNIX systems responsible for running the database software. PgSQL and variants is a less commonly used abbreviation; it is also used by the support company PostgreSQL, Inc. to abbreviate their company name.

psql is PostgreSQL's command line client and should not be use to refer to the entire database server (it confuses people).

Variants such as PostgresSQL, Postgress are plain wrong.

1.2. Why are there so many websites with PostgreSQL or variants in their domain name which are not officially part of the PostgreSQL project?

Table 1. Websites containing PostgreSQL or variants in the domain name

http://www.pgsql.com/ Official site of PostgreSQL, Inc.
http://www.pgsql.info/ Website of the German author Cornelia Boenigk with information on her book "PostgreSQL: Grundlagen, Praxis, Anwendungsentwicklung mit PHP"
Website from Cornelia Boenigk and Ralf Burger with information in German
http://www.postgresql.at Commercial website belonging to the Austrian company Cybertec Geschwinde & Schoenig
http://www.postgresql.jp/ Information portal of the Japan PostgreSQL Users Group
http://www.postgresql.info/ Redirect to the online version of the O'Reilly book "Practical PostgreSQL" from Command Prompt, Inc.

As a purely open-source software project the name PostgreSQL and variants thereof are not protected by a lawyer-enabled international corporation. Individuals and companies offering PostgreSQL-related information and services are therefore often able to reserve domain names containing variants of the PostgreSQL name for their own purposes.