The error states that the psql utility can’t find the socket to connect to your database server. Either you don’t have the database service running in the background, or the socket is located elsewhere, or perhaps the pg_hba.conf needs to be fixed. Step 1: Verify that the database is running The command may vary depending … Read more
I just needed to spend more time staring at the documentation. This command: \x on will do exactly what I wanted. Here is some sample output: select * from dda where u_id=24 and dda_is_deleted=’f’; -[ RECORD 1 ]——+—————————————————————————————————————————————————————————————————————- dda_id | 1121 u_id | 24 ab_id | 10304 dda_type | CHECKING dda_status | PENDING_VERIFICATION dda_is_deleted | … Read more
In all schemas: => \dt *.* In a particular schema: => \dt public.* It is possible to use regular expressions with some restrictions \dt (public|s).(s|t) List of relations Schema | Name | Type | Owner ——–+——+——-+——- public | s | table | cpn public | t | table | cpn s | t | table … Read more
If you wish to list all tables, you must use: \dt *.* to indicate that you want all tables in all schemas. This will include tables in pg_catalog, the system tables, and those in information_schema. There’s no built-in way to say “all tables in all user-defined schemas”; you can, however, set your search_path to a … Read more
/etc/services is only advisory, it’s a listing of well-known ports. It doesn’t mean that anything is actually running on that port or that the named service will run on that port. In PostgreSQL’s case it’s typical to use port 5432 if it is available. If it isn’t, most installers will choose the next free port, … Read more
To disable pagination but retain the output, use: \pset pager off To remember this setting, add it to your ~/.psqlrc, e.g. like this: echo \\pset pager off >> ~/.psqlrc See the psql manual. On older versions of Pg it was just a toggle, so \pset pager To completely suppress query output, use \o /dev/null in … Read more
Newer versions: (from 8.4 – mentioned in release notes) TABLE mytablename; Longer but works on all versions: SELECT * FROM mytablename; You may wish to use \x first if it’s a wide table, for readability. For long data: SELECT * FROM mytable LIMIT 10; or similar. For wide data (big rows), in the psql command … Read more
The view pg_indexes provides access to useful information about each index in the database, eg. select * from pg_indexes where tablename not like ‘pg%’;
Postgres variables are created through the \set command, for example … \set myvariable value … and can then be substituted, for example, as … SELECT * FROM :myvariable.table1; … or … SELECT * FROM table1 WHERE :myvariable IS NULL; edit: As of psql 9.1, variables can be expanded in quotes as in: \set myvariable value … Read more
Change the user to postgres : su – postgres Create User for Postgres (in the shell and NOT with psql) $ createuser testuser Create Database (same) $ createdb testdb Acces the postgres Shell psql ( enter the password for postgressql) Provide the privileges to the postgres user $ alter user testuser with encrypted password ‘qwerty’; … Read more