5 ms·

I wish sqlite made their terminal interface a bit more robust or emulated psql’s interface. Simple things like \d tablename would be great.

srcreigh 4y ago
.schema tablename
- tmpfile 4y ago
  The output is apples and oranges tho. Since I was downvoted by someone I'll added a simple example to show the difference between the two interfaces. I shouldn't have assumed anyone here was familiar with the respective representations. Sample data: CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id; INSERT INTO users VALUES (1, 'Bob'); INSERT INTO users VALUES (2, 'Sally'); SQLITE3 OUTPUT sqlite> .schema CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id /* user_comment_view(user_id,comment) */; sqlite> .schema users CREATE TABLE users (user_id serial, name text); sqlite> select * from users; 1|Bob 2|Sally POSTGRESQL OUTPUT test=# \d List of relations Schema | Name | Type | Owner --------+-------------------------+----------+---------- public | comments | table | postgres public | comments_comment_id_seq | sequence | postgres public | user_comment_view | view | postgres public | users | table | postgres public | users_user_id_seq | sequence | postgres (5 rows) test=# \d users Table "public.users" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+---------------------------------------- user_id | integer | | not null | nextval('users_user_id_seq'::regclass) name | text | | | test=# select * from users; user_id | name ---------+------- 1 | Bob 2 | Sally (2 rows) Postgres also supports adding + to commands to get additional extended information, eg, \d+. You can also filter by tables (\dt), filter by views (\dv), filter by functions (\df), etc. It's allows much more natural enumeration of the DB which I wish sqlite had as well.

srcreigh 4y ago

.schema tablename

tmpfile 4y ago
The output is apples and oranges tho. Since I was downvoted by someone I'll added a simple example to show the difference between the two interfaces. I shouldn't have assumed anyone here was familiar with the respective representations. Sample data: CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id; INSERT INTO users VALUES (1, 'Bob'); INSERT INTO users VALUES (2, 'Sally'); SQLITE3 OUTPUT sqlite> .schema CREATE TABLE users (user_id serial, name text); CREATE TABLE comments (comment_id serial, user_id int, comment text unique); CREATE VIEW user_comment_view as select u.user_id, u.name, c.comment from users u, comments c where u.user_id = c.user_id /* user_comment_view(user_id,comment) */; sqlite> .schema users CREATE TABLE users (user_id serial, name text); sqlite> select * from users; 1|Bob 2|Sally POSTGRESQL OUTPUT test=# \d List of relations Schema | Name | Type | Owner --------+-------------------------+----------+---------- public | comments | table | postgres public | comments_comment_id_seq | sequence | postgres public | user_comment_view | view | postgres public | users | table | postgres public | users_user_id_seq | sequence | postgres (5 rows) test=# \d users Table "public.users" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+---------------------------------------- user_id | integer | | not null | nextval('users_user_id_seq'::regclass) name | text | | | test=# select * from users; user_id | name ---------+------- 1 | Bob 2 | Sally (2 rows) Postgres also supports adding + to commands to get additional extended information, eg, \d+. You can also filter by tables (\dt), filter by views (\dv), filter by functions (\df), etc. It's allows much more natural enumeration of the DB which I wish sqlite had as well.