Do you want the resulting file on the server, or on the client? Server side If you want something easy to re-use or automate, you can use Postgresql’s built in COPY command. e.g. Copy (Select * From foo) To ‘/tmp/test.csv’ With CSV DELIMITER ‘,’ HEADER; This approach runs entirely on the remote server – it … Read more
The Homebrew package manager includes launchctl plists to start automatically. For more information, run brew info postgres. Start manually pg_ctl -D /usr/local/var/postgres start Stop manually pg_ctl -D /usr/local/var/postgres stop Start automatically “To have launchd start postgresql now and restart at login:” brew services start postgresql What is the result of pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log … Read more
Run this query from PostgreSQL: SELECT version();
In PostgreSQL, you can use the \connect meta-command of the client tool psql: \connect DBNAME or in short: \c DBNAME
If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public) DROP SCHEMA public CASCADE; CREATE SCHEMA public; If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants. GRANT ALL ON SCHEMA public TO … Read more
To log in without a password: sudo -u user_name psql db_name To reset the password if you have forgotten: ALTER USER user_name WITH PASSWORD ‘new_password’;
DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more
Type \q and then press ENTER to quit psql. UPDATE: 19-OCT-2018 As of PostgreSQL 11, the keywords “quit” and “exit” in the PostgreSQL command-line interface have been included to help make it easier to leave the command-line tool.
Try this (in the psql command-line tool): \d+ tablename See the manual for more info.
From the psql command line interface, First, choose your database \c database_name Then, this shows all tables in the current schema: \dt Programmatically (or from the psql interface too, of course): SELECT * FROM pg_catalog.pg_tables; The system tables live in the pg_catalog database.