in today “waiting for 8.4" i will show you 3 new patches. all of them add new functionality to psql – the most common tool to access postrgesql database.
first patch (by kenneth d'souza, with changes by tom lane, applied by tom lane) adds information about foreign keys in \d table output.
what? it was always there, you say? yes, but take a look at this:
CREATE TABLE users ( id SERIAL PRIMARY KEY, username TEXT NOT NULL DEFAULT '' UNIQUE ); CREATE TABLE sessions ( id SERIAL PRIMARY KEY, user_id INT4 NOT NULL DEFAULT 0 REFERENCES users (id), started TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE user_data ( id SERIAL PRIMARY KEY, user_id INT4 NOT NULL DEFAULT 0 REFERENCES users (id), FIELD TEXT NOT NULL, VALUE TEXT, UNIQUE (user_id, FIELD) );
3 simple tables, with 2 foreign keys. and how does their \d XXX look like?
# \d users Table "public.users" Column | Type | Modifiers ----------+---------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null default ''::text Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_username_key" UNIQUE, btree (username)
# \d sessions Table "public.sessions" Column | Type | Modifiers ---------+--------------------------+------------------------------------------------------- id | integer | not null default nextval('sessions_id_seq'::regclass) user_id | integer | not null default 0 started | timestamp with time zone | not null default now() Indexes: "sessions_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
# \d user_data Table "public.user_data" Column | Type | Modifiers ---------+---------+-------------------------------------------------------- id | integer | not null default nextval('user_data_id_seq'::regclass) user_id | integer | not null default 0 field | text | not null value | text | Indexes: "user_data_pkey" PRIMARY KEY, btree (id) "user_data_user_id_key" UNIQUE, btree (user_id, field) Foreign-key constraints: "user_data_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
ok, and how it does look with this new patch?
# \d users Table "public.users" Column | Type | Modifiers ----------+---------+---------------------------------------------------- id | integer | not null default nextval('users_id_seq'::regclass) username | text | not null default ''::text Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_username_key" UNIQUE, btree (username) Referenced by: "sessions_user_id_fkey" IN sessions FOREIGN KEY (user_id) REFERENCES users(id) "user_data_user_id_fkey" IN user_data FOREIGN KEY (user_id) REFERENCES users(id)
# \d sessions Table "public.sessions" Column | Type | Modifiers ---------+--------------------------+------------------------------------------------------- id | integer | not null default nextval('sessions_id_seq'::regclass) user_id | integer | not null default 0 started | timestamp with time zone | not null default now() Indexes: "sessions_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
# \d user_data Table "public.user_data" Column | Type | Modifiers ---------+---------+-------------------------------------------------------- id | integer | not null default nextval('user_data_id_seq'::regclass) user_id | integer | not null default 0 field | text | not null value | text | Indexes: "user_data_pkey" PRIMARY KEY, btree (id) "user_data_user_id_key" UNIQUE, btree (user_id, field) Foreign-key constraints: "user_data_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
if you dont see what changed, take a look at output for users table. and note “Referenced by:" section
second patch (by andrew gilligan, also applied by tom lane) modifies output of \l and \l+ (list of databases).
before applying patch, it looked like this:
<code># \l List of databases Name | Owner | Encoding -----------------+-----------------+---------- xxxxxxx | depesz | UTF8 depesz | depesz | UTF8 pgdba | pgdba | UTF8 postgres | pgdba | UTF8 template0 | pgdba | UTF8 template1 | pgdba | UTF8 yyyyyyyyyyyyyyy | zzzzzzzzzzzzzzz | UTF8 (7 rows)</code>
# \l+ List of databases Name | Owner | Encoding | Tablespace | Description -----------------+-----------------+----------+------------+--------------------------- xxxxxxx | depesz | UTF8 | pg_default | depesz | depesz | UTF8 | pg_default | pgdba | pgdba | UTF8 | pg_default | postgres | pgdba | UTF8 | pg_default | template0 | pgdba | UTF8 | pg_default | template1 | pgdba | UTF8 | pg_default | Default template database yyyyyyyyyyyyyyy | zzzzzzzzzzzzzzz | UTF8 | pg_default | (7 rows)
with this new patch we have “a bit" more information:
# \l List of databases Name | Owner | Encoding | Access Privileges -----------------+-----------------+----------+---------------------------- xxxxxxx | depesz | UTF8 | depesz | depesz | UTF8 | pgdba | pgdba | UTF8 | postgres | pgdba | UTF8 | template0 | pgdba | UTF8 | {=c/pgdba,pgdba=CTc/pgdba} template1 | pgdba | UTF8 | {pgdba=CTc/pgdba,=c/pgdba} yyyyyyyyyyyyyyy | zzzzzzzzzzzzzzz | UTF8 | (7 rows)
# \l+ List of databases Name | Owner | Encoding | Access Privileges | Size | Tablespace | Description -----------------+-----------------+----------+----------------------------+---------+------------+--------------------------- xxxxxxx | depesz | UTF8 | | 20 MB | pg_default | depesz | depesz | UTF8 | | 4359 kB | pg_default | pgdba | pgdba | UTF8 | | 4223 kB | pg_default | postgres | pgdba | UTF8 | | 4215 kB | pg_default | template0 | pgdba | UTF8 | {=c/pgdba,pgdba=CTc/pgdba} | 4136 kB | pg_default | template1 | pgdba | UTF8 | {pgdba=CTc/pgdba,=c/pgdba} | 4215 kB | pg_default | Default template database yyyyyyyyyyyyyyy | zzzzzzzzzzzzzzz | UTF8 | | 4271 kB | pg_default | (7 rows)
pretty cool. of course one could get the same data also in 8.3, but it required complicated queries. and now, it's accessible with simple \l+
last patch (by greg sabino mullane, with simplification by tom lane, applied by tom lane) is hard to show, as it changes (fixes) tab-completion.
basically, if you ever used schemas, you know that psql was not able to <tab-complete> when you have query like:
SELECT * FROM schema.table WHERE fi<tab>
now it works correctly. commit log states exactly what was modified:
Improve psql's tab completion to handle completing attribute names in cases
where the relation name was schema-qualified, for example
UPDATE foo.bar SET <tab>
Also support cases where the relation name was quoted unnecessarily,
for example
UPDATE "foo" SET <tab>
excellent news!