Michael Tyson <michael@tyson.id.au> writes:
> Anything I should be looking at, here? What am I missing?
> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> --------------+----------+----------+-------------+-------------+---------------------------
> testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres +
> | | | | | postgres=CTc/postgres +
> | | | | | testdb=CTc/postgres
> postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
Given the weird spacing you're showing there, I'm suspicious that that
database isn't actually named "testdb", but has some nonprinting
character(s) in its name that confuse psql's formatting code.
An easy way to check for nonprinting characters is to do
set bytea_output to escape;
select datname::bytea from pg_database;
and see if bytea prints anything funny.
Having said that, I don't think that theory explains this result:
> pi@raspi ~ $ sudo -u postgres createdb testdb
> createdb: database creation failed: ERROR: duplicate key value violates unique constraint
"pg_database_datname_index"
> DETAIL: Key (datname)=(testdb) already exists.
There's definitely something weird there, because what you ought to get
in normal cases is
createdb: database creation failed: ERROR: database "testdb" already exists
There may be some corruption in pg_database_datname_index that is allowing
the initial search to not find "testdb" but then the insertion decides
there's a conflict.
regards, tom lane