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.

asked Oct 14, 2015 at 10:44
0

1 Answer 1

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.

answered Oct 15, 2015 at 7:32

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.