In PostgreSQL, a database is a collection of schemas, and each schema contains tables, views, functions, and other objects. The default schema is public. Creating a Database Use CREATE DATABASE to create a new database. You can specify encoding, locale, and template: Creating Tables Tables are defined with CREATE TABLE. PostgreSQL supports a rich set of data types: Category Types Numeric INTEGER, BIGINT, SERIAL, NUMERIC, REAL, DOUBLE PRECISION Text VARCHAR(n), TEXT, CHAR(n) Boolean BOOLEAN Date/Time DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL JSON JSON, JSONB Other UUID, BYTEA, ARRAY, INET, CIDR, MONEY Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT enforce data integrity at the database level. SERIAL vs IDENTITY PostgreSQL offers both SERIAL (legacy) and…
Key Takeaways
Use GENERATED ALWAYS AS IDENTITY instead of SERIAL for new projects.
PostgreSQL supports arrays, JSONB, UUID, INET, and other advanced types.
Schemas help organize objects; the default schema is public.
Always add indexes on foreign keys and frequently filtered columns.