Greenplum CheatSheet

Getting Started

Switch and connect

sudo -u postgres psql

List all databases

postgres=# \l

Connect to the database named postgres

postgres=# \c postgres

Disconnect

postgres=# \q
postgres=# \!

Getting help

\h Help on syntax of SQL commands

\h DELETE DELETE SQL statement syntax

\? List of PostgreSQL command

PSQL commands

Option	Example	Description
`[-d] <database>`	psql -d mydb	Connecting to database
`-U`	psql -U john mydb	Connecting as a specific user
`-h` `-p`	psql -h localhost -p 5432 mydb	Connecting to a host/port
`-U` `-h` `-p` `-d`	psql -U admin -h 192.168.1.5 -p 2506 -d mydb	Connect remote PostgreSQL
`-W`	psql -W mydb	Force password
`-c`	psql -c '\c postgres' -c '\dt'	Execute a SQL query or command
`-H`	psql -c "\l+" -H postgres > database.html	Generate HTML report
`-l`	psql -l	List all databases
`-f`	psql mydb -f file.sql	Execute commands from a file
`-V`	psql -V	Print the psql version

Database

Show version

SHOW SERVER_VERSION;

Show system status

\conninfo

Show environmental variables

SHOW ALL;

List users

SELECT rolname FROM pg_roles;

Show current user

SELECT current_user;

Show current user's permissions

\du

Show current database

SELECT current_database();

Show all tables in database

\dt

List functions

\df schema

Tables

List tables, in current db

\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

List tables, globally

\dt *.*.
SELECT * FROM pg_catalog.pg_tables

List table schema

\d table_name
\d+ table_name

SELECT column_name, data_type, character_maximum_length
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE table_name = 'table_name';

Create table

CREATE TABLE table_name(
                  column_name column_type,
                  column_name column_type
                );

Create table, with an auto-incrementing primary key

CREATE TABLE table_name (
                  column_name SERIAL PRIMARY KEY
                );

Delete table

DROP TABLE IF EXISTS table_name CASCADE;

Permissions

Become the postgres user, if you have permission errors

sudo su - postgres
psql

Grant all permissions on database

GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name;

Grant connection permissions on database

GRANT CONNECT ON DATABASE db_name TO user_name;

Grant permissions on schema

GRANT USAGE ON SCHEMA public TO user_name;

Grant permissions to functions

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user_name;

Grant permissions to select, update, insert, delete, on a all tables

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO user_name;

Grant permissions, on a table

GRANT SELECT, UPDATE, INSERT ON table_name TO user_name;

Grant permissions, to select, on a table

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;

Columns

Add column

ALTER TABLE table_name IF EXISTS
                  ADD column_name data_type [constraints];

Update column

ALTER TABLE table_name IF EXISTS
                  ALTER column_name TYPE data_type [constraints];

Delete column

ALTER TABLE table_name IF EXISTS
                  DROP column_name;

Update column to be an auto-incrementing primary key

ALTER TABLE table_name
                  ADD COLUMN column_name SERIAL PRIMARY KEY;

Insert into a table, with an auto-incrementing primary key

INSERT INTO table_name
                  VALUES (DEFAULT, value1);

INSERT INTO table_name (column1_name,column2_name)
                  VALUES ( value1,value2 );

Data

Select all data

SELECT * FROM table_name;

Read one row of data

SELECT * FROM table_name LIMIT 1;

Search for data

SELECT * FROM table_name WHERE column_name = value;

Insert data

INSERT INTO table_name VALUES( value_1, value_2 );

Update data

UPDATE table_name
                  SET column_1 = value_1, column_2 = value_2
                  WHERE column_1 = value;

Delete all data

DELETE FROM table_name;

Delete specific data

DELETE FROM table_name
                  WHERE column_name = value;

Users

List roles

SELECT rolname FROM pg_roles;

Create user

CREATE USER user_name WITH PASSWORD 'password';

Drop user

DROP USER IF EXISTS user_name;

Alter user password

ALTER ROLE user_name WITH PASSWORD 'password';

Schema

List schemas

\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;

Create schema

CREATE SCHEMA IF NOT EXISTS schema_name;

Drop schema

DROP SCHEMA IF EXISTS schema_name CASCADE;

Dates

Show current date YYYY-MM-DD

SELECT current_date;

Calculate age between two dates

SELECT age(timestamp, timestamp);

Show current time with time zone

SELECT current_time;

Make dates using integers

SELECT make_date(2021,03,25);