CODEWITHSUNDEEP
postgresqlpostgresql-10
theGtknerd
theGtknerd

Reputation: 3745

Cast a PostgreSQL column to stored type

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:

1
10
100
2

Ok, so I change the SQL to:

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

Which reults in:

1
2
10
100

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.

Upvotes: 0

Views: 2276

Answers (2)

klin
klin

Reputation: 121474

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;

Upvotes: 2

theGtknerd
theGtknerd

Reputation: 3745

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.

Upvotes: 1

Related Questions