Databases - TopAnswers

Jack Douglas

^^^ AdventureWorks works — just on 2019 for now though

Jack Douglas

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&sample=adventureworks&fiddle=862c6a7f9f9275632f00c98ad40e9a60

Josh Darnell

lol

PeterVandivier

[v. festive](https://imgur.com/a/S7aey4m) :-P

Josh Darnell

Awesome, @Jack!

Paul White

yay

Jack Douglas

I've got the mechanics working just need to add some UI

Jack Douglas

AdventureWorks is coming to db<>fiddle soon

PeterVandivier replying to Josh Darnell

👏

Josh Darnell

@Peter has jokes :D

Josh Darnell

> I'd thank you for this post, but there's no appropriate emoji-based channel conveniently colocated with the content. – Peter Vandivier [31 mins ago](https://dba.stackexchange.com/questions/270678/truncate-a-table-with-17-billion-rows-in-an-ag/270689#comment532842_270689)

PeterVandivier

screaming internally dot gif

PeterVandivier

encountered during code review  
  
```sql  
CREATE UNIQUE INDEX foo
    ON bar 
    (md5(json_doc::text));
```

Jack Douglas

and it does what it does!

Jack Douglas

still, it isn't a cast, it's an operator/function

Jack Douglas

if it did, the first example above would produce `"1 abc 1"` (with the quotes)

Jack Douglas

<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1c6162ccc62b956ecd8f1fe5e41fe147

Jack Douglas

I'm wrong though, it doesn't stringify the way I thought:

Jack Douglas

`->>` is a function that returns `text`

Jack Douglas

<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f0839928a74cdc6227c04efc63569957

Jack Douglas replying to PeterVandivier

pg_typeof shows `text` because it is type `text`

Jack Douglas

lol

PeterVandivier

baby hacker looking for injection vulns in your chat endpoints

PeterVandivier

(my son says hello)

PeterVandivier

er-psd

PeterVandivier

 ,m jijuo ,ouikmm nj m./lpo[c/

PeterVandivier

where is there room for difference between the two?

PeterVandivier

like... the data is encoded just the same as if you wrote it to a data page on disk with type `text`... right? that's why `pg_typeof` shows you `text` when you inspect it

PeterVandivier replying to Jack Douglas

> it isn’t cast, it is stringified  
> it’s json in string representation  
  
i think i don't quite understand. :-/

PeterVandivier

yup, `pg_typeof()` is showing what i'd expect, just checking   
  
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=7270004a4cdc2e1587e78c239998053d&hide=3

Jack Douglas

The only thing I find strange in all of this is that your workaround doesn't throw the same error: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=139ff2cb9e0d8de0dcb12da202cd6395&hide=3

Jack Douglas

it's json in string representation

Jack Douglas

it isn't cast, it is stringified

Jack Douglas

->> is text always

Jack Douglas

well, yes

PeterVandivier

unless.... 🤔

PeterVandivier

<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=75296249554ba68fbe21b419584a1f37&hide=3  
  
it's the `->>'text'` that throws me though. by both the docs and by example, it gets cast to `text`, not `text[]`

Jack Douglas

can you give a simple example where that is the case?

Jack Douglas

> '1 1'::json is interpolated as an array

Jack Douglas replying to PeterVandivier

<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2f97c544a98dff5dfcd7f8c446e0b9e8

PeterVandivier

so maybe that's on me

PeterVandivier

most things can, eh?

PeterVandivier

although i guess this is postgres and now that i think about it, i don't _know for sure_ that `replace()` can't be overloaded to spit out an array or rowset

PeterVandivier replying to Jack Douglas

i think the reason i find it unintuitive is because the string-with-spaces is the result of an expression. sure it's fine if `'1 1'::json` is interpolated as an array because people are lazy and don't want to type more than they _absolutely have to_, but if the result of `func()` is known to be a scalar and you cast `func()::json`, then the fact that it gets array handling seems wrong to me

PeterVandivier replying to Jack Douglas

maybe, yea

PeterVandivier

doing a write-up for why shit broke yesterday and i want to use the phrase "NoSQL Strikes Back" since we're storing critical data as json docs in postgres because _Galaxy Brain_

Jack Douglas

NoSQL-style?

PeterVandivier

would it be appropriate to use the term "NoSQL" to refer to the practice of storing data that should be relational as a document in a relational DB?

Jack Douglas replying to PeterVandivier

The error in your fiddle isn't generated by `jsonb_set`, its generated by the cast to `jsonb`: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9757b6029d8e322f1ea43624cf35f766&hide=1. You can't cast `1 11` to json because it isn't a valid json string. You can cast `"1 11"` to json however.

PeterVandivier

sorry my initial minification went a little too far from the original spec. i was playing around with just generic expressions in `jsonb_set()` to get a feel for any wonky handling in the function