Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to sort the output of meta / slash commands in `psql`?
For example, how to sort the output below by the values in "Column" in the psql shell itself?
my_db=> \d auth_user
Table "public.auth_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('auth_user_id_seq'::regclass)
is_superuser | boolean | | not null |
username | character varying(150) | | not null |
first_name | character varying(150) | | |
last_name | character varying(150) | | not null |
email | character varying(254) | | |
is_staff | boolean | | not null | false
is_active | boolean | | not null | false
password | character varying(128) | | |
last_login | timestamp with time zone | | |
date_joined | timestamp with time zone | | |
Indexes:
"auth_user_pkey" PRIMARY KEY, btree (id)
"auth_user_username_6821ab7c_like" btree (username varchar_pattern_ops)
"auth_user_username_key" UNIQUE CONSTRAINT, btree (username)
I know that this would be easy to do outside the psql shell, for example in Bash,
$ psql -U postgres -h localhost -d my_db -c '\d auth_user' | grep '^.*|' | sort -t'|' -k1
Column | Type | Collation | Nullable | Default
date_joined | timestamp with time zone | | |
email | character varying(254) | | |
first_name | character varying(150) | | |
id | integer | | not null | nextval('auth_user_id_seq'::regclass)
is_active | boolean | | not null | false
is_staff | boolean | | not null | false
is_superuser | boolean | | not null |
last_login | timestamp with time zone | | |
last_name | character varying(150) | | not null |
password | character varying(128) | | |
username | character varying(150) | | not null |
but I'm curious if this could be done in psql at all.
1 answer
The following users marked this post as Works for me:
| User | Comment | Date |
|---|---|---|
| toraritte |
Thread: Works for me Exactly what I needed. Thanks:) |
May 12, 2024 at 22:58 |
It's been a while, but the answer is "yes and no," I believe.
No: The describe-schema command doesn't (last I heard) allow any modification.
Yes: The describe-schema command also doesn't do anything that a SELECT statement from information_schema doesn't do, so something like this should work.
SELECT
column_name
FROM
information_schema.columns
WHERE
table_schema = 'public'
AND
table_name = 'auth_user'
ORDER BY
column_name ASC;
Assuming that they the PostgreSQL team hasn't moved anything in the years that I've had to deal with everything else, that should at least get you close.
Another approach to get around the immutability of the psql meta-commands is to list the SQL queries that are executed by them, and modify the relevant parts (e.g., by adding an ORDER BY clause).
For example, here's how to list the queries behind \d:
$ psql -E -d my_db
my_db=> \d auth_user
or
my_db=> \set ECHO_HIDDEN on
my_db=> \d auth_user
That will also show you the metadata query. Copy and paste that query and add your own ORDER BY clause.
0 comment threads