0

I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must double quote it to work with it, for example, SELECT * FROM crs_1973."2015_04_21"; (this works for me). However, I want to extract information about this table from the PostgreSQL information_schema. Specifically, I need the data types of the columns in the table 2015_04_21,

I have tried this query with a table whose name starts with a letter and it produces the desired result:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'some_table_name_that_starts_with_a_letter'
ORDER  BY ordinal_position;

However, when I run this query with the tables whose names begin with a number, it does not. For example:

WHERE  table_name = 'crs_1973.2015_04_21'

does not work. Nor does:

WHERE  table_name = 'crs_1973'.'2015_04_21'
WHERE  table_name = 'crs_1973'."2015_04_21"
WHERE  table_name = 'crs_1973'.'"2015_04_21"'
...

etc. How do I make this query work for a table name that starts with a number? Is this possible?

There are tables called "2015_04_21" in several different schemas so I need to pass a qualified table name to the query.

2

@ypercube's solution (in the comments above):

SELECT column_name, data_type
FROM   information_schema.columns
WHERE table_schema = 'crs_1973' AND
table_name = '2015_04_21'
ORDER  BY ordinal_position;

worked perfectly. I'm writing it up here so that I can accept the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.