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
The following steps work for a fresh install of postgres 9.1 on Ubuntu 12.04. (Worked for postgres 9.3.9 on Ubuntu 14.04 too.) By default, postgres creates a user named ‘postgres’. We log in as her, and give her a password. $ sudo -u postgres psql \password Enter password: … … Logout of psql by typing … Read more
To see where the data directory is, use this query. show data_directory; To see all the run-time parameters, use show all; You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query. SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;
You can run Postgres this way (map a port): docker run –name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres So now you have mapped the port 5432 of your container to port 5432 of your server. -p <host_port>:<container_port> .So now your postgres is accessible from your public-server-ip:5432 To test: Run the postgres database (command above) … Read more
In your data/postgresql.conf file, change the log_statement setting to ‘all’. Edit Looking at your new information, I’d say there may be a few other settings to verify: make sure you have turned on the log_destination variable make sure you turn on the logging_collector also make sure that the log_directory directory already exists inside of the … Read more
There are two tools to look at, depending on how you created the dump file. Your first source of reference should be the man page pg_dump as that is what creates the dump itself. It says: Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands … Read more
COPY products_273 TO ‘/tmp/products_199.csv’ WITH (FORMAT CSV, HEADER); as described in the manual.
You can use the REASSIGN OWNED command. Synopsis: REASSIGN OWNED BY old_role [, …] TO new_role This changes all objects owned by old_role to the new role. You don’t have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a … Read more
Use LOWER function to convert the strings to lower case before comparing. Try this: SELECT id FROM groups WHERE LOWER(name)=LOWER(‘Administrator’)
When you get a connection to PostgreSQL it is always to a particular database. To access a different database, you must get a new connection. Using \c in psql closes the old connection and acquires a new one, using the specified database and/or credentials. You get a whole new back-end process and everything.