Behaviour of NOT LIKE with NULL values

Question

I want to fetch all columns of a table except of columns of type serial. The closest query to this problem I was able to come up with this one:

SELECT column_name FROM information_schema.columns
WHERE table_name = 'table1' AND column_default NOT LIKE 'nextval%'

But the problem is its also excluding/filtering rows having empty values for column_default.I don't know why the behaviour of Postgres is like this. So I had to change my query to something like this:

SELECT column_name FROM information_schema.columns
WHERE table_name = 'table1'
AND ( column_default IS NULL OR column_default NOT LIKE 'nextval%')

Any better suggestions or rationale behind this are welcome.

Solution

About NULL

'anything' NOT LIKE NULL yields NULL, not TRUE.
And only TRUE qualifies for filter expressions in a WHERE clause.

Most functions return NULL on NULL input (there are exceptions). That's the nature of NULL in any proper RDBMS.

If you desire a single expression, you could use:

AND   (column_default LIKE 'nextval%')  IS NOT TRUE;

That's hardly shorter or faster, though. Details in the manual.

Proper query

Your query is still unreliable. A table name alone is not unique in a Postgres database, you need to specify the schema name in addition or rely on the current search_path to find the first match in it:

Related:

• How does the search_path influence identifier resolution and the "current schema"

SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'hstore1'
AND    table_schema = 'public'   -- your schema!
AND   (column_default IS NULL OR
       column_default NOT LIKE 'nextval%');

Better, but still not bullet-proof. A column default starting with 'nextval' does not make a serial, yet. See:

• Auto increment table column

To be sure, check whether the sequence in use is "owned" by the column with pg_get_serial_sequence(table_name, column_name).

I rarely use the information schema myself. Those slow, bloated views guarantee portability across major versions - and aim at portability to other standard-compliant RDBMS. But too much is incompatible anyway. Oracle does not even implement the information schema (as of 2015).

Also, useful Postgres-specific columns are missing in the information schema. For this case I might query the the system catalogs like this:

SELECT *
FROM   pg_catalog.pg_attribute a
WHERE  attrelid = 'table1'::regclass
AND    NOT attisdropped   -- no dropped (dead) columns
AND    attnum > 0         -- no system columns
AND    NOT EXISTS (
   SELECT FROM pg_catalog.pg_attrdef d
   WHERE  (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
   AND    d.adsrc LIKE 'nextval%'
   AND    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) <> ''
   );

Faster and more reliable, but less portable.

The manual:

The catalog pg_attrdef stores column default values. The main information about columns is stored in pg_attribute (see below). Only columns that explicitly specify a default value (when the table is created or the column is added) will have an entry here.

'table1'::regclass uses the search_path to resolve the name, which avoids ambiguity. You can schema-qualify the name to overrule: 'myschema.table1'::regclass.

Related:

• Find the referenced table name using table, field and schema name
• Get the default values of table columns in Postgres?

Comments

[Sam DeHaan]

Comparisons with NULL not using IS NULL or IS NOT NULL return NULL instead of true or false in most/all database systems.

[Mattias Lindberg]

Please add some explaination to your answer!

[jgaw]

nvl/coalesce function will give a value for column_default field to make it not null. this seems to work and is easier to manage than adding additional where clause parameters.

[Tom Lint]

I really wish they'd change the standard to make sense. NULL means the absence of a value, meaning that LIKE and NOT LIKE should actually evaluate to FALSE and TRUE, respectively, when dealing with NULL values, and not the nonsensical NULL.

[Erwin Brandstetter]

NULL in SQL is supposed to mean "unknown" rather than "no value". So it's treated like it could be anything, and we don't know whether it matches or not. Consequently, the result is also NULL.

[Tom Lint]

The problem is, there is no such thing as "unknown". Only a value or its absence, the latter of which NULL is used for. "unknown" only exists in the abstract.