Cast a PostgreSQL column to stored type

Question

I am creating a viewer for PostgreSQL. My SQL needs to sort on the type that is normal for that column. Take for example:

Table:

CREATE TABLE contacts (id serial primary key, name varchar)

SQL:

SELECT id::text FROM contacts ORDER BY id;

Gives:

Ok, so I change the SQL to:

SELECT id::text FROM contacts ORDER BY id::regtype;

Which reults in:

Nice! But now I try:

SELECT name::text FROM contacts ORDER BY name::regtype;

Which results in:

invalid type name "my first string"

Google is no help. Any ideas? Thanks

Repeat: the error is not my problem. My problem is that I need to convert each column to text, but order by the normal type for that column.

klin · Accepted Answer · 2018-09-10 23:41:50Z

2

regtype is a object identifier type and there is no reason to use it when you are not referring to system objects (types in this case).

You should cast the column to integer in the first query:

SELECT id::text 
FROM contacts 
ORDER BY id::integer;

You can use qualified column names in the order by clause. This will work with any sortable type of column.

SELECT id::text
FROM contacts 
ORDER BY contacts.id;

edited Sep 10, 2018 at 23:41

answered Sep 10, 2018 at 17:44

klin

124k15 gold badges244 silver badges265 bronze badges

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

3 Comments

theGtknerd Over a year ago

Ok, so I know that I don't need to convert to text if it is text. The problem? I don't know what each column is because the user controls the types, I don't. I am creating a viewer for Postgresql. I need each column converted to specifically text, and sorted by the normal sort order for that column. My app is over 1000 lines, or I would post the whole thing.

klin Over a year ago

Your application should retrieve the types of returned columns (which is not difficult) and use the appropriate type. Note however that the application must be prepared for special cases of unsortable types (eg json).

theGtknerd Over a year ago

Sigh... It seems some of this stuff is not easy. That is what I ended up doing on my other question.

theGtknerd · Accepted Answer · 2018-09-10 23:14:05Z

So, I found two ways to accomplish this. The first is the solution @klin provided by querying the table and then constructing my own query based on the data. An untested psycopg2 example:

c = conn.cursor()
c.execute("SELECT * FROM contacts LIMIT 1")
select_sql = "SELECT "
for row in c.description:
    if row.name == "my_sort_column":
        if row.type_code == 23:
            sort_by_sql = row.name + "::integer "
        else:
            sort_by_sql = row.name + "::text "
c.execute("SELECT * FROM contacts " + sort_by_sql)

A more elegant way would be like this:

SELECT id::text AS _id, name::text AS _name AS n FROM contacts ORDER BY id

This uses aliases so that ORDER BY still picks up the original data. The last option is more readable if nothing else.

A good try with aliases. If this simple solution meets your expectations, using the table name would be more elegant, see my updated answer.
I had tried this first, and it didn't work, @klin. Now I just tried it again after your suggeston, now it works. I must have made a mistake somewhere. Thanks!

Collectives™ on Stack Overflow

Cast a PostgreSQL column to stored type

2 Answers 2

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Linked

Related