Create a mapping table in postgresql (PGT Redshift) with given data

0
votes

How do I create a permanent table in PGSQL - for example: Say I have the following mapping table called 'Cars_Mapping_Table' (this table currently resides in an excel doc) {

  • FULL_NAME ----- ABBREV
  • ford --------------- fd
  • chevy ------------- ch
  • nissan ------------ ni

I want to create this table in the database and be able to make updates to it if a new car brand comes out. Pretend all my tables in the database always use the ABBREV field and I want the easy 'Cars_Mapping_Table' available so that I can always convert the abbreviation to the Full_name.

I feel like this should be fairly simple, but I can't find how to do it. THANKS

1
postgresqlamazon-redshift

1 Answers

0
votes

This will create the table for you that you can update when you have new data:

drop table if exists Cars_Mapping_Table;
create table Cars_Mapping_Table
(
    FULL_NAME varchar(50),
    ABBREV varchar(2)
)
distkey(ABBREV)
sortkey(ABBREV);

insert into Cars_Mapping_Table
select 'ford', 'fd' union
select 'chevy','ch' union
select 'nissan','ni'