Mapping with two tables using Postgres

Question

I have two tables,

Table_1 contains like,

Project_name    Date          value
P1              15/06/2016
P2              25/04/2017
P3              18/06/2017
P4              12/05/2017

Table_2 contains like,

Name      Occ_June_2016  Occ_April_2017 Occ_May_2017  Occ_June_2017  
P1          8.1               7.5           6.5          8.2
P2          8.3               7.4           6.0          8.5
P3          8.6               7.1           6.1          8.1
P4          8.8               7.9           6.8          8.9

I want to get the value, based on mapping the project_name and the date.

Here is what i have tried. Converting table_1 date column to a particular format,

 SELECT to_char(to_date(a.date, 'DD-MM-YYYY'), 'mon_YYYY') from table_1 a ;

Output I got as,

jun_2016
apr_2017
jun_2017
may_2017

By using output above i want to search the column in table_2 by matching the column and name,

Am trying to get the columns based on partial match condition,

select column_name from information_schema.COLUMNS as c where c.TABLE_NAME = 'table_2' and c.COLUMN_NAME like '%occ_%';

Here is the output for the above query,

Occ_June_2016  
Occ_April_2017 
Occ_May_2017  
Occ_June_2017

Now i need to take the output of one query to the input of one query Here is where am stucked. Mapping the date based on name.

My output should be like,

Project_name    Date            value
P1              15/06/2016      8.1
P2              25/04/2017      7.4
P3              18/06/2017      8.1 
P4              12/05/2017      6.8

kindly give me a solution. Thanks in advance

Dimitar Spasovski · Accepted Answer · 2018-07-07T13:54:12.357

1

This can be solved with a database function. First, lets create the tables and fill them with example data.

Creating and filling table_1

CREATE TABLE table_1 (project_name TEXT, Date DATE, value TEXT);
INSERT INTO table_1(project_name, Date) VALUES ('P1','06/15/2016'), ('P2','04/25/2017'), ('P3','06/23/2017'), ('P4','05/12/2017') ;

Creating and filling table_2

CREATE TABLE table_2 (name TEXT, occ_june_2016 DECIMAL, occ_april_2017 DECIMAL, occ_may_2017 DECIMAL, occ_june_2017 DECIMAL);
INSERT INTO table_2(name, occ_june_2016, occ_april_2017, occ_may_2017, occ_june_2017) VALUES
  ('P1',          8.1,               7.5,           6.5,          8.2),
  ('P2',          8.3,               7.4,           6.0,          8.5),
  ('P3',          8.6,               7.1,           6.1,          8.1),
  ('P4',         8.8,               7.9,           6.8,          8.9);

Next, we create the function:

CREATE OR REPLACE FUNCTION getData(projectName TEXT, projectDate DATE)
  RETURNS DECIMAL
  AS
  $$
  DECLARE
    columnName TEXT :=  'Occ_' || trim(to_char(projectDate, 'Month')) || '_' || to_char(projectDate, 'yyyy');
    selectQuery TEXT := 'SELECT %s FROM table_2 where name = ''%s'' LIMIT 1';
    returnValue DECIMAL;
  BEGIN
  selectQuery = format(selectQuery, columnName, projectName);
  EXECUTE selectQuery INTO returnValue;
  RETURN returnValue;
  END;
  $$ LANGUAGE 'plpgsql';

The function takes 2 arguments. The first one (projectName) represents the name of the project (for example 'P1'). The second one (projectDate) is the date on which we want to retrieve the data (for example '15/06/2016').

We start by generating the name of the date column in table 2 from the the value of the projectDate variable. Once we have a name, we generate a dynamic sql query by replacing the %s placeholders with the values of the columnName and projectName variables respectively.

We execute the query and store the result in the returnValue variable which this function returns.

After we created the tables and the function, we can execute the following query:

 SELECT project_name, Date, getData(project_name, Date) FROM table_1;

This query returns the following result:

project_name      date       getdata
P1             2016-06-15      8.1
P2             2017-04-25      7.4
P3             2017-06-23      8.1
P4             2017-05-12      6.8

edited Jul 07 '18 at 13:54

answered Jul 07 '18 at 08:03

Dimitar Spasovski

2,023
9
29
45

Do i have to put the above query instead of selectQuery there ?? Can you help out ??? – venkat Jul 07 '18 at 13:08
No you dont need to do that. Just execute the first block of code I posted (The one that starts with `CREATE OR REPLACE` and ends with `$$ language 'plpgsql`). After you execute that (that statment needs to be executed only once, because functions need to be executed only once) you use the `SELECT project_name, Date, getData(project_name, Date) FROM table_1;` in the format you want. If you want I can post full sql example with test data. – Dimitar Spasovski Jul 07 '18 at 13:17
LINE 1: SELECT project_name, first_move_in_date, getData(project_nam... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Am getting this error – venkat Jul 07 '18 at 13:32
have you executed the CREATE function code block? Please try executing all of the code I wrote in the order I wrote it. – Dimitar Spasovski Jul 07 '18 at 13:34
Show me the select statement you are using. It is different than the one I am using. – Dimitar Spasovski Jul 07 '18 at 13:36
select project_name, Date, getData(project_name, Date) FROM table_1; – venkat Jul 07 '18 at 13:37
What is the `Date` column type in your table_1 ? – Dimitar Spasovski Jul 07 '18 at 13:39
date is the type – venkat Jul 07 '18 at 13:40
Well I am not sure what could be wrong. Did you execute the CREATE TABLE statements I gave you above ? – Dimitar Spasovski Jul 07 '18 at 13:41
yes i am executing what ever u created. That gives me error when i execute select statement. – venkat Jul 07 '18 at 13:41
As a last resort, please try creating a new database and executing all of the statements there. I see no reason why it shouldn't work. – Dimitar Spasovski Jul 07 '18 at 13:43
is it because of this ( plpgsql ). Mine is postgresql – venkat Jul 07 '18 at 13:43
Mine is psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04)) – venkat Jul 07 '18 at 13:44
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/174561/discussion-between-dvorog-and-venkat). – Dimitar Spasovski Jul 07 '18 at 13:45
hey my date column has a format 'dd/mm/yyy', how to convert this into 'mm/dd/yyyy' – venkat Jul 07 '18 at 16:36
This question should be of help to you https://stackoverflow.com/questions/6123484/how-do-i-alter-the-date-format-in-postgres – Dimitar Spasovski Jul 07 '18 at 16:52
It throws me error, if the project name from table 1 is not present in table 2 Can you help me to sove that ??? – venkat Jul 08 '18 at 03:08
It should return null in that case, not throw an error. for example: `SELECT getData('P5','04/15/2017');` will return null because there is no 'P5' in the table. – Dimitar Spasovski Jul 08 '18 at 10:25

Mapping with two tables using Postgres

1 Answers1