How to alter a column’s data type in a PostgreSQL table?
See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);
psql
Find the host name and port using PSQL commands
SELECT * FROM pg_settings WHERE name=”port”;
Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
I was getting the exact same error, but the above answers didn’t work for me. I had to reinstall postgresql. brew reinstall postgresql
How to select a schema in postgres when using psql?
In PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted, otherwise, if there is no match a error is raised, even if matching table names exist in … Read more
Correct way to install psql without full Postgres on macOS?
You could also use homebrew to install libpq. brew install libpq This would give you psql, pg_dump and a whole bunch of other client utilities without installing Postgres. Unfortunately since it provides some of the same utilities as are included in the full postgresql package, brew installs it “keg-only” which means it isn’t in the … Read more
Postgresql: Scripting psql execution with password
You may wish to read a summary of the ways to authenticate to PostgreSQL. To answer your question, there are several ways provide a password for password-based authentication: Via the password prompt. Example: psql -h uta.biocommons.org -U foo Password for user foo: In a pgpass file. See libpq-pgpass. Format: <host>:<port>:<database>:<user>:<password> With the PGPASSWORD environment variable. … Read more
Using psql how do I list extensions installed in a database?
In psql that would be \dx See the manual of psql for details. Doing it in plain SQL it would be a select on pg_extension: SELECT * FROM pg_extension;
psql – save results of command to a file
From psql’s help (\?): \o [FILE] send all query results to file or |pipe The sequence of commands will look like this: [wist@scifres ~]$ psql db Welcome to psql 8.3.6, the PostgreSQL interactive terminal db=>\o out.txt db=>\dt Then any db operation output will be written to out.txt. Enter ‘\o’ to revert the output back to … Read more
Alternate output format for psql
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
Run a PostgreSQL .sql file using command line arguments
Of course, you will get a fatal error for authenticating, because you do not include a user name… Try this one, it is OK for me 🙂 psql -U username -d myDataBase -a -f myInsertFile If the database is remote, use the same command with host psql -h host -U username -d myDataBase -a -f … Read more