db

The DB you want to connect to is on a remote server and cannot be accessed directly from the outside, but you do have ssh access to the remote server.

Solution

First, open a terminal window and run the following command (keep it open while you want to maintain the connection to the DB):

ssh -L localhost:<local_port>:<remote_db_host>:<remote_db_port> <remote_ssh_user>@<remote_ssh_host> [-i <permission_file>.pem]

Then, you connect to the DB at localhost:<local_port> and the remote DB credentials.

This doesn’t work:

ALTER TABLE company DROP CONSTRAINT UQ_924dc2ee53aa15f1b16b4af12be;

Postgres says that this constraint doesn’t exist.

Solution

The problem is that Postgres will lowercase the constraint name. Use double quotes around the constraint name, like this:

ALTER TABLE company DROP CONSTRAINT "UQ_924dc2ee53aa15f1b16b4af12be";

Solution

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'prefix%' AND TABLE_SCHEMA = 'my_database'
INTO outfile '/tmp/drop_my_tables.sql';

SOURCE /tmp/drop_my_tables.sql;

Solution

mysqldump -u root -p pass my_database my_table --where="id IN (100, 101)" > my_table_dump.sql

Solution

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%name%'
    AND TABLE_SCHEMA = 'my_database';

Solution

mysql -u user -p pass -B -e "SELECT * FROM blog_post WHERE title LIKE '%MySQL%'" > output_file.csv

Solution

SELECT
    table_schema as `Database`,
    table_name AS `Table`, 
    ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Solution

Add SQL_NO_CACHE right after SELECT in any query. E.g.:

SELECT SQL_NO_CACHE * FROM post WHERE post.title LIKE '%MySQL%'

When you want to avoid NULL for the result of JSONB_AGG and instead you want to get an empty array.

Solution

Use COALESCE and '[]'::JSONB.

SELECT
   COALESCE(
      JSONB_AGG(
        JSONB_BUILD_OBJECT(
          'id', role.id,
          'name', role.name
        )
      ) FILTER (WHERE role.id IS NOT NULL),
      '[]'::JSONB
    ) AS "adminRoles",
FROM ...