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.