---
name: postgresql
description: PostgreSQL-specific features and extensions in Prisma 7
when-to-use: PostgreSQL-specific types, extensions, advanced features
keywords: PostgreSQL, uuid, citext, extension, native types, array
priority: medium
requires: schema.md, field-attributes.types.md
related: typedsql.md, full-text-search.md
---

# PostgreSQL Features

Type definitions in `/plugins/nextjs-expert/skills/prisma-7/references/interfaces/field-attributes.types.md`.

PostgreSQL-specific features in Prisma 7.

## Native Types

See `DatabaseType` in `/plugins/nextjs-expert/skills/prisma-7/references/interfaces/field-attributes.types.md`

```typescript
/**
 * PostgreSQL native types for Prisma fields.
 * @typedef {Object} PostgreSQLNativeTypes
 * @property {string} uuid - PostgreSQL UUID type for identifiers
 * @property {string} varChar - PostgreSQL VARCHAR with length constraint
 * @property {string} text - PostgreSQL TEXT for unlimited length text
 * @property {string} decimal - PostgreSQL DECIMAL(p,s) for precise numbers
 * @property {string} jsonB - PostgreSQL JSONB for queryable JSON
 * @property {string[]} textArray - PostgreSQL TEXT array type
 * @property {string} timestamptz - PostgreSQL TIMESTAMPTZ with timezone
 * @module modules/database/src/interfaces/field-attributes.types
 */
type PostgreSQLNativeTypes = {
  uuid: string;
  varChar: string;
  text: string;
  decimal: string;
  jsonB: string;
  textArray: string[];
  timestamptz: string;
}

/**
 * Product model with PostgreSQL native types.
 * @typedef {Object} Product
 * @property {string} id - UUID primary key generated by database
 * @property {string} name - Product name (VARCHAR)
 * @property {string} description - Detailed description (TEXT)
 * @property {number} price - Price with precision (DECIMAL 10,2)
 * @property {Record<string, unknown>} metadata - Searchable JSON (JSONB)
 * @property {string[]} tags - Array of tags (TEXT[])
 * @property {Date} createdAt - Timestamp with timezone
 * @module modules/database/src/models/product
 */
type Product = {
  id: string;
  name: string;
  description: string;
  price: number;
  metadata: Record<string, unknown>;
  tags: string[];
  createdAt: Date;
}
```

---

## Common PostgreSQL Types

| Prisma Type | PostgreSQL Type | Usage |
|-------------|-----------------|-------|
| `String @db.Uuid` | `uuid` | Unique identifiers |
| `String @db.Text` | `text` | Long text |
| `String @db.VarChar(n)` | `varchar(n)` | Limited text |
| `String @db.Citext` | `citext` | Case-insensitive |
| `Decimal @db.Decimal(p,s)` | `decimal(p,s)` | Money |
| `Json @db.JsonB` | `jsonb` | Searchable JSON |
| `String[]` | `text[]` | Arrays |
| `DateTime @db.Timestamptz` | `timestamptz` | With timezone |

---

## PostgreSQL Extensions

```typescript
/**
 * PostgreSQL extension configuration in Prisma schema.
 * @typedef {Object} ExtensionConfig
 * @property {string} uuidOssp - UUID generation (uuid-ossp extension)
 * @property {string} citext - Case-insensitive text type
 * @property {string} pgcrypto - Cryptographic functions
 * @module modules/database/src/schema
 */
type ExtensionConfig = {
  uuidOssp: string;
  citext: string;
  pgcrypto: string;
}

/**
 * Initialize PostgreSQL extensions.
 * Must be enabled in generator config with previewFeatures = ["postgresqlExtensions"]
 * @returns {ExtensionConfig} Configured extensions
 * @module modules/database/src/schema
 */
function initPostgreSQLExtensions(): ExtensionConfig {
  // Enable in datasource block:
  // extensions = [uuidOssp(map: "uuid-ossp"), citext, pgcrypto]
  return {
    uuidOssp: 'uuid-ossp',
    citext: 'citext',
    pgcrypto: 'pgcrypto',
  }
}
```

Create in migrations:

```sql
-- prisma/migrations/xxx/migration.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "citext";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
```

---

## UUID Generation

```typescript
/**
 * PostgreSQL UUID generation strategies.
 * See `IdentityField` in /plugins/nextjs-expert/skills/prisma-7/references/interfaces/field-attributes.types.md
 * @typedef {Object} UUIDGenerationStrategy
 * @property {string} native - gen_random_uuid() - built-in PostgreSQL
 * @property {string} ossp - uuid_generate_v4() - from uuid-ossp extension
 * @module modules/database/src/interfaces/field-attributes.types
 */
type UUIDGenerationStrategy = {
  native: string;
  ossp: string;
}

/**
 * User model with native UUID primary key.
 * Uses PostgreSQL gen_random_uuid() for ID generation.
 * @typedef {Object} User
 * @property {string} id - UUID primary key (gen_random_uuid)
 * @property {string} email - Unique email address
 * @property {string} name - User name
 * @module modules/database/src/models/user
 */
type User = {
  id: string; // gen_random_uuid()
  email: string;
  name: string;
}

/**
 * Alternative User with uuid-ossp extension.
 * Uses uuid_generate_v4() from uuid-ossp extension.
 * Requires: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
 * @typedef {Object} UserWithOSCP
 * @property {string} id - UUID primary key (uuid_generate_v4)
 * @module modules/database/src/models/user
 */
type UserWithOSCP = User & {
  // id generated with uuid_generate_v4()
}
```

