0

I'm trying to query some data in Postgres and I'm wondering how I might use some sort of pattern matching not merely to select rows - e.g. SELECT * FROM schema.tablename WHERE varname ~ 'phrase' - but to select columns in the SELECT statement, specifically doing so based on the common names of those columns.

I have a table with a bunch of estimates of rates over the course of many years - say, of apples picked per year - along with upper and lower 95% confidence intervals for each year. (For reference, each estimate and 95% CI comes from a different source - a written report, to be precise; these sources are my rows, and the table describes various aspect of each source. Based on a critique from below, I think it's important that the reader know that the unit of analysis in this relational database is a written report with different estimates of things picked per year - apples in one Table, oranges in another, pears in a third.)

So in this table, each year has three columns / variables:

rate_1994 low_95_1994 high_95_1994

The thing is, the CIs are mostly null - they haven't been filled in. In my query, I'm really only trying to pull out the rates for each year: all the variables that begin with rate_. How can I phrase this in my SELECT statement?

I'm trying to employ regexp_matches to do this, but I keep getting back errors.

I've done some poking around StackOverflow on this, and I'm getting the sense that it may not even be possible, but I'm trying to make sure. If it isn't possible, it's easy to break up the table into two new ones: one with just the rates, and another with the CIs.

(For the record, I've looked at posts such as this one: Selecting all columns that start with XXX using a wildcard? )

Thanks in advance!

logjammin
  • 1,121
  • 6
  • 21
  • 1
    That's a terrible data design. You should have a table with a column for the period and another one for the value. – sticky bit Apr 15 '19 at 22:52
  • @stickybit I don't follow. Can you be more specific? – logjammin Apr 15 '19 at 22:54
  • 1
    Instead of adding a new column for each year have a table and add a row for each year. It would be easy to search in that with a `WHERE year LIKE 'rate%'` or similar. – sticky bit Apr 15 '19 at 23:03
  • @stickybit Ah I see, thanks. That makes sense, but the issue there is that the entities this whole database revolves around is a set of written reports with different estimates of the same phenomena. Report A has estimates of apples picked in 1996 and 2002, while Report B has estimates for 2000 and 2008, for example. Table 1 is about e.g. Apples and Table 2 is about Oranges picked. In Table 2, for example, Report B may not have an estimate of Oranges, but A does and a different report, Report C does, too. When I set out to organize this db, that's the way that made sense. – logjammin Apr 15 '19 at 23:07
  • Have another table for reports with an id and the fruit it is about. To the table with the values add another column that identifies the report. Problem solved, the relational way. – sticky bit Apr 15 '19 at 23:10
  • @stickybit What you're saying would ultimately involve reorganizing the database - transposing all these matrices, basically - and I'd need to think through if that works for my purposes. EDIT: just saw that last column! Okay, I think that makes sense. I'll take a crack at it. Cheers! – logjammin Apr 15 '19 at 23:11

1 Answers1

0

If what you are basically asking is can columns be selected dynamically based on an execution-time condition,

No.

You could however use PL/SQL to build up a query as a string and then execute it using EXECUTE IMMEDIATE.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thanks. As my discussion with @stickybit above shows, I think may have more to do with how I've designed the database than with Postgres's ability to query it. I have to work on mapping this thing out better than I have (I'm poring over db design best practices as we speak). I may post another question about that tomorrow, after I've chewed on this for a bit, but maybe the main thing I'm realizing here is that I'm a complete rookie and I have a lot to learn about structuring db's before I get deep into querying them. – logjammin Apr 16 '19 at 00:04