Skip to main content

PostgreSQL handles variables differently from SQL Server. Here are the main approaches...

---
title: 'SQL Server vs PostgreSQL: Working with Variables'
subtitle: PostgreSQL handles variables differently from SQL Server.
author: Jon LaBelle
date: March 24, 2025
source: https://jonlabelle.com/snippets/view/markdown/sql-server-vs-postgresql-working-with-variables
notoc: false
---

## Using Variables in PostgreSQL

PostgreSQL handles variables differently from SQL Server. Here are the main approaches:

### 1. PL/pgSQL Blocks with DECLARE

```sql
DO $$
DECLARE
    customer_id INT := 123;
    order_date DATE := CURRENT_DATE;
BEGIN
    -- Use variables in queries
    INSERT INTO orders (customer_id, order_date)
    VALUES (customer_id, order_date);

    -- Can also select into variables
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE customer_id = customer_id;
END $$;
```

### 2. Functions with Variables

```sql
CREATE OR REPLACE FUNCTION get_recent_orders(customer_id INT)
RETURNS TABLE (order_id INT, order_date DATE) AS $$
DECLARE
    cutoff_date DATE := CURRENT_DATE - INTERVAL '30 days';
BEGIN
    RETURN QUERY
    SELECT o.id, o.order_date
    FROM orders o
    WHERE o.customer_id = customer_id
    AND o.order_date >= cutoff_date;
END;
$$ LANGUAGE plpgsql;

-- Call with: SELECT * FROM get_recent_orders(123);
```

### 3. Common Table Expressions (WITH)

```sql
WITH params AS (
    SELECT
        123 AS customer_id,
        CURRENT_DATE - INTERVAL '30 days' AS start_date
)
SELECT o.*
FROM orders o, params p
WHERE o.customer_id = p.customer_id
AND o.order_date >= p.start_date;
```

### 4. Prepared Statements

```sql
-- Create a prepared statement with placeholders
PREPARE order_query(INT, DATE) AS
SELECT * FROM orders WHERE customer_id = $1 AND order_date >= $2;

-- Execute with specific values
EXECUTE order_query(123, '2025-01-01');

-- When done
DEALLOCATE order_query;
```

Unlike SQL Server's `@variable` syntax, PostgreSQL uses named variables in procedural blocks and positional parameters (`$1`, `$2`) in prepared statements.

## Declaring Multiple Variables in PostgreSQL

Variable declarations always go between the `DECLARE` and `BEGIN` keywords, with each variable on its own line ending with a semicolon.

### In PL/pgSQL Blocks (DO blocks or functions)

You can declare any number of variables in the DECLARE section, with each variable on its own line ending with a semicolon:

```sql
DO $$
DECLARE
    customer_id INT := 123;
    order_date DATE := CURRENT_DATE;
    total_amount DECIMAL(10, 2) := 0;
    shipping_address TEXT := 'New York';
    is_priority BOOLEAN := TRUE;
BEGIN
    -- Use variables here
    INSERT INTO orders (customer_id, order_date, amount, address, priority)
    VALUES (customer_id, order_date, total_amount, shipping_address, is_priority);
END $$;
```

### In Functions

```sql
CREATE OR REPLACE FUNCTION process_order(p_order_id INT)
RETURNS VOID AS $$
DECLARE
    v_customer_id INT;
    v_order_date DATE;
    v_total_amount DECIMAL(10, 2) := 0;
    v_items_count INT;
    v_is_completed BOOLEAN := FALSE;
BEGIN
    -- Function body using the variables
    SELECT customer_id, order_date INTO v_customer_id, v_order_date
    FROM orders
    WHERE order_id = p_order_id;

    -- More processing...
END;
$$ LANGUAGE plpgsql;
```

## PostgreSQL Data Types for Variables

PostgreSQL is very flexible with types, supporting everything from simple primitives to complex structures like arrays, JSON, and geometric types.

Here are the most common ones:

### Numeric Types

```sql
DO $$
DECLARE
    id INT := 1;                           -- Integer
    small_num SMALLINT := 100;             -- Small integer
    big_num BIGINT := 9223372036854775807; -- Large integer
    price DECIMAL(10,2) := 99.99;          -- Exact decimal with precision
    amount NUMERIC := 123.45;              -- Exact numeric
    distance REAL := 123.456;              -- Floating point
    scientific DOUBLE PRECISION := 1.23e10; -- Double precision floating point
BEGIN
    -- Use variables here
END $$;
```

### String Types

```sql
DO $$
DECLARE
    code CHAR(3) := 'ABC';                 -- Fixed-length, padded with spaces
    name VARCHAR(100) := 'John Smith';     -- Variable-length with limit
    description TEXT := 'Long text...';    -- Variable unlimited length
BEGIN
    -- Use variables here
END $$;
```

### Date/Time Types

```sql
DO $$
DECLARE
    current_date DATE := CURRENT_DATE;
    meeting_time TIME := '14:30:00';
    created_at TIMESTAMP := '2025-03-24 12:30:45';
    created_at_tz TIMESTAMPTZ := '2025-03-24 12:30:45-07';
    duration INTERVAL := '2 hours 30 minutes';
BEGIN
    -- Use variables here
END $$;
```

### Other Common Types

```sql
DO $$
DECLARE
    is_active BOOLEAN := TRUE;
    uuid_val UUID := 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
    json_data JSONB := '{"name": "John", "age": 30}';
    ip_address INET := '192.168.0.1';
    numbers INT[] := ARRAY[1,2,3,4,5];     -- Array type
BEGIN
    -- Use variables here
END $$;
```

### User-Defined Types

You can also use enum types, composite types, and domain types:

```sql
-- Enum example (create type first)
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

DO $$
DECLARE
    current_mood mood := 'happy';
BEGIN
    -- Use variables here
END $$;
```