PostgreSQL (SQL) vs pandas vs PySpark.
Read / Inspect
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Preview rows | SELECT * FROM t LIMIT 5; | df.head(5) | df.show(5) |
| Row count | SELECT count(*) FROM t; | len(df) | df.count() |
| Columns/schema | \d t | df.dtypes | df.printSchema() |
| Distinct | SELECT DISTINCT a FROM t; | df.a.drop_duplicates() | df.select(“a”).distinct() |
Select / Filter
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Select cols | SELECT a, b FROM t; | df[[“a”,”b”]] | df.select(“a”,”b”) |
| Filter | WHERE age > 30 | df[df.age > 30] | df.filter(F.col(“age”) > 30) |
| Multi-condition | WHERE a > 1 AND b < 5 | df[(df.a>1) & (df.b<5)] | df.filter((F.col(“a”)>1) & (F.col(“b”)<5)) |
| In list | WHERE c IN (1,2) | df[df.c.isin([1,2])] | df.filter(F.col(“c”).isin(1,2)) |
Columns / Expressions
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| New column | SELECT a + b AS c | df[“c”] = df.a + df.b | df.withColumn(“c”, F.col(“a”)+F.col(“b”)) |
| Rename | SELECT a AS x | df.rename(columns={“a”:”x”}) | df.withColumnRenamed(“a”,”x”) |
| Cast | CAST(a AS INT) | df.a.astype(“int”) | F.col(“a”).cast(“int”) |
| Conditional | CASE WHEN a>0 THEN ‘pos’ ELSE ‘neg’ END | np.where(df.a>0,”pos”,”neg”) | F.when(F.col(“a”)>0,”pos”).otherwise(“neg”) |
Aggregate / Group
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Group + sum | SELECT k, sum(v) FROM t GROUP BY k; | df.groupby(“k”).v.sum() | df.groupBy(“k”).agg(F.sum(“v”)) |
| Count per group | SELECT k, count(*) GROUP BY k; | df.groupby(“k”).size() | df.groupBy(“k”).count() |
| Mean | SELECT avg(v) FROM t; | df.v.mean() | df.agg(F.mean(“v”)) |
Join / Combine
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Left join | a LEFT JOIN b USING(id) | a.merge(b, on=”id”, how=”left”) | a.join(b, on=”id”, how=”left”) |
| Stack rows | SELECT * FROM a UNION ALL SELECT * FROM b | pd.concat([a,b]) | a.union(b) |
Sort / Nulls / Output
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Sort desc | ORDER BY a DESC | df.sort_values(“a”, ascending=False) | df.orderBy(F.desc(“a”)) |
| Drop nulls | WHERE a IS NOT NULL | df.dropna() | df.na.drop() |
| Fill nulls | COALESCE(a, 0) | df.fillna(0) | df.na.fill(0) |
PySpark assumes from pyspark.sql import functions as F. Key difference: SQL and pandas execute eagerly; Spark is lazy until an action like .show() or .collect().
Window Functions
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Row number per group | row_number() OVER (PARTITION BY k ORDER BY v DESC) | df.sort_values(“v”,ascending=False).groupby(“k”).cumcount()+1 | F.row_number().over(Window.partitionBy(“k”).orderBy(F.desc(“v”))) |
| Rank | rank() OVER (PARTITION BY k ORDER BY v) | df.groupby(“k”).v.rank(method=”min”) |
| Dense rank | dense_rank() OVER (…) | df.groupby(“k”).v.rank(method=”dense”) | F.dense_rank().over(w) |
| Lag | lag(v,1) OVER (ORDER BY t) | df.v.shift(1) | F.lag(“v”,1).over(Window.orderBy(“t”)) |
| Lead | lead(v,1) OVER (ORDER BY t) | df.v.shift(-1) | F.lead(“v”,1).over(Window.orderBy(“t”)) |
| Running total | sum(v) OVER (ORDER BY t) | df.v.cumsum() | F.sum(“v”).over(Window.orderBy(“t”).rowsBetween(Window.unboundedPreceding, 0)) |
| Group sum (no collapse) | sum(v) OVER (PARTITION BY k) | df.groupby(“k”).v.transform(“sum”) | F.sum(“v”).over(Window.partitionBy(“k”)) |
Deduplication
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Drop exact dupes | SELECT DISTINCT * FROM t; | df.drop_duplicates() | df.dropDuplicates() |
| Dupes on subset | DISTINCT ON (a,b) | df.drop_duplicates(subset=[“a”,”b”]) | df.dropDuplicates([“a”,”b”]) |
| Keep latest per key | row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1 | df.sort_values(“ts”).drop_duplicates(“k”, keep=”last”) | filter where row_number().over(w)==1 |
| Count dupes | GROUP BY a HAVING count(*) > 1 | df[df.duplicated(“a”, keep=False)] | df.groupBy(“a”).count().filter(F.col(“count”)>1) |
The “keep latest per key” pattern in Spark, fully written:
python
w = Window.partitionBy("k").orderBy(F.desc("ts"))
df.withColumn("rn", F.row_number().over(w)).filter(F.col("rn")==1).drop("rn")Reshape (pivot / melt / explode)
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Pivot | crosstab(…) (tablefunc ext) | df.pivot_table(index=”k”, columns=”c”, values=”v”) | df.groupBy(“k”).pivot(“c”).agg(F.sum(“v”)) |
| Melt / unpivot | UNNEST / manual UNION | df.melt(id_vars=”k”) | df.select(“k”, F.expr(“stack(…)”)) |
| Explode array → rows | unnest(arr) | df.explode(“arr”) | df.withColumn(“x”, F.explode(“arr”)) |
| Split string → array | string_to_array(s, ‘,’) | df.s.str.split(“,”) | F.split(F.col(“s”), “,”) |
String / Date manipulation
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Lowercase | lower(s) | df.s.str.lower() | F.lower(“s”) |
| Substring | substring(s,1,3) | df.s.str[0:3] | F.substring(“s”,1,3) |
| Concat | a || b | df.a + df.b | F.concat(“a”,”b”) |
| Replace | replace(s,’x’,’y’) | df.s.str.replace(“x”,”y”) | F.regexp_replace(“s”,”x”,”y”) |
| Parse date | to_date(s,’YYYY-MM-DD’) | pd.to_datetime(df.s) | F.to_date(“s”,”yyyy-MM-dd”) |
| Extract year | extract(year from d) | df.d.dt.year | F.year(“d”) |
| Date diff (days) | d2 – d1 | (df.d2 – df.d1).dt.days | F.datediff(“d2″,”d1”) |
Aggregate edge cases
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Count distinct | count(DISTINCT a) | df.a.nunique() | F.countDistinct(“a”) |
| Conditional count | count(*) FILTER (WHERE a>0) | (df.a>0).sum() | F.sum(F.when(F.col(“a”)>0,1).otherwise(0)) |
| Collect into list | array_agg(v) | df.groupby(“k”).v.apply(list) | F.collect_list(“v”) |
| First/last | first_value/last_value OVER (…) | df.groupby(“k”).v.first() | F.first(“v”) / F.last(“v”) |
Creating a schema / defining types
PostgreSQL — types live in the table definition:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age SMALLINT,
signup DATE,
active BOOLEAN DEFAULT true
);
pandas — schema is a dtype dict, applied at read or after:
dtypes = {“id”:”int32″,”name”:”string”,”age”:”Int16″,”active”:”boolean”}
df = pd.read_csv(“f.csv”, dtype=dtypes, parse_dates=[“signup”])
PySpark — an explicit StructType (preferred over inferSchema for production):
from pyspark.sql.types import (StructType, StructField, IntegerType,
StringType, ShortType, DateType, BooleanType)
schema = StructType([
StructField(“id”, IntegerType(), nullable=False),
StructField(“name”, StringType(), nullable=False),
StructField(“age”, ShortType(), nullable=True),
StructField(“signup”, DateType(), nullable=True),
StructField(“active”, BooleanType(), nullable=True),
])
df = spark.read.csv(“f.csv”, header=True, schema=schema)
Assigning / changing a data type
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Cast a column | CAST(a AS INTEGER) or a::int | df.a.astype(“int32”) | df.withColumn(“a”, F.col(“a”).cast(“int”)) |
| To string | a::text | df.a.astype(“string”) | F.col(“a”).cast(“string”) |
| To date | to_date(a,’YYYY-MM-DD’) | pd.to_datetime(df.a) | F.to_date(“a”,”yyyy-MM-dd”) |
| To numeric (safe) | a::numeric (errors if bad) | pd.to_numeric(df.a, errors=”coerce”) | F.col(“a”).cast(“double”) (bad → null) |
| Alter stored type | ALTER TABLE t ALTER COLUMN a TYPE int; |
Data quality checks
| Check | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Null count per col | count(*) – count(a) | df.a.isna().sum() | df.filter(F.col(“a”).isNull()).count() |
| Duplicate keys | GROUP BY id HAVING count(*)>1 | df.duplicated(“id”).sum() | df.groupBy(“id”).count().filter(F.col(“count”)>1) |
| Out-of-range | WHERE age < 0 OR age > 120 | `df[(df.age<0) | (df.age>120)]` |
| Distinct count | count(DISTINCT a) | df.a.nunique() | df.select(“a”).distinct().count() |
| Pattern / format | a ~ ‘^[0-9]+$’ | df.a.str.match(r”^\d+$”) | F.col(“a”).rlike(“^[0-9]+$”) |
| Cast-failure rows | — | rows where to_numeric(coerce) is null but original wasn’t | same: compare pre/post-cast nulls |
| Enforce at write | CHECK, NOT NULL, UNIQUE constraints | manual assert | manual filter / framework |
A compact null-profile across all columns:
pandas
df.isna().sum()
PySpark
df.select([F.sum(F.col(c).isNull().cast(“int”)).alias(c) for c in df.columns])
Renaming a field:
| Task | PostgreSQL | pandas | PySpark |
|---|---|---|---|
| Rename one column | ALTER TABLE t RENAME COLUMN a TO x; | df.rename(columns={“a”:”x”}) | df.withColumnRenamed(“a”,”x”) |
| Rename several | run multiple RENAME statements | df.rename(columns={“a”:”x”,”b”:”y”}) | chain .withColumnRenamed(…) or use select with aliases |
| Rename in a query (alias only) | SELECT a AS x FROM t; | n/a | df.select(F.col(“a”).alias(“x”)) |
| Rename all at once | — | df.columns = [“x”,”y”,”z”] | df.toDF(“x”,”y”,”z”) |
Rename several in Spark:
python
mapping = {"a":"x", "b":"y"}
for old, new in mapping.items():
df = df.withColumnRenamed(old, new)The structural difference: PostgreSQL enforces schema and quality declaratively (constraints reject bad data on insert), while pandas and Spark are permissive — types are assigned but nothing stops bad values, so quality has to be checked explicitly after load.
Key distinction: the Postgres ALTER TABLE permanently changes the stored table, while SELECT a AS x only relabels in the query output. pandas rename returns a new frame (use inplace=True or reassign), and Spark always returns a new immutable DataFrame.
The big conceptual split stays the same: SQL and pandas evaluate eagerly, while Spark builds a lazy plan — window specs and transformations are just descriptions until an action triggers execution.