Behaviour of NOT LIKE with NULL values

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_nameFROM   information_schema.columnsWHERE  table_name = 'hstore1'AND    table_schema = 'public'   -- your schemaAND   (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 aWHERE  attrelid = 'table1'::regclassAND    NOT attisdropped   -- no dropped (dead) columnsAND    attnum > 0         -- no system columnsAND   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?

I think you can use :

SELECT column_name *FROM* information_schema.columnsWHERE table_name = 'table1'AND ( nvl(column_default,0) *NOT LIKE* 'nextval%');