This is the second part of the series I started on Learning PostgreSQL from AI and JSON exploration: Part 1. For this 2nd part, I decided to try gpt-oss the 14GB model which was just released in the past week. My first impression, "When will this ai shut up about its thinking process?". Phi4 on the other hand gave a bit of a narrative about how it came up with answers but it did not write a novel about it and it also seemed more personable, using "My and you" instead of a distanced "User wants to". However gpt-os did become less chatty with a bit of coaxing. So anyway personality wise I prefer phi4. That is not to say that Gpt-oss doesn't have some wow factor. It created perfect SQL each time. I did not need to correct it. One of the other neat features of this model which I haven't had time to explore is ability to have it do browser searches and other agentic like stuff.
Again for this size model, speed of answering with my crappy graphics card and processor was not bad, but it was noticeably slower than Phi4. Given the impressive accuracy, I didn't mind too much and I suspect can be improved by tweaking some of its settings.
Here are a couple of ways I feel an AI like gpt-oss can help with personal growth.
Lets start as we did before assessing gpt-oss recency of knowledge about PostgreSQL. I did not try to tweak any of its settings and out of the box it's painfully chatty. As I used it more, probably because my prior conversation was still in it's context, it became less chatty and event stopped with the impersonal "User requests" preface.
To prevent it from writing a novel about PostgreSQL's versioning before giving me an answer, I asked it this way
Me:What is the latest version of PostgreSQL in one sentence. No narrative.
To which I am greeted with below which took quite some time cause it was probably thinking about PostgreSQL versionining patterns over the years.
gpt-oss:Thinking... User requests: "What is the latest version of PostgreSQL in one sentence. No narrative". So we answer: "PostgreSQL 17.4".
Me:
Create a PostgreSQL table with a jsonb column that has heavily nested data that has 2 rows covering trips where the primary key is generated using `GENERATED ALWAYS AS IDENTITY`. No thinking narrative.
I threw in the requirement about GENERATED BY because just like phi4, gpt-oss wanted to give me an old-school bigserial. Its generated data output freaked me out a bit because many of the locations were places that are a few blocks from me, though the names were slightly different or had the wrong address, which I assume was intentional. Is it because I live at the center of techie civilization or it picked up my GPS location and thought "I'll show her some stuff she can relate to". Maybe next time, I will allow it to go on about how it came up with these places.
gpt-oss:Thinking... We need to provide just the SQL code. No narrative. Provide table creation and insertion of two rows with nested JSONB. Keep concise. ...done thinking.
CREATE TABLE trips (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
data jsonb NOT NULL
);
INSERT INTO trips (data) VALUES
(
'{
"trip_name": "Weekend Getaway",
"trip_type": "leisure",
"origin": {
"city": "New York",
"airport": "JFK",
"coordinates": { "lat": 40.6413, "lon": -73.7781 }
},
"destination": {
"city": "Boston",
"airport": "BOS",
"coordinates": { "lat": 42.3656, "lon": -71.0096 }
},
"stops": [
{
"stop_id": 1,
"name": "Boston Airport",
"arrival": "2025-09-01T10:00:00-04:00",
"departure": "2025-09-01T11:30:00-04:00",
"location": {
"coordinates": { "lat": 42.3656, "lon": -71.0096 },
"address": {
"street": "S. Station Fwy",
"city": "Boston",
"state": "MA",
"zip": "02128"
}
},
"weather": {
"forecast": [
{ "date": "2025-09-01", "high": 78, "low": 62, "condition": "Partly Cloudy" },
{ "date": "2025-09-02", "high": 80, "low": 63, "condition": "Sunny" }
],
"precipitation_probability": 0.15
}
},
{
"stop_id": 2,
"name": "Boston City Center",
"arrival": "2025-09-01T12:00:00-04:00",
"departure": "2025-09-01T15:00:00-04:00",
"location": {
"coordinates": { "lat": 42.3601, "lon": -71.0589 },
"address": {
"street": "1 First St",
"city": "Boston",
"state": "MA",
"zip": "02108"
}
},
"weather": {
"forecast": [
{ "date": "2025-09-01", "high": 78, "low": 62, "condition": "Partly Cloudy" },
{ "date": "2025-09-02", "high": 80, "low": 63, "condition": "Sunny" }
],
"precipitation_probability": 0.12
}
}
],
"itinerary": {
"activities": [
{
"time": "2025-09-01T12:30:00-04:00",
"activity": "Lunch at Quincy Market",
"details": { "restaurant": "Trident", "cuisine": "Seafood", "reservation": true }
},
{
"time": "2025-09-01T14:00:00-04:00",
"activity": "Freedom Trail",
"details": { "tour_type": "Self‑guided", "duration_minutes": 120, "cost_usd": 0 }
}
],
"accommodation": {
"hotel": {
"name": "Boston Marriott Copley Place",
"address": "101 State Street, Boston, MA 02108",
"room_type": "Deluxe King",
"check_in": "2025-09-01T15:30:00-04:00",
"check_out": "2025-09-02T11:00:00-04:00"
}
}
}
}'::jsonb
),
(
'{
"trip_name": "Business Trip",
"trip_type": "work",
"origin": {
"city": "San Francisco",
"airport": "SFO",
"coordinates": { "lat": 37.6213, "lon": -122.3790 }
},
"destination": {
"city": "Seattle",
"airport": "SEA",
"coordinates": { "lat": 47.6038, "lon": -122.3308 }
},
"stops": [
{
"stop_id": 1,
"name": "Seattle Airport",
"arrival": "2025-10-10T09:00:00-07:00",
"departure": "2025-10-10T09:30:00-07:00",
"location": {
"coordinates": { "lat": 47.6038, "lon": -122.3308 },
"address": {
"street": "3rd Ave & E Denny Way",
"city": "Seattle",
"state": "WA",
"zip": "98128"
}
},
"weather": {
"forecast": [
{ "date": "2025-10-10", "high": 68, "low": 54, "condition": "Cloudy" }
],
"precipitation_probability": 0.22
}
},
{
"stop_id": 2,
"name": "Microsoft Seattle HQ",
"arrival": "2025-10-10T10:30:00-07:00",
"departure": "2025-10-10T11:30:00-07:00",
"location": {
"coordinates": { "lat": 47.6694, "lon": -122.3458 },
"address": {
"street": "1501 2nd Ave W",
"city": "Seattle",
"state": "WA",
"zip": "98121"
}
},
"weather": {
"forecast": [
{ "date": "2025-10-10", "high": 70, "low": 58, "condition": "Clear" }
],
"precipitation_probability": 0.05
}
}
],
"itinerary": {
"meetings": [
{
"time": "2025-10-10T10:30:00-07:00",
"location": "Microsoft Seattle HQ",
"participants": ["John Doe", "Jane Smith", "Alan Turing"],
"agenda": ["Kickoff", "Budget Review", "Road‑map"]
},
{
"time": "2025-10-10T11:30:00-07:00",
"location": "Amazon Seattle HQ",
"participants": ["Bob Johnson", "Elon Musk"],
"agenda": ["Partnership", "Tech Integration"]
}
],
"accommodation": {
"hotel": {
"name": "The Edgewater Seattle",
"address": "121 2nd Ave, Seattle, WA 98101",
"room_type": "Executive Suite",
"check_in": "2025-10-10T14:00:00-07:00",
"check_out": "2025-10-11T11:00:00-07:00"
}
}
}
}'::jsonb
);
gpt-oss gave exceptionally beautiful and flawlessly runnable code and it gave me no more and no less than the column structure I asked for.
Now that we have some dense data, let's query it.
Me:Write a PostgreSQL SQL statement that returns the date and itineraries of each trip one row per itinerary. No thinking narrative.
gpt-oss took about a minute or 2 to come up with an answer for this on my desktop, but it gave a pretty decent answer and a short description of its thinking process.
Thinking... We need to extract date and itineraries per trip. Likely itinerary activities: each row per activity. Provide date and itinerary details. Use jsonb_to_recordset. Let's craft: SELECT id, trip_name, activity_time, activity, details FROM trips, jsonb_to_recordset(data->'itinerary'->'activities') as x(activity_time timestamp, activity text, details jsonb). Or extract date part of time. Provide date (activity_time::date). So output: date, activity, details. Let's craft final answer. ...done thinking.
SELECT
t.id,
t.data->>'trip_name' AS trip_name,
activity_rec.time::date AS activity_date,
activity_rec.activity AS activity,
activity_rec.details AS activity_details
FROM
trips AS t
CROSS JOIN LATERAL
jsonb_to_recordset(
t.data -> 'itinerary' -> 'activities'
) AS activity_rec(
time TIMESTAMP,
activity TEXT,
details JSONB
);
Let's revise a bit cause I don't want to read a blob of json in that activity details.
Me:Write a PostgreSQL SQL statement that returns the date, time, type, name of each activity or meeting, one row per activity or meeting. For the type specify if it is a meeting or an activity. No thinking narrative.
As you can see it did end up providing a bit of narrative. But if you are learning how to query PostgreSQL, the explaining you probably welcome.
What I really love about gpt-oss explanation is that it made me aware of ambiguities in my question and things I didn't really think that much about relative to the structure of the data. More importantly I discovered that I've never used this wonderful function jsonb_to_recordset, though I had heard about it and yet this wonderful function has existed since PostgreSQL 13.
gpt-oss:Thinking... We need a query to return date, time, type (meeting or activity), name. Activities have fields: time, activity, details. Meetings have time, location, participants, agenda. So type determined by presence of 'activity' key. Or we can union two selects. We need date, time, type, name. For activity name: activity field. For meeting name: maybe location or maybe some field like 'meeting'. But they didn't specify meeting name. We could use 'location' as name for meeting. The problem: "type, name of each activity or meeting". For meeting, name could be location. Use location. We'll union. Use CROSS JOIN LATERAL. Return: trip id maybe optional. They didn't ask for trip id. Just date, time, type, name. Implement: SELECT date, time, 'activity' AS type, activity AS name FROM activities UNION ALL SELECT date, time, 'meeting' AS type, location AS name FROM meetings But need to parse JSON. We'll use jsonb_to_recordset for activities and meetings. Return date and time from timestamp. Use date(time) and time(time). The 'time' field is TIMESTAMP. Also include type. Let's produce: SELECT (act.time)::date AS date, (act.time)::time AS time, 'activity' AS type, act.activity AS name FROM trips t CROSS JOIN LATERAL jsonb_to_recordset(t.data->'itinerary'->'activities') AS act(time TIMESTAMP, activity TEXT, details JSONB) UNION ALL SELECT (meet.time)::date AS date, (meet.time)::time AS time, 'meeting' AS type, meet.location AS name FROM trips t CROSS JOIN LATERAL jsonb_to_recordset(t.data->'itinerary'->'meetings') AS meet(time TIMESTAMP, location TEXT, participants JSONB, agenda JSONB); Order by date, time. Let's produce final answer. ...done thinking.
/* Return one row per activity or meeting, showing
the date, time, type (activity / meeting) and name */
SELECT
(act.time)::date AS date,
(act.time)::time AS time,
'activity' AS type,
act.activity AS name
FROM
trips t
CROSS JOIN LATERAL
jsonb_to_recordset(
t.data -> 'itinerary' -> 'activities'
) AS act(
time TIMESTAMP,
activity TEXT,
details JSONB
)
UNION ALL
SELECT
(meet.time)::date AS date,
(meet.time)::time AS time,
'meeting' AS type,
meet.location AS name
FROM
trips t
CROSS JOIN LATERAL
jsonb_to_recordset(
t.data -> 'itinerary' -> 'meetings'
) AS meet(
time TIMESTAMP,
location TEXT,
participants JSONB,
agenda JSONB
)
ORDER BY date, time;