Postgres check if column name is valid

Question

In Oracle we have

select dbms_assert.simple_sql_name(TEST) from dual

what will be the equivalent in postgresql ? I can't seem to find anywhere the answer.

If column is wrong in Oracle we get:

 ORA-44003: invalid SQL name ORA-06512: at "SYS.DBMS_ASSERT", line 160
 44003. 0000 -  "invalid SQL name"
 *Document: Yes
 *Cause:    The input parameter string was not a valid simple SQL name.
 *Action:   Check with the DBMS_ASSERT spec to verify that the parameter
            string is a valid simple SQL name.

You can easily write such a function yourself using the specification from the documentation. — Laurenz Albe
– Laurenz Albe, Commented Dec 13, 2017 at 15:31

Laurenz Albe · Accepted Answer · 2017-12-13 15:47:21Z

3

Not exactly the same thing, but an "illegal" identifier has to be quoted in Postgres (and SQL). The function format() has a special place holder for identifiers and will return a quoted identifier if anything is non-standard:

So select format('%I', 'foo') returns foo.

But select format('%I', 'foo bar') returns "foo bar"

So if you check that the return value is quoted (starts with a ") then it's not a valid identifier.

This also checks for reserved keywords, so select format('%I', 'order') returns "order"

edited Dec 13, 2017 at 15:47

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

answered Dec 13, 2017 at 15:32

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres check if column name is valid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related