---

## Case-Insensitive Text

```typescript
/**
 * PostgreSQL case-insensitive text (citext) for case-insensitive fields.
 * See `DatabaseType` in /plugins/nextjs-expert/skills/prisma-7/references/interfaces/field-attributes.types.md
 * @typedef {Object} UserWithCitext
 * @property {string} id - Primary key
 * @property {string} email - Case-insensitive email using citext
 * @module modules/database/src/models/user
 */
type UserWithCitext = {
  id: string;
  email: string; // Stored as citext - case-insensitive
}

/**
 * Find user by email - case-insensitive matching.
 * Both queries return the same user due to citext field type.
 * @param {string} email - User email (any case)
 * @returns {Promise<UserWithCitext | null>} User record
 * @module modules/database/src/services/users
 */
export async function findUserByEmail(
  email: string
): Promise<UserWithCitext | null> {
  // Both these queries find the same user:
  const user1 = await prisma.user.findUnique({
    where: { email: 'JOHN@EXAMPLE.COM' },
  })
  const user2 = await prisma.user.findUnique({
    where: { email: 'john@example.com' },
  })
  // user1 === user2 (same record)
  return user1
}
```

---

```typescript
/**
 * PostgreSQL array field operations with Prisma.
 * See `DatabaseType` in /plugins/nextjs-expert/skills/prisma-7/references/interfaces/field-attributes.types.md
 * @typedef {Object} PostWithArrays
 * @property {string} id - Primary key
 * @property {string[]} tags - Array of tags (PostgreSQL TEXT[])
 * @module modules/database/src/models/post
 */
type PostWithArrays = {
  id: string;
  tags: string[];
}

/**
 * Find posts containing specific tag.
 * Array filtering using PostgreSQL has() operator.
 * @param {string} tag - Tag to search for
 * @returns {Promise<PostWithArrays[]>} Posts with tag
 * @module modules/database/src/services/posts
 */
export async function findPostsByTag(tag: string): Promise<PostWithArrays[]> {
  return await prisma.post.findMany({
    where: {
      tags: { has: tag },
    },
  })
}

/**
 * Find posts with all specified tags.
 * Array filtering using PostgreSQL hasEvery() operator.
 * @param {string[]} requiredTags - Tags that must all be present
 * @returns {Promise<PostWithArrays[]>} Posts with all tags
 * @module modules/database/src/services/posts
 */
export async function findPostsByMultipleTags(
  requiredTags: string[]
): Promise<PostWithArrays[]> {
  return await prisma.post.findMany({
    where: {
      tags: { hasEvery: requiredTags },
    },
  })
}

/**
 * Add tag to post's tag array.
 * Array mutation using PostgreSQL push() operator.
 * @param {string} postId - Post identifier
 * @param {string} newTag - Tag to append
 * @returns {Promise<PostWithArrays>} Updated post
 * @module modules/database/src/services/posts
 */
export async function addTagToPost(
  postId: string,
  newTag: string
): Promise<PostWithArrays> {
  return await prisma.post.update({
    where: { id: postId },
    data: {
      tags: { push: newTag },
    },
  })
}
```

---

## GIN Indexes

```typescript
/**
 * PostgreSQL GIN (Generalized Inverted Index) configuration.
 * See `IndexConfig` in /plugins/nextjs-expert/skills/prisma-7/references/interfaces/model-attributes.types.md
 * @typedef {Object} PostWithIndexing
 * @property {string} id - Primary key
 * @property {string[]} tags - Array field (GIN indexed for fast array queries)
 * @property {string} content - Content field (GIN indexed for full-text search)
 * @module modules/database/src/models/post
 */
type PostWithIndexing = {
  id: string;
  tags: string[];
  content: string;
}

/**
 * Create GIN index on array field for optimal query performance.
 * GIN indexes speed up has(), hasEvery(), and hasAny() queries.
 * Use in Prisma schema: @@index([tags], type: Gin)
 * @param {string[]} tags - Tags to index
 * @returns {Promise<void>}
 * @module modules/database/src/migrations/create-indexes
 */
async function createGINIndexOnTags(): Promise<void> {
  // In schema:
  // model Post {
  //   tags String[]
  //   @@index([tags], type: Gin)
  // }
}

/**
 * Create full-text search GIN index on content.
 * Enables efficient full-text search using tsvector.
 * @returns {string} SQL migration command
 * @module modules/database/src/migrations/create-indexes
 */
function createFullTextSearchIndex(): string {
  return `
    CREATE INDEX post_content_idx ON "Post"
    USING GIN (to_tsvector('english', content));
  `
}
```

---

## Best Practices

1. **Use JSONB** - Not JSON for queries
2. **UUID for IDs** - Better for distributed systems
3. **Citext for emails** - Case-insensitive by default
4. **GIN for arrays/JSON** - Essential for performance
5. **Timestamptz** - Always store with timezone
