MIME-Version: 1.0
References: 
 <CAJyMCYJ+JGpHtpV36UP+kUAW4LoprmgbgDPLCpe_8A0xbe=GZA@mail.gmail.com>
 <CAJyMCYLL-mMuCfXT8M1kD-=6=r8YHHrSGbT-rN25vCwtVyr1jg@mail.gmail.com>
 <CAEzk6ffszUabGjDhBOCZqTP0PoMzLaJtnu+=ye2snMVSOy=b4w@mail.gmail.com>
 <CAJyMCYLvymAS+7DoiHGs2zHKGigQj7waGzdXWbo+3xBUzi4Mew@mail.gmail.com>
 <CAJyMCYJDiY6hoLdtM=T2nc8rnZTxBbNOr0mHsJ5v6OPtes6B0Q@mail.gmail.com>
 <CAEzk6fcfXk_-UweJZ_hFWecDAs_0ECd27Q_r_=o1V78k5xBBuA@mail.gmail.com>
In-Reply-To: 
 <CAEzk6fcfXk_-UweJZ_hFWecDAs_0ECd27Q_r_=o1V78k5xBBuA@mail.gmail.com>
From: Anthony Apollis <anthony.apollis@gmail.com>
Date: Thu, 20 Jul 2023 17:47:08 +0200
Message-ID: 
 <CAJyMCY+97Lg=EANU6g5iioj0ay+FMssws2Dfjb1vV9UGUjG39w@mail.gmail.com>
Subject: Re: TSQL To Postgres - Unpivot/Union All
To: Geoff Winkless <pgsqladmin@geoff.dj>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003989eb0600ed1049"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJyMCY%2B97Lg%3DEANU6g5iioj0ay%2BFMssws2Dfjb1vV9UGUjG39w%40mail.gmail.com>
Precedence: bulk

--0000000000003989eb0600ed1049
Content-Type: text/plain; charset="UTF-8"

CREATE TEMP TABLE temp_FieldFlowsFact (
    Account varchar(20),
    "Calendar day" DATE,
    "Financial year" varchar(5),
    Period varchar(8),
    "Period type" varchar(10),
    Metric varchar(50),
    "Metric Value" float
)
It was created as a temp table in SQLServer, but it does not have to be in
Postgres. Thanks

On Thu, 20 Jul 2023 at 16:51, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

> On Thu, 20 Jul 2023 at 15:28, Anthony Apollis <anthony.apollis@gmail.com>
> wrote:
> > I am attaching my TSQL and Postgres SQL:
>
> You're still missing some CREATEs, for example for temp_FieldFlowsFact.
>
> Even assuming your columns list is correct, I would still (and as a
> matter of habit) include the target column list in your INSERT
> statements, if only to make your own life easier, but especially
> because any later changes to the tables could end up shifting the
> position of the columns.
>
> Certainly it will be easier for anyone else trying to disentangle the SQL
> later.
>
> As an aside, can you clarify whether you mean temporal tables (and are
> you using a temporal tables extension)? Or should that read
> "temporary"?
>
> Geoff
>

--0000000000003989eb0600ed1049
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">CREATE TEMP TABLE temp_FieldFlowsFact (<br>=C2=A0 =C2=A0 A=
ccount varchar(20),<br>=C2=A0 =C2=A0 &quot;Calendar day&quot; DATE,<br>=C2=
=A0 =C2=A0 &quot;Financial year&quot; varchar(5),<br>=C2=A0 =C2=A0 Period v=
archar(8),<br>=C2=A0 =C2=A0 &quot;Period type&quot; varchar(10),<br>=C2=A0 =
=C2=A0 Metric varchar(50),<br>=C2=A0 =C2=A0 &quot;Metric Value&quot; float<=
br>)<br><div>It was created as a temp table in SQLServer, but it does not h=
ave to be in Postgres. Thanks</div></div><br><div class=3D"gmail_quote"><di=
v dir=3D"ltr" class=3D"gmail_attr">On Thu, 20 Jul 2023 at 16:51, Geoff Wink=
less &lt;<a href=3D"mailto:pgsqladmin@geoff.dj">pgsqladmin@geoff.dj</a>&gt;=
 wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, =
20 Jul 2023 at 15:28, Anthony Apollis &lt;<a href=3D"mailto:anthony.apollis=
@gmail.com" target=3D"_blank">anthony.apollis@gmail.com</a>&gt; wrote:<br>
&gt; I am attaching my TSQL and Postgres SQL:<br>
<br>
You&#39;re still missing some CREATEs, for example for temp_FieldFlowsFact.=
<br>
<br>
Even assuming your columns list is correct, I would still (and as a<br>
matter of habit) include the target column list in your INSERT<br>
statements, if only to make your own life easier, but especially<br>
because any later changes to the tables could end up shifting the<br>
position of the columns.<br>
<br>
Certainly it will be easier for anyone else trying to disentangle the SQL l=
ater.<br>
<br>
As an aside, can you clarify whether you mean temporal tables (and are<br>
you using a temporal tables extension)? Or should that read<br>
&quot;temporary&quot;?<br>
<br>
Geoff<br>
</blockquote></div>

--0000000000003989eb0600ed1049--