Re: the difference between psql , createdb, dropuser

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,

[postgres(at)localhost daily_jagran]$ psql < VIEW_output_daily_jagran.sql
Password:
SET
SET
ERROR: syntax error at or near "id"
LINE 7: FROM category a JOIN cte c ON (c.prior id
^
=================================================

[postgres(at)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(at)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(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 2019-10-28 01:01, Jeff Janes wrote:
> > On Sun, Oct 27, 2019 at 4:23 PM Setve <setve(at)protonmail(dot)com
> > <mailto:setve(at)protonmail(dot)com>> 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
>
>
>