33

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
kushi
  • 389
  • 1
  • 5
  • 11
  • Comparisons with NULL not using `IS NULL` or `IS NOT NULL` return `NULL` instead of true or false in most/all database systems. – Sam DeHaan Apr 02 '14 at 17:39

2 Answers2

52

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:

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 5
    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. – Tom Lint Feb 15 '19 at 11:06
  • `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`. – Erwin Brandstetter Jun 23 '22 at 16:15
  • 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. – Tom Lint Jun 26 '22 at 13:28
-2

I think you can use :

SELECT column_name *FROM* information_schema.columns
WHERE table_name = 'table1'
AND ( nvl(column_default,0) *NOT LIKE* 'nextval%');
Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42
satish pattar
  • 1
  • Please add some explaination to your answer! – Mattias Lindberg Jan 15 '16 at 09:06
  • 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. – jgaw Nov 23 '16 at 00:56