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
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.