0

I build the following table (it adds yesterday's 8 digit date to some text):

create table DayBefore as select (current_date -interval '1 Days')::timestamp::text;
update daybefore set text = substr (text, 1, 10);
update daybefore set text = replace (text, '-', '');
update daybefore set text = 'MyFile_'||text;

and I can do:

select * from daybefore;

text
---------------------------------
 MyFile_20170126
(1 row)

You'll understand that if the associated script runs daily, this resulting entry also changes daily.

My question is: How do I now refer to the table MyFile_20170126 in a following command ie

select * from 'MyFile_????????' or create table my_table as select * from MyFile_????????

Going batty on this now and I can't upgrade the version of PostgreSQL from 8.2 either :-s

Any help greatly appreciated ... !

Alex K.
  • 171,639
  • 30
  • 264
  • 288
NigeH
  • 3
  • 3
  • Why not have *one* table with a date *column*. 99% of the time this is the correct approach. – Alex K. Jan 27 '17 at 16:30
  • It sounds like you may be looking for [partitioning](https://www.postgresql.org/docs/8.2/static/ddl-partitioning.html). That, and dynamic SQL, which depends entirely on how you're running the SQL: a pl/pgsql function? from PHP? from a command-line script using `psql`? – IMSoP Jan 27 '17 at 16:34
  • 1
    Also, your date format manipulation can all be done by [the `to_char` function](https://www.postgresql.org/docs/8.2/static/functions-formatting.html). I think the format you want is `'MyFile_' || to_char(current_date -interval '1 Days', 'YYYYMMDD')` – IMSoP Jan 27 '17 at 16:39
  • 2
    I don't really understand your question. Do you *really* have a table, which is called `MyFile_20170126`? Or you have (somewhere) a `MyFile_20170126` named file, which is some kind of export of a table (CSV f.ex.)? – pozs Jan 27 '17 at 16:44
  • Very little of your question makes sense. Whats the name of your table. What's the name of the field, and what are the values in that field. What you are trying to select from that table/field? – JNevill Jan 27 '17 at 17:13
  • Hi All and thanks for your comments ... Alex-K - I'm reading a table that's set by another program so I can't change any details. I'm reading the entire table so field names are irrelevant. – NigeH Jan 30 '17 at 10:11
  • Hi All and thanks for your comments ... Alex-K - I'm reading a table that's set by another program so I can't change any details. JNevill - I'm reading the entire table so field names are irrelevant. POZS - yes, I have a table called MyFile_YYYYMMDD. IMSoP - Dynamic SQL beyond me I'm afraid, also to_char function does not appear to work BUT I am trying to end up with [create table TEMP1 as select * FROM 'MyFile_'|| to_char(current_date -interval '1 Days', 'YYYYMMDD')] ... – NigeH Jan 30 '17 at 10:19
  • create table Temp_Store as select * FROM ('MyFile_' || to_char(current_date -interval '1 Days', 'YYYYMMDD')) host_table.fqdn AS "Src_FQDN" from MyFile_YYYYMMDD left outer join host_table on MyFile_YYYYMMDD.src_ip = host(host_table.ip) – NigeH Jan 30 '17 at 10:24
  • Sorry All - that looks confusing ... I am trying to read everything from a table that has as part of its name the date in the format YYYYMMDD, and I need to do it for yesterday's date eg if today is 30 Jan '17, then I am trying to select * from MyFile_20170129 - and I need to do this programmatically from psql on a daily basis. I can not upgrade from V8.2 . I have been unable to use to_char so far (don't know why but I get no usable value returned). I can build the table name in another temporary table but I then can't work out how to get it back to use it to read the table of that name. – NigeH Jan 30 '17 at 11:56
  • HELP!!!!!!! `wglog=# select * FROM 'MyFile_'|| to_char(current_date -interval '1 Days', 'YYYYMMDD') limit 10; ERROR: syntax error at or near "'MyFile_'" LINE 1: select * FROM 'MyFile_'|| to_char(current_date -interval '1 ...` – NigeH Feb 01 '17 at 13:15

0 Answers0