FilesExpand file tree

 master

/

postgres_columns_null.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

62 lines (59 loc) · 1.17 KB

 master

/

postgres_columns_null.sql

Copy path

Top

File metadata and controls

• Code
• Blame

62 lines (59 loc) · 1.17 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

--

-- Author: Hari Sekhon

-- Date: 2020-10-20 11:01:44 +0100 (Tue, 20 Oct 2020)

--

-- vim:ts=4:sts=4:sw=4:et:filetype=sql

--

-- https://github.com/HariSekhon/SQL-scripts

--

-- License: see accompanying Hari Sekhon LICENSE file

--

-- If you're using my code you're welcome to connect with me on LinkedIn and optionally send me feedback to help steer this or other code I publish

--

-- https://www.linkedin.com/in/HariSekhon

--

-- Finds PostgreSQL columns that contain only NULLs

--

-- Tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.0

SELECT

nspname,

relname,

attname,

typname,

(stanullfrac*100)::int AS null_percent

FROM

pg_class c

JOIN

pg_namespace ns

ON

(ns.oid=relnamespace)

JOIN

pg_attribute

ON

(c.oid=attrelid)

JOIN

pg_type t

ON

(t.oid=atttypid)

JOIN

pg_statistic

ON

(c.oid=starelid AND staattnum=attnum)

WHERE

(stanullfrac*100)::int = 100

AND

nspname NOT LIKE E'pg\\_%'

AND

nspname != 'information_schema'

AND

relkind = 'r'

AND

NOT attisdropped

AND

attstattarget != 0

-- AND

--reltuples >= 100

ORDER BY

nspname,

relname,

attname;