ERROR: syntax error at end of input error when trying to save result of sql query to json

Question

I just run this query

SELECT row_to_json(tt) FROM (
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM patients p
  INNER JOIN admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
, age as
(
  SELECT
      subject_id, dob, gender
      , first_admittime, first_admit_age
      , CASE
          -- all ages > 89 in the database were replaced with 300
          -- we check using > 100 as a conservative threshold to ensure we capture all these patients
          WHEN first_admit_age > 100
              then '>89'
          WHEN first_admit_age >= 14
              THEN 'adult'
          WHEN first_admit_age <= 1
              THEN 'neonate'
          ELSE 'middle'
          END AS age_group
  FROM first_admission_time
)
select age_group, gender   , count(subject_id) as NumberOfPatients from age group by age_group, gender ) tt;

she works fine, and this is the output:

                          row_to_json                          
--------------------------------------------------------------  
{"age_group":"adult","gender":"F","numberofpatients":15476} 
{"age_group":"neonate","gender":"M","numberofpatients":4245} 
{"age_group":">89","gender":"F","numberofpatients":1294} 
{"age_group":"neonate","gender":"F","numberofpatients":3629} 
{"age_group":"adult","gender":"M","numberofpatients":21179} 
{"age_group":">89","gender":"M","numberofpatients":697} 
(6 rows)

after that I just try to export the result to json format by tapping :

\copy (
SELECT row_to_json(tt) FROM (
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM patients p
  INNER JOIN admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
, age as
(
  SELECT
      subject_id, dob, gender
      , first_admittime, first_admit_age
      , CASE
          -- all ages > 89 in the database were replaced with 300
          -- we check using > 100 as a conservative threshold to ensure we capture all these patients
          WHEN first_admit_age > 100
              then '>89'
          WHEN first_admit_age >= 14
              THEN 'adult'
          WHEN first_admit_age <= 1
              THEN 'neonate'
          ELSE 'middle'
          END AS age_group
  FROM first_admission_time
)
select age_group, gender   , count(subject_id) as NumberOfPatients from age group by age_group, gender ) tt
) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/PatientAgeAndMortality.json';

I just add copy\ ( .....) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/PatientAgeAndMortality.json';

this is the error that I found :

ERROR: syntax error at end of input

LINE 1: ...nts from age group by age_group, gender ) test ) TO STDOUT;

The json file is created but empty PS: I just run this command which also save result in a json file and she works fine :

\copy (  SELECT row_to_json(test) FROM (SELECT gender, COUNT(*) FROM patients GROUP BY gender ) test) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/test.json';

I don't understand the problem

Please take a look at https://stackoverflow.com/a/29636586/745342 — Ilia Maskov, Oct 24 '19 at 14:54

score 0 · Accepted Answer · answered Oct 24 '19 at 16:09

0

To fix this error, we must put all the query in a single line.

Thank you @IliaMaskov for the reference Postgres: \copy syntax error in .sql file .

answered Oct 24 '19 at 16:09

el abed houssem

350
1
7
16

ERROR: syntax error at end of input error when trying to save result of sql query to json

1 Answers1