Dear Team,
Iam trying to migrate data oracle to postgres, I am using conversion tool ora2pg, data is converted, but I am dumping data into postgres I will get following error message,
Password:
SET
SET
ERROR: syntax error at or near "id"
LINE 7: FROM category a JOIN cte c ON (c.prior id
^
=================================================
[postgres@localhost daily_jagran]$ cat VIEW_output_daily_jagran.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=prdb.jagran.com;sid=orcl;port=1521
SET client_encoding TO 'UTF8';
SET search_path = daily_jagran;
\set ON_ERROR_STOP ON
CREATE OR REPLACE VIEW category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,1 as level
FROM category a
WHERE parent_id is null
UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,(c.level+1)
FROM category a JOIN cte c ON (c.prior id
UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)
) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION
select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1
order by 1
;
CREATE OR REPLACE VIEW hp_main_slider (article_id, tags, created_date, language_id, title, bigtitle, summary, slide_path, display_title, thumbnail_path, article_priority, rank) AS SELECT ARTICLE_ID,TAGS,CREATED_DATE,LANGUAGE_ID,TITLE,BIGTITLE,SUMMARY,SLIDE_PATH,DISPLAY_TITLE,THUMBNAIL_PATH,ARTICLE_PRIORITY,RANK
FROM (SELECT a.id article_id,
a.tags,
TO_CHAR(b.created_date,'dd Mon, yyyy') created_date,
b.language_id,
d.title,
b.title bigtitle,
b.summary,
d.path slide_path,
b.comments display_title,
d.thumbnail_path,
a.article_priority,
rank() over (order by CASE WHEN d.modified_date='' THEN d.created_date ELSE d.modified_date END DESC) rank
FROM article a,
article_language b,
article_media d
WHERE a.id = b.article_id
AND a.id = d.article_id
AND a.IS_ACTIVE = 1
AND a.is_deleted = 0
AND a.deleted_date IS NULL
AND b.IS_ACTIVE = 1
AND b.is_deleted = 0
AND b.deleted_date IS NULL
AND d.IS_ACTIVE = 1
AND d.is_deleted = 0
AND d.DELETED_DATE IS NULL
AND d.media_type_id = 4
AND (b.expiry_date > LOCALTIMESTAMP
OR b.EXPIRY_DATE IS NULL)
AND a.ARTICLE_PRIORITY = userenv('client_info')
order by rank
) alias5
WHERE RANK <= 1;
CREATE OR REPLACE VIEW topic_category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,1 as level
FROM category a
WHERE id =1296817087
UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,(c.level+1)
FROM category a
JOIN cte c ON (c.prior id
UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)
) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION
select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1
order by 1;
[postgres@localhost daily_jagran]$
-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=prdb.jagran.com;sid=orcl;port=1521
SET client_encoding TO 'UTF8';
SET search_path = daily_jagran;
\set ON_ERROR_STOP ON
CREATE OR REPLACE VIEW category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,1 as level
FROM category a
WHERE parent_id is null
UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,(c.level+1)
FROM category a JOIN cte c ON (c.prior id
UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)
) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION
select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1
order by 1
;
CREATE OR REPLACE VIEW hp_main_slider (article_id, tags, created_date, language_id, title, bigtitle, summary, slide_path, display_title, thumbnail_path, article_priority, rank) AS SELECT ARTICLE_ID,TAGS,CREATED_DATE,LANGUAGE_ID,TITLE,BIGTITLE,SUMMARY,SLIDE_PATH,DISPLAY_TITLE,THUMBNAIL_PATH,ARTICLE_PRIORITY,RANK
FROM (SELECT a.id article_id,
a.tags,
TO_CHAR(b.created_date,'dd Mon, yyyy') created_date,
b.language_id,
d.title,
b.title bigtitle,
b.summary,
d.path slide_path,
b.comments display_title,
d.thumbnail_path,
a.article_priority,
rank() over (order by CASE WHEN d.modified_date='' THEN d.created_date ELSE d.modified_date END DESC) rank
FROM article a,
article_language b,
article_media d
WHERE a.id = b.article_id
AND a.id = d.article_id
AND a.IS_ACTIVE = 1
AND a.is_deleted = 0
AND a.deleted_date IS NULL
AND b.IS_ACTIVE = 1
AND b.is_deleted = 0
AND b.deleted_date IS NULL
AND d.IS_ACTIVE = 1
AND d.is_deleted = 0
AND d.DELETED_DATE IS NULL
AND d.media_type_id = 4
AND (b.expiry_date > LOCALTIMESTAMP
OR b.EXPIRY_DATE IS NULL)
AND a.ARTICLE_PRIORITY = userenv('client_info')
order by rank
) alias5
WHERE RANK <= 1;
CREATE OR REPLACE VIEW topic_category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,1 as level
FROM category a
WHERE id =1296817087
UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN a.CAT_TYPE_ID ELSE a.parent_id END parent_id,(c.level+1)
FROM category a
JOIN cte c ON (c.prior id
UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)
) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION
select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1
order by 1;
[postgres@localhost daily_jagran]$
Kindly check and revert back, the issue.
Regards,
Mallikarjunarao,
+91-8142923383.
On Mon, Oct 28, 2019 at 6:34 PM Peter Eisentraut <peter.eisentraut@xxxxxxxxxxxxxxx> wrote:
On 2019-10-28 01:01, Jeff Janes wrote:
> On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@xxxxxxxxxxxxxx
> <mailto:setve@xxxxxxxxxxxxxx>> wrote:
>
> I can execute the create user or create db .etc... command at a
> PostgreSQL interactive terminal so why are these commands
> "createuser , createdb , dropdb etc.... separately and what is their
> purpose?
> <https://www.reddit.com/r/PostgreSQL/comments/dnyr0n/i_can_execute_the_create_user_or_create_db_etc/>
>
>
> When scripting the initial set up of a system, it is easier to pass the
> name to one of these programs, then to embed them into the middle of an
> SQL command properly escaped and quoted. (The names of the programs
> themselves are ancient history, if starting from scratch they probably
> begin with "pg_")
Also, in the distant past, there was no CREATE USER command and the
createuser program inserted directly into pg_shadow.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
