Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Consider to add check for columns with type json #153

Closed
mfvanek opened this issue Jul 25, 2022 · 1 comment · Fixed by #156
Closed

Consider to add check for columns with type json #153

mfvanek opened this issue Jul 25, 2022 · 1 comment · Fixed by #156
Assignees
Labels
enhancement New feature or request

Comments

@mfvanek
Copy link
Owner

mfvanek commented Jul 25, 2022

With json type it's possible to get an error
org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type json
in queries like
select distinct id, json_column from table_with_json;

We should avoid using json type.
Jsonb should be preferred instead

@mfvanek mfvanek added enhancement New feature or request help wanted Extra attention is needed labels Jul 25, 2022
@mfvanek mfvanek self-assigned this Aug 13, 2022
@mfvanek mfvanek added work in progress Work on this issue has already begun and removed help wanted Extra attention is needed labels Aug 13, 2022
@mfvanek
Copy link
Owner Author

mfvanek commented Aug 13, 2022

-- Finds columns of type 'json'. Use 'jsonb' instead.
select
    t.oid::regclass::text as table_name,
    col.attname::text as column_name,
    col.attnotnull as column_not_null
from pg_catalog.pg_class t
         join pg_catalog.pg_namespace nsp on nsp.oid = t.relnamespace
         join pg_catalog.pg_attribute col on (col.attrelid = t.oid)
where
        t.relkind = 'r' and
        col.attnum > 0 and /* to filter out system columns such as oid, ctid, xmin, xmax, etc.*/
        col.atttypid::regtype::text = 'json' and
        nsp.nspname = :schema_name_param::text
order by t.oid::regclass::text, col.attname::text;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant