pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedthe difference between psql , createdb, dropuser
6+ messages / 5 participants
[nested] [flat]
* the difference between psql , createdb, dropuser
@ 2019-10-27 20:23 Setve <setve@protonmail.com>
2019-10-28 00:00 ` Re: the difference between psql , createdb, dropuser David G. Johnston <david.g.johnston@gmail.com>
2019-10-28 00:01 ` Re: the difference between psql , createdb, dropuser Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Setve @ 2019-10-27 20:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
[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/)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: the difference between psql , createdb, dropuser
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
@ 2019-10-28 00:00 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2019-10-28 00:00 UTC (permalink / raw)
To: Setve <setve@protonmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Sunday, October 27, 2019, Setve <setve@protonmail.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/;
>
>
Its simply an abstraction so one can execute those actions in a shell
without including and dealing with raw SQL - thus removing SQL-injection
exposure.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: the difference between psql , createdb, dropuser
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
@ 2019-10-28 00:01 ` Jeff Janes <jeff.janes@gmail.com>
2019-10-28 13:04 ` Re: the difference between psql , createdb, dropuser Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2019-10-28 00:01 UTC (permalink / raw)
To: Setve <setve@protonmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.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_")
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: the difference between psql , createdb, dropuser
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
2019-10-28 00:01 ` Re: the difference between psql , createdb, dropuser Jeff Janes <jeff.janes@gmail.com>
@ 2019-10-28 13:04 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-10-29 12:11 ` Re: the difference between psql , createdb, dropuser mallikarjun t <mallit333@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2019-10-28 13:04 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Setve <setve@protonmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 2019-10-28 01:01, Jeff Janes wrote:
> On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.com
> <mailto:setve@protonmail.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: the difference between psql , createdb, dropuser
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
2019-10-28 00:01 ` Re: the difference between psql , createdb, dropuser Jeff Janes <jeff.janes@gmail.com>
2019-10-28 13:04 ` Re: the difference between psql , createdb, dropuser Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2019-10-29 12:11 ` mallikarjun t <mallit333@gmail.com>
2019-10-29 16:37 ` Re: the difference between psql , createdb, dropuser David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: mallikarjun t @ 2019-10-29 12:11 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@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@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]$
Kindly check and revert back, the issue.
Regards,
Mallikarjunarao,
+91-8142923383.
On Mon, Oct 28, 2019 at 6:34 PM Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
> On 2019-10-28 01:01, Jeff Janes wrote:
> > On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.com
> > <mailto:setve@protonmail.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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: the difference between psql , createdb, dropuser
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
2019-10-28 00:01 ` Re: the difference between psql , createdb, dropuser Jeff Janes <jeff.janes@gmail.com>
2019-10-28 13:04 ` Re: the difference between psql , createdb, dropuser Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-10-29 12:11 ` Re: the difference between psql , createdb, dropuser mallikarjun t <mallit333@gmail.com>
@ 2019-10-29 16:37 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2019-10-29 16:37 UTC (permalink / raw)
To: mallikarjun t <mallit333@gmail.com>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Jeff Janes <jeff.janes@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Oct 29, 2019 at 5:12 AM mallikarjun t <mallit333@gmail.com> wrote:
> 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,
>
Maybe try sending a brand new email with a proper subject line and to an
appropriate list (probably ora2pg since the query you says its generating
simply isn't valid in PostgreSQL).
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-10-29 16:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-27 20:23 the difference between psql , createdb, dropuser Setve <setve@protonmail.com>
2019-10-28 00:00 ` David G. Johnston <david.g.johnston@gmail.com>
2019-10-28 00:01 ` Jeff Janes <jeff.janes@gmail.com>
2019-10-28 13:04 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2019-10-29 12:11 ` mallikarjun t <mallit333@gmail.com>
2019-10-29 16:37 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox