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!