NOT LIKE Behavior with NULL Values

Question

I want to get all columns of a table, except columns of type serial. The closest request to this problem, I was able to come up with the following:

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

But the problem is that it also excludes / filters rows having null values ​​for column_default. I don't know why Postgres behavior looks like this. So I had to change my request 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 best suggestions or justifications for this are welcome.

Answer 1

About null

'anything' NOT LIKE NULLgives NULL, not TRUE.
And only TRUEsuitable for filter expressions in a sentence WHERE.

NULL NULL ( !).
NULL .

, :

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

. .

- . Postgres, search_path, .

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%');

, . , "nextval" serial. , , "" . pg_get_serial_sequence(table_name, column_name) ...

. , . . . . Oracle ...

, Postgres. :

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 1 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) <> ''
   );

, .

:

pg_attrdef . pg_attribute (. ). , ( ) .

'table1'::regclass search_path , . : 'myschema.table1'::regclass. :
,

:
Postgres?

Answer 2

AND column_default NOT LIKE 'nextval%' OR column_default IS NULL

AND NOT column_default LIKE 'nextval%'

Answer 3

, :

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