Skip to content

Postgresql

Data Types

Data Type Range Storage Size
CHAR(size) Fixed length, 1 to 1,024 chars 1 to 1,024 bytes
VARCHAR(size) Variable length, 1 to 65,535 chars 1 to 65,535 bytes
TEXT Up to 1 GB of text Up to 1 GB
BYTEA Up to 1 GB of binary data Up to 1 GB
ENUM Enumerated list of values Depends on values
ARRAY Array of any data type Depends on the array size
Data Type Range Storage Size
SMALLINT -32,768 to 32,767 2 bytes
INT, INTEGER -2,147,483,648 to 2,147,483,647 4 bytes
BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 bytes
NUMERIC(p, s) Depends on precision (p) and scale (s) Varies
DECIMAL(p, s) Depends on precision (p) and scale (s) Varies
REAL -3.4028235E+38 to 3.4028235E+38 4 bytes
DOUBLE PRECISION -1.7976931348623157E+308 to 1.7976931348623157E+308 8 bytes
SERIAL Auto-incrementing integer (equivalent to INTEGER) 4 bytes
BIGSERIAL Auto-incrementing large integer (equivalent to BIGINT) 8 bytes
SMALLSERIAL Auto-incrementing small integer (equivalent to SMALLINT) 2 bytes
Data Type Range Storage Size
DATE 4713-01-01 (BC) to 5874897-12-31 (AD) 4 bytes
TIMESTAMP 4713-01-01 00:00:00 (BC) to 294276-12-31 23:59:59 (AD) 8 bytes
TIMESTAMPTZ 4713-01-01 00:00:00 (BC) to 294276-12-31 23:59:59 (AD) 8 bytes
TIME 00:00:00 to 24:00:00 8 bytes
INTERVAL Varies, up to 178000 years 16 bytes
Data Type Range Storage Size
BOOLEAN TRUE or FALSE 1 byte

Full Data Types

Export/Import

psql -d <db_name> -f <file_name.sql>

pg_dump -U <db_user> <db_name> > <file_name>.sql

Shell

psql

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

\x

User

\du

CREATE USER <username>

DROP USER <username>

ALTER USER <username> WITH PASSWORD '<new_password>'

Database

\l

CREATE DATABASE <db_name>

DROP DATABASE <db_name>

ALTER DATABASE <db_name> OWNER TO <username>

\c <db_name>

Tables

\dt

\d+ <table_name>

# Simple
CREATE TABLE <table_name>

# Based on other table
CREATE TABLE <table_name> (LIKE <other_table_name> INCLUDING ALL);

DROP TABLE <table_name>

Row

UPDATE <table_name> SET <column1>=<value>

# Simple
INSERT INTO <table_name> (column1, column2) VALUES (value1, value2)

# Based on other table row
INSERT INTO <table_name> SELECT * FROM <other_table_name>

DELETE FROM <table_name> WHERE <column1>=<value>

Column

# For creating new column need to temporarily disable all connections to table
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> [NOT NULL] [DEFAULT <value>]

SELECT column_name, data_type FROM information_schema.columns WHERE table_name='<table_name>'

ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP NOT NULL

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET NOT NULL

Index

CREATE [UNIQUE] INDEX <index_name> ON <table> (<column1, column2>) WHERE <conditions>

Other

  • Version:

    SHOW server_version

  • Connection URL:

    postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]