PostgreSQL: Drop Database but DB is still there [duplicate]
Did you type a ; after the DROP DATABASE test_db? Did PostgreSQL print a response to your command?
psql
Use psql’s \copy for a multi-line query
The working solution I have right now is to create a temporary view, which can be declared over multiple lines, then select from it in the \copy command, which fits comfortably on one line. db=> CREATE TEMP VIEW v1 AS db-> SELECT i db-> FROM generate_series(1, 2) AS i; CREATE VIEW db=> \cd /path/to/a/really/deep/directory/structure/on/client db=> … Read more
How to return a value from psql to bash and use it?
You can capture the result of a command using the VAR=$(command) syntax: VALUE=$(psql -qtAX -d database_name -f get_seq.sql) echo $VALUE The required psql options mean: -t only tuple -A output not unaligned -q quiet -X Don’t run .psqlrc file
pg_stat_statements enabled, but the table does not exist
Extension isn’t installed: SELECT * FROM pg_available_extensions WHERE name=”pg_stat_statements” and installed_version is not null; If the table is empty, create the extension: CREATE EXTENSION pg_stat_statements;
PostgreSQL Nested JSON Querying
This is because operator ->> gets JSON array element as text. You need a cast to convert its result back to JSON. You can eliminate this redundant cast by using operator ->: select person->’dogs’->0->’breed’ from people where id = 77;
keystroke to clear screen in psql?
As an alternative to Ctrl-L use, e.g. for scripts in an linux environment: \! clear or \! cls for Microsoft.
Show query result column types (PostgreSQL)
It is possible to get any SELECT query result column type. Example Given the following query and result, let’s answer the question *”What is the column type of all_ids?”* SELECT array_agg(distinct “id”) “all_ids” FROM “auth_user”; all_ids ——————————————– {30,461577687337538580,471090357619135524} (1 row) We need a mechanism to unveil the type of “all_ids”. On the postgres mailing list … Read more
Datatype for a URL in PostgreSQL
The answer depends on what you intend to do with the data. If you just need to store some uris in order to print them when requested, the text datatype seems indicated. There seems to be no standard about the maximum length of an url (note that browsers have their own limits, for example at … Read more
Shell script to execute pgsql commands in files
First off, do not mix psql meta-commands and SQL commands. These are separate sets of commands. There are tricks to combine those (using the psql meta-commands \o and \\ and piping strings to psql in the shell), but that gets confusing quickly. Make your files contain only SQL commands. Do not include the CREATE DATABASE … Read more
PostgreSQL psql terminal command
These are not command line args. Run psql. Manage to log into database (so pass the hostname, port, user and database if needed). And then write it in the psql program. Example (below are two commands, write the first one, press enter, wait for psql to login, write the second): psql -h host -p 5900 … Read more