SQL: Postgres common commands

# Log into the server as root
sudo -i -u postgres
psql

or

# Connect to a database 
psql -U toxlab -d toxrefdb_3_0

-- List all databases
\l

-- List all users
\du

-- Display information about the current database connection
\conninfo	

-- Connect to a different database. Example: \c postgres
\c dbname	

-- List the schemas 
toxrefdb_3_0=> \dn

-- List the tables in the current (public) schema
toxrefdb_3_0=> \dt

-- Get the structure of the 'studies' table
toxrefdb_3_0=> \d studies

-- List all schemas in the current database
SELECT schema_name FROM information_schema.schemata;

-- List all tables in the 'public' schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- List all columns for a specific table
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'studies';

-- A simple query to see a few rows from a table
SELECT * FROM studies LIMIT 10;

-- Get the total number of rows in a table
SELECT COUNT(*) FROM studies;