On 10/30/2015 09:55 AM, Dane Foster wrote:
On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 10/30/2015 09:36 AM, Dane Foster wrote:
On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>> wrote:
On 10/30/2015 08:13 AM, Dane Foster wrote:
On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>> wrote:
On 10/30/2015 07:21 AM, Dane Foster wrote:
On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>>> wrote:
On 10/29/2015 05:38 PM, Dane Foster wrote:
Hello,
I think I've tripped over another
mysq_fdw
bug. I've
filed a bug
report
on github already but just in case
the problem
is w/ my
query I
figured
I would post it here in case someone sees
something
obvious.
The error message I get is: null
value in column
"location" violates
not-null constraint.
The DDL is here:
https://github.com/EnterpriseDB/mysql_fdw/issues/71
For the record I know top posting is
a crime
against
god and
humanity
but I feel justified because this
post is not
directly
related
to the
original. So there! Granted it's in
the same
milieu;
and yes this
current sentence exists for the sole
purpose of me
being able to
use the
word milieu because the opportunity
to use it
is so few
and far
between.
INSERT INTO series (cid, day, title,
description,
location,
duration,
can_join)
SELECT
cid,
row_number() OVER (PARTITION BY
cid ORDER BY
lower(duration)),
title,
description,
location,
duration,
can_join
FROM (
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations
WHERE
loc=location) AS
location,
('[' || starts || ', ' ||
(starts +
INTERVAL '4
HOUR') ||
']')::TSZ_PERIOD AS duration
FROM
_series
) AS v
Regards,
So what do you get when you do?:
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE
loc=location) AS
location,
('[' || starts || ', ' || (starts
+ INTERVAL '4
HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
);
Dane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>>
I get rows of data, location and all.
And when you do?:
SELECT
cid,
row_number() OVER (PARTITION BY cid ORDER BY
lower(duration)),
title,
description,
location,
duration,
can_join
FROM (
SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE
loc=location) AS
location,
('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') ||
']')::TSTZRANGE AS duration
FROM
_series
) AS v
Dane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>>
Before I answer your second query question I need to
revise my
response
to the first. Yes the first query runs w/o an error
message but
the bit
about "rows and all" was not entirely correct. Out of
313 rows
only the
first row had a location. The other 312 rows have NULL
in the
location
column which is not supposed to happen. To verify this
I changed the
table names and removed the PostgreSQL transformations
(i.e.,
use of ||
and :: for casting) and ran the query against the MySQL
database; it
returned 313 rows of data, location and all.
You would think that would also cause an issue with the
first row
that is returned correctly. My suspicion is with this:
row_number() OVER (PARTITION BY cid ORDER BY lower(duration))
What happens if you run the full SELECT without it?
Now that I've cleared that up.
Your second query also runs w/o any error messages but
like the
first
only the first row has a non NULL value in the location
column.
Dane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>
Your first query didn't use it and as discussed rows come back
but only
the first row has a non NULL location column.
Forgot about that. Where I was going with this is that duration
comes from:
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
and MySQL and Postgres have different ideas about timestamps. While
I thinking about what that meant in the context of the query I
realized I was stepping over the obvious:
SELECT label FROM _locations WHERE loc=location
So what does the below show:
SELECT label FROM _locations, _series WHERE loc=location;
Dane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
It returns all the locations.
You know where we are going:
SELECT label, starts, ('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') || ']')::TSTZRANGE AS duration FROM _locations, _series WHERE
loc=location;
Dane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
