postgresql.md

Postgresql

Accessing Postgresql cli inside a docker container

Source

• Starting docker docker-compose up --build
• Find docker container name of postgres container: docker ps --format '{{.Names}}' | grep postgres
• Enter postgres container docker exec -it postgres-container psql -U postgres
• OR EASYER: docker exec -it $(docker ps --format '{{.Names}}' | grep postgres) psql -U postgres
• Connect \c <database_name>
• List tables \d
• List table \d+ <table_name>
• Exit \q

Syntax

Insert or Update (Upsert)

Use ON CONFLICT ("rownameOfConflictingKey") DO UPDATE SET "row" = EXCLUDED."row"

Example

INSERT INTO goal ("groupLevel", "desc")
      VALUES (?, ?)
      ON CONFLICT ("groupLevel")
      DO UPDATE SET "desc" = EXCLUDED."desc"

Delete

Ordinary delete:

DELETE FROM "table"
WHERE "table"."id" = ? AND …

With a joined table:

DELETE FROM "table"
USING "another_table"
WHERE "table"."id" = "another_table"."id" AND …

UPDATE

UPDATE "table"
SET "column1" = value1,
    "column2" = value2 ,...
WHERE
   condition;

Kill hanging Query

SELECT pid, query FROM pg_stat_activity WHERE state = 'active';

SELECT pg_cancel_backend(PID);
--OR
SELECT pg_terminate_backend(PID);

Fully Optional Migration

-- helper function that performs migration
CREATE OR REPLACE FUNCTION __tmp_migration() RETURNS BOOLEAN AS
$BODY$ 
BEGIN

IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = '<my table to check>')
THEN
      <migration>
RETURN 'true';
END IF;
RETURN 'false';
END;
$BODY$
LANGUAGE plpgsql;

-- execute function and cleanup
SELECT __tmp_migration();
DROP FUNCTION __tmp_migration;