PgPal Setup

1. Launch PgPal.

2. Create a project.

3. Add an environment with your database details.

4. Head to your Workspace and start writing SQL.

-- Create a table with a JSONB column and metadata
create table if not exists user_profiles (
  id integer primary key generated by default as identity,
  name text not null,
  email text not null unique,
  metadata jsonb,
  created_at timestamptz default now()
);

-- Seed data
insert into user_profiles (name, email, metadata) values
  ('Alice', 'alice@example.com', '{"age": 32, "tags": ["admin", "beta"], "location": {"city": "Philly", "state": "PA"}}'),
  ('Bob', 'bob@example.com', '{"age": 28, "tags": ["editor"], "location": {"city": "Boston", "state": "MA"}}'),
  ('Charlie', 'charlie@example.com', '{"age": 35, "tags": ["viewer", "legacy"], "location": {"city": "Chicago", "state": "IL"}}');

-- Fetch everyone
declare allUsers = select id, name, email, metadata, created_at from user_profiles;

-- Get user's ID and city
declare $1 = allUsers[0].id;
declare $2 = allUsers[0].metadata.location.city;

-- Fetch just that user again using variables
select id, name, email, metadata, created_at from user_profiles
where id = $1
  and metadata->'location'->>'city' = $2;

-- Update that user's tags to include "power-user"
update user_profiles
set metadata = jsonb_set(
  metadata,
  '{tags}',
  (metadata->'tags')::jsonb || '"power-user"'
)
where id = $1;

-- Re-fetch the updated user
declare updatedUser = select id, name, email, metadata, created_at from user_profiles where id = $1;

-- Delete the user if they have the "legacy" tag
delete from user_profiles
where metadata->'tags' ? 'legacy';

-- Select everyone again to show final state
select id, name, email, metadata, created_at from user_profiles;

-- Drop table
drop table user_profiles;

PgPal makes common tasks like data exploration, debugging, and collaboration faster and more intuitive—without needing to bounce between terminals, notebooks, or version-controlled .sql files. Whether you’re managing a production schema or experimenting locally, PgPal keeps your workflow smooth.