---
name: "SQL::Translator::Producer::PostgreSQL"
section: "3pm"
platform: "linux"
package: "libsql-translator-perl"
version: "1.66-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/SQL::Translator::Producer::PostgreSQL"
---

# SQL::Translator::Producer::PostgreSQL(3pm)

PostgreSQL producer for SQL::Translator

## NAME

SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator

## SYNOPSIS

```
  my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
  $t->translate;
```

## DESCRIPTION

Creates a DDL suitable for PostgreSQL.  Very heavily based on the Oracle producer.

Now handles PostGIS Geometry and Geography data types on table definitions. Does not yet support PostGIS Views.

### Producer Args

You can change the global behavior of the producer by passing the following options to the `"producer_args"` attribute of `"SQL::Translator"`.

- postgres\_version

  The version of postgres to generate DDL for. Turns on features only available in later versions. The following features are supported

  - IF EXISTS

    If your postgres\_version is higher than 8.003 (I should hope it is by now), then the DDL generated for dropping objects in the database will contain IF EXISTS.

- attach\_comments

  Generates table and column comments via the COMMENT command rather than as a comment in the DDL. You could then look it up with \\dt+ or \\d+ (for tables and columns respectively) in psql. The comment is dollar quoted with `$comment`$ so you can include ' in it. Just to clarify: you get this

  ```
      CREATE TABLE foo ...;
      COMMENT on TABLE foo IS $comment$hi there$comment$;
  ```

  instead of this

  ```
      -- comment
      CREAT TABLE foo ...;
  ```

### Extra args

Various schema types support various options via the `"extra"` attribute.

- Tables

  - temporary

    Produces a temporary table.

- Views

  - temporary

    Produces a temporary view.

  - materialized

    Produces a materialized view.

- Fields

  - list, custom\_type\_name

    For enum types, list is the list of valid values, and custom\_type\_name is the name that the type should have. Defaults to `$table_`$field\_type.

  - geometry\_type, srid, dimensions, geography\_type

    Fields for use with PostGIS types.

## PostgreSQL Create Table Syntax

```
  CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
      { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
      | table_constraint }  [, ... ]
  )
  [ INHERITS ( parent_table [, ... ] ) ]
  [ WITH OIDS | WITHOUT OIDS ]
```

where column\_constraint is:

```
  [ CONSTRAINT constraint_name ]
  { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
    CHECK (expression) |
    REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
      [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
```

and table\_constraint is:

```
  [ CONSTRAINT constraint_name ]
  { UNIQUE ( column_name [, ... ] ) |
    PRIMARY KEY ( column_name [, ... ] ) |
    CHECK ( expression ) |
    EXCLUDE [USING acc_method] (expression) [INCLUDE (column [, ...])] [WHERE (predicate)]
    FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
      [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
```

## Create Index Syntax

```
  CREATE [ UNIQUE ] INDEX index_name ON table
      [ USING acc_method ] ( column [ ops_name ] [, ...] )
      [ INCLUDE  ( column [, ...] ) ]
      [ WHERE predicate ]
  CREATE [ UNIQUE ] INDEX index_name ON table
      [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
      [ WHERE predicate ]
```

## SEE ALSO

SQL::Translator, SQL::Translator::Producer::Oracle.

## AUTHOR

Ken Youens-Clark \<kclark@cpan.org\>.