Skip to content

Latest commit

 

History

History
110 lines (79 loc) · 4.78 KB

columns.md

File metadata and controls

110 lines (79 loc) · 4.78 KB
Raw

Column Operations

Column Definitions

The createTable and addColumns methods both take a columns argument that specifies column names and options. It is a object (key/value) where each key is the name of the column, and the value is another object that defines the options for the column.

  • type [string] - data type (use normal postgres types)
  • collation [string] - collation of data type
  • unique [boolean] - set to true to add a unique constraint on this column
  • primaryKey [boolean] - set to true to make this column the primary key
  • notNull [boolean] - set to true to make this column not null
  • default [string] - adds DEFAULT clause for column. Accepts null, a literal value, or a pgm.func() expression.
  • check [string] - sql for a check constraint for this column
  • references [Name or string] - a table name that this column is a foreign key to
  • referencesConstraintName [string] - name of the created constraint
  • referencesConstraintComment [string] - comment on the created constraint
  • onDelete [string] - adds ON DELETE constraint for a reference column
  • onUpdate [string] - adds ON UPDATE constraint for a reference column
  • match [string] - FULL or SIMPLE
  • deferrable [boolean] - flag for deferrable column constraint
  • deferred [boolean] - flag for initially deferred deferrable column constraint
  • comment [string] - adds comment on column
  • generated [object] - creates identity column

Data types & Convenience Shorthand

Data type strings will be passed through directly to postgres, so write types as you would if you were writing the queries by hand.

There are some aliases on types to make things more foolproof: (int, string, float, double, datetime, bool)

There is a shorthand to pass only the type instead of an options object: pgm.addColumns('myTable', { age: 'integer' }); is equivalent to pgm.addColumns('myTable', { age: { type: 'integer' } });

There is a shorthand for normal auto-increment IDs: pgm.addColumns('myTable', { id: 'id' }); is equivalent to pgm.addColumns('myTable', { id: { type: 'serial', primaryKey: true } });

Methods

pgm.addColumns( tablename, new_columns, options )

Add columns to an existing table - postgres docs

Arguments:

  • tablename [Name] - name of the table to alter
  • new_columns [object] - column names / options -- see column definitions section
  • options [object] - options:
    • ifNotExists [boolean] adds column only if it does not exist

Aliases: addColumn Reverse Operation: dropColumns

pgm.dropColumns( tablename, columns, options )

Drop columns from a table - postgres docs

Arguments:

  • tablename [Name] - name of the table to alter
  • columns [array of strings or object] - columns to drop (if object, uses keys)
  • options [object] - options:
    • ifExists [boolean] - drops column only if it exists
    • cascade [boolean] - drops also dependent objects

Aliases: dropColumn

pgm.renameColumn( tablename, old_column_name, new_column_name )

Rename a column - postgres docs

Arguments:

  • tablename [Name] - name of the table to alter
  • old_column_name [string] - current column name
  • new_column_name [string] - new column name

Reverse Operation: same operation in opposite direction

pgm.alterColumn( tablename, column_name, column_options )

Alter a column (default value, type, allow null) - postgres docs

Arguments:

  • tablename [Name] - name of the table to alter
  • column_name [string] - column to alter
  • column_options [object] - optional new column options
    • default [string or null] - null, string
    • type [string] - new datatype
    • notNull [boolean] - sets NOT NULL if true or NULL if false
    • allowNull [boolean] - sets NULL if true (alternative to notNull)
    • using [string] - adds USING clause to change values in column
    • collation [string] - adds COLLATE clause to change values in column
    • comment [string] - adds comment on column
    • generated [object or null] - sets or drops identity column