PostgreSQL TABLESAMPLE (Sampling)

PostgreSQL TABLESAMPLE (Sampling) is a SQL statement in the PostgreSQL Specific category. PostgreSQL: returns a random sample of rows. SYSTEM is faster but less random than BERNOULLI. The syntax is SELECT * FROM table TABLESAMPLE SYSTEM(percentage);. It returns random sample. A typical example: SELECT * FROM employees TABLESAMPLE SYSTEM(10); -- Approximately 10% of pages SELECT * FROM employees TABLESAMPLE BERNOULLI(5); -- Approximately 5% of rows (more random, slower) -- Repeatable sampling: SELECT * FROM employees TABLESAMPLE SYSTEM(10) REPEATABLE(42); -- Same seed (42) produces the same sample each time -- Use cases: -- Quick data exploration -- Testing queries on large datasets -- Statistical sampling -- Approximate aggregations -- Limitations: -- Not available in… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).