Index of every page on this site: llms.txt
All skills
jyasuu avatar

/postgresql

@d3e2684
Run it yourself

PostgreSQL database operations including backups, transactions, user management, window functions, materialized views, and extensions.

  • 1 file
  • 3.1 KB
  • Updated 10 months ago
  • GitHub
Use this Skill: https://skilld.dev/gh/jyasuu/cheat-sheet/postgresql

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈37 tokens for metadata: the name and description. ≈762 when used: this file.

Description uses 1.8% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈147 characters, or 1.8%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

PostgreSQL — Advanced Operations

Connection

psql -U username -d dbname -h host

Backups

pg_dump dbname > backup.sql
pg_restore -d dbname backup.sql
pg_dump -v -d 'postgres://postgres:postgres@localhost:5432/a' -n a -Fd -j 5 -f /backup/backup -t 'a.(test)' --no-acl --no-owner
pg_restore -v -d 'postgres://postgres:postgres@localhost:5432/b' -Fd -j5 /backup/backup --no-acl  --no-owner --disable-triggers

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

User Management

CREATE USER username WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

PostgreSQL — Advanced Features

Window Functions

SELECT department, employee, salary,
       AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

Materialized Views

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product, SUM(quantity) FROM sales GROUP BY product;
REFRESH MATERIALIZED VIEW sales_summary;

Extensions

CREATE EXTENSION pg_trgm;  -- Fuzzy string matching
CREATE EXTENSION postgis;   -- Geospatial support

Check Long-Running Queries

SELECT now() - query_start, *
FROM pg_catalog.pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minute';

Table Disk Size


SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Others

\x -- format display

\d
\d+
\dt
\d+ <table_name>
\dn

SELECT jsonb_pretty(json_column::jsonb) FROM your_table;

SELECT *
FROM my_table
WHERE my_column ~ '[\u0009-\u000D\u00A0\u1680\u2000-\u200A\u2028\u2029\u202F\u205F\u3000]';
SELECT * FROM users WHERE name LIKE 'Leo%';
SELECT * FROM users WHERE name ILIKE 'leo%';
SELECT * FROM users WHERE name SIMILAR TO '(Leo|Luke)%';
SELECT * FROM logs WHERE message ~ 'error|fail';
SELECT * FROM logs WHERE message ~* 'error|fail';
SELECT * FROM logs WHERE message !~ 'success';
SELECT * FROM products WHERE tags @> ARRAY['electronics'];
SELECT * FROM products WHERE ARRAY['electronics'] <@ tags;
SELECT * FROM products WHERE tags && ARRAY['electronics', 'home'];
SELECT data->'name' FROM users;
SELECT data->>'name' FROM users;
SELECT data#>'{address,city}' FROM users;
SELECT * FROM events WHERE timerange @> now();
SELECT * FROM events WHERE timerange && '[2025-01-01,2025-12-31)'::tstzrange;
SELECT * FROM table1 WHERE col1 IS DISTINCT FROM col2;
SELECT * FROM users WHERE role IN ('admin', 'user');

set search_path=public;

create unlogged table unlogged_table (
  id varchar(10) not null
);
alter table
  unlogged_table setlogged;



ALTER TABLE a.test SET SCHEMA b;

Source: SKILL.md on GitHub

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at d3e2684. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub last month.

Steadyupdated 10 months ago

README badge

README badge for jyasuu/cheat-sheet/postgresql