Psql

Postgres

Fun fact: "Postgres" has exactly as many characters as "Database"
Check for yourself!
Postgres --> 8 chars!
Database --> 8 chars!

Postgres

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

PostgreSQL

sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

Source: postgresapp.com

Postgresql

# The bash function below utilizes the CLI tool psql to vacuum analyze tables
# in a single schema which can be identified by either passing the name of
# the schema as the first parameter to the function
# or setting the environment variable PG_SCHEMA:
#

vacuum_analyze_schema() {
    # vacuum analyze only the tables in the specified schema

    # postgres info can be supplied by either passing it as parameters to this
    # function, setting environment variables or a combination of the two
    local pg_schema="${1:-${PG_SCHEMA}}"
    local pg_db="${2:-${PG_DB}}"
    local pg_user="${3:-${PG_USER}}"
    local pg_host="${4:-${PG_HOST}}"

    echo "Vacuuming schema \`${pg_schema}\`:"

    # extract schema table names from psql output and put them in a bash array
    local psql_tbls="\dt ${pg_schema}.*"
    local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*/\1/p"
    local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}"  | sed -nr "${sed_str}" )
    local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )

    # loop through the table names creating and executing a vacuum
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" \
            -c "VACUUM (ANALYZE) ${pg_schema}.${t};"
    done
}

Source: stackoverflow.com

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 other schemas in the database.

To show the current search path you can use the following command:

SHOW search_path;
And to put the new schema in the path, you could use:

SET search_path TO myschema;
Or if you want multiple schemas:

SET search_path TO myschema, public;
Reference: https://www.postgresql.org/docs/current/static/ddl-schemas.html

Source: stackoverflow.com

Psql commande \gset

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset result_
=> \echo :result_var1 :result_var2
hello 10

Source: postgresql.org

Similar codes

0 vote108 views1 answer
Python sqlalchemy orm to select null values
SQL
15 votes113 views2 answers
Open postgress in terminal mac
SQL
6 votes101 views5 answers
Mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'
SQL
0 vote153 views1 answer
How to get column name in db from an sqlalchemy attribute model
SQL
1 vote109 views1 answer
SQL date part only of datetime
SQL
4 votes101 views2 answers
Linq join
SQL
0 vote106 views1 answer
Change permission to database mysql
SQL
0 vote134 views1 answer
Ceil upto 2 decimal place mysql
SQL
0 vote103 views1 answer
Sql get date from timestamp
SQL
0 vote116 views1 answer
Sql server beginning of month
SQL