[SQL] Recursive SELECT problem
Hi, I'm trying to write a query to determine whether or not the current login role has CREATEDB (or CREATEROLE) privileges, either directly or inherited from a parent role. The query: SELECT EXISTS (SELECT 1 FROM pg_authid WHERE rolcreatedb = TRUE AND pg_has_role(rolname, 'MEMBER')) AS has_createdb does the job for CREATEDB, except that it doesn't honour rolinherit. Anyone know how I can do this in pure SQL? Thanks, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Recursive SELECT problem
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 17 May 2006 15:07 > To: Dave Page > Cc: [email protected] > Subject: Re: [SQL] Recursive SELECT problem > > "Dave Page" writes: > > I'm trying to write a query to determine whether or not the current > > login role has CREATEDB (or CREATEROLE) privileges, either > directly or > > inherited from a parent role. > > Those privileges don't inherit, so I'm not sure why you need > a recursive check. Because I didn't realise they didn't inherit 'cos I missed the note at the bottom of the CREATE ROLE page :-) Thanks - that makes life significantly easier! Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] some error when executing query in pgAdmin tool
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P.Sent: 23 May 2006 13:11To: [email protected]: [SQL] some error when executing query in pgAdmin tool I created one table in pgAdmin tool but when I am executing query it is giving error…. CREATE TABLE "ADV" ( "T-Section_Id" varchar(10) NOT NULL, "CDA_No" varchar(7) NOT NULL, "Imp_Schedule_Id" int4 NOT NULL, "Sanction_No" varchar(20) NOT NULL, "Sanction_Date" date NOT NULL, "Station_From" varchar(20) NOT NULL, "Station_To" varchar(20) NOT NULL, "Amt_Claimed" int4, "Amt_Admitted" int4, "Dak_Id" varchar(20) NOT NULL, "Refund_Dak_Id" int4 NOT NULL, "T-Wing_Allowance_Id" varchar(10) NOT NULL, CONSTRAINT "ADV_pkey" PRIMARY KEY ("T-Section_Id") ) WITHOUT OIDS; ALTER TABLE "ADV" OWNER TO postgres; Above table I created….. Select * from ADV; when I am executing this query I am getting error is Relation ADV does not exist… like this error is giving … may I know y am I getting this error Try SELECT * FROM "ADV"; pgAdmin quoted the tablename because you used uppercase characters. Without the quotes, the name is folded to lowercase. Regards, Dave.
Re: [SQL] some error when executing query in pgAdmin tool
From: Penchalaiah P. [mailto:[EMAIL PROTECTED] Sent: 23 May 2006 13:18To: Dave PageSubject: RE: [SQL] some error when executing query in pgAdmin tool Hi sir… I tried like that also … but I am getting same error……I tried like these…… 1).Select * from adv; 2). Select * from “adv”; 3).Select * from public.adv; here public is the schema name Like I said, try: SELECT * FROM "ADV"; You created the table with a quoted, uppercase name, so that is how you must reference it. Regards, Dave
Re: [SQL] hi can u give solution to this query
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P.Sent: 23 May 2006 13:41To: [email protected]: [SQL] hi can u give solution to this query Hi sir… If u don’t mind can u reply for this question This is one table… it has one primary key…….so I can insert the values from data edit… 1)…CREATE TABLE "ADV" ( "T-Section_Id" varchar(10) NOT NULL, "CDA_No" varchar(7) NOT NULL, "Imp_Schedule_Id" int4 NOT NULL, "Sanction_No" varchar(20) NOT NULL, "Sanction_Date" date NOT NULL, "Station_From" varchar(20) NOT NULL, "Station_To" varchar(20) NOT NULL, "Amt_Claimed" int4, "Amt_Admitted" int4, "Dak_Id" varchar(20) NOT NULL, "Refund_Dak_Id" int4 NOT NULL, "T-Wing_Allowance_Id" varchar(10) NOT NULL, CONSTRAINT "ADV_pkey" PRIMARY KEY ("T-Section_Id") ) WITHOUT OIDS; ALTER TABLE "ADV" OWNER TO postgres; 2) this table doesn’t contain primary key … but I cant insert values from data edit… CREATE TABLE "ADV_LTC" ( "T-Section_Id" varchar(20) NOT NULL, "Ltc_Block_Year" date NOT NULL, "Ltc_Type" varchar(10) NOT NULL, "Leave_Period_From" date NOT NULL, "Leave_Period_To" date NOT NULL, "Date_Journey" date NOT NULL, "Travelling" varchar(10) NOT NULL, "No_Child" int4, "No_Adult" int4, "Child_Fare" int4, "Adult_Fare" int4, "Journey_Fare" int4, "Amt_Clm" int4 NOT NULL, "Amt_Adm" int4, "Remarks" varchar(40) ) WITHOUT OIDS; ALTER TABLE "ADV_LTC" OWNER TO postgres; Can u check it y I am unable to insert values to 2nd table………. You have answered your own question. pgAdmin won't allow you to edit a table without either OIDs or a Primary Key because it has no way to identify a row (which many would say is a broken design). Regards, Dave.
Re: [SQL] MS-SQL<->Postgres sync
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe > Sent: 10 July 2006 20:03 > To: Kevin Bednar > Cc: Forums @ Existanze; [email protected] > Subject: Re: [SQL] MS-SQL<->Postgres sync > > I think there's a version with windows support due out soon. > Not sure, > since I don't run windows... just remember seeing it mentioned. Yes, full support for windows has been written already for the upcoming 1.2 release. Regards, Dave ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] pgAgent stats
On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk wrote: > It's pgAdmin 1.10.0. I've partially identified the problem, pgAdmin log says > that following query gets executed when switching to job statistics tab : > SELECT jsljlgid, jslstatus, jslresult, jslstart, jslduration, (jslstart + > jslduration) AS endtime, jsloutput > FROM pgagent.pga_jobsteplog WHERE jsljstid = 5 ORDER BY jslstart DESC LIMIT > 0 > obviously the problem is LIMIT 0 clause but why it is there remains > a mystery... pgAdmin bug ? a configuration issue ? Check the 'Maximum number of rows to retrieve' option on the Query tab of the Options dialog. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgAgent stats
On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk wrote: > Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to > not limit anything when 0 :)) a bug ? What makes you say that? The docs say: Maximum rows to retrieve - This option specifies the number of job and job step statistics rows to retrieve when viewing the statistics in the main browser. Unlike the statistics for other objects which normally consist of a fixed number of rows, a row is created every time a job or job step is executed. The most recent statistics will be shown. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgAgent stats
:-) On Wed, Mar 17, 2010 at 12:40 PM, Marcin Krawczyk wrote: > Yeah... my bad. Sorry for being a pain in the a... ;) > > pozdrowienia > mk > > > 2010/3/17 Dave Page >> >> On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk >> wrote: >> > Ha, it worked :)) kind of funny though. It was set to 0 but it's >> > supposed to >> > not limit anything when 0 :)) a bug ? >> >> What makes you say that? The docs say: >> >> Maximum rows to retrieve - This option specifies the number of job and >> job step statistics rows to retrieve when viewing the statistics in >> the main browser. Unlike the statistics for other objects which >> normally consist of a fixed number of rows, a row is created every >> time a job or job step is executed. The most recent statistics will be >> shown. >> >> >> -- >> Dave Page >> EnterpriseDB UK: http://www.enterprisedb.com >> PG East Conference: >> http://www.enterprisedb.com/community/nav-pg-east-2010.do > > -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
On Thu, Jul 8, 2010 at 8:31 AM, silly sad wrote: > On 07/07/10 18:35, Justin Graf wrote: >> >> On 7/7/2010 12:00 AM, silly sad wrote: >>> >>> On 07/06/10 21:52, Justin Graf wrote: >>> >>>> I wrote an article covering this on the wiki >>>> >>>> http://wiki.postgresql.org/wiki/BinaryFilesInDB >>>> >>> there are some "red flags" in communication >>> (particularly reading papers) >>> one of them is "binary data" which ITSELF IS NONSENCE. >>> >> >> WHAT??? >> >> You do understand that if you don't like it you can spend time fixing it. > > fix what? human minds of so-called programmers? That is not appropriate behaviour for the PostgreSQL mailing lists. Please treat others with respect, even if you disagree with them. -- Dave Page PostgreSQL Core Team -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
On Wed, Jul 21, 2010 at 7:47 AM, Achilleas Mantzios wrote: > It seems there are certain projects running at the moment that will > eventually make possible > to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). We've been doing that across the project infrastructure for 10 years or more. The only issue we run into is that we need to use a unique port in each jail as shared memory isn't entirely isolated between jails. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Split a string to rows?
On Tue, Jan 8, 2013 at 2:34 PM, Emi Lu wrote: > Hello Thomas, > > >>> Thanks a lot! I just noticed that my postgresql is 8.3(unnest >>> function is not there by default). Is there a way that I could >>> download and load only this function from somewhere? >> >> >> >> Are you aware that 8.3 will be de-suppported as of next month? >> >> You should really think about an upgrade *now* >> > > http://www.postgresql.org/support/versioning/ > > Although 8.3 reaches EOL date, it says that "Supported=Yes" for 8.3. > > I believe that "Supported" would be fine, wouldn't it? It is supported until February (or more precisely, until the next point release after then, which will be the final wrapup release). At that point, there will be no more releases or bug fixes. Start thinking about upgrading now, as Thomas suggests. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Is there anyway to...
imad wrote: Or you can probably use a PostgreSQL administration tool for scheduled jobs. I know a number of such tools which provide this feature and EnterpriseDB Management Server is one of them. As is pgAdmin's pgAgent. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] PgAgent logging verbosity
Ezequias Rodrigues da Rocha wrote: Hi list, I would like to know if I set the debug mode to 2 (-l 2) in the pgAgent connection string it could spend much cpu processing. I have other things running in the server and I don't like to create problem to others. Please stop cross posting to lots of lists! -l 2 is extremely unlikely to cause excessive CPU or IO, but it will clutter logs, so you wouldn't want to leave it on permanently. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [ADMIN] [GENERAL] PgAgent logging verbosity
Ezequias R. da Rocha wrote: Ok I will use only on error ok ? -l 0 :) In the normal case, yes, that is what I'd use (0 is the default in fact, so you can omit the option altogether). Regard,s Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] workday function
Kenneth Gonsalves wrote: > > On 15-May-07, at 2:39 PM, Richard Huxton wrote: > >> Don't know of one - not sure what "workday" would mean in a global >> sense. I mean, Mon-Fri in most European office settings, but you'd >> include Sat in retail settings and in Islamic countries presumably >> exclude Fridays. Our local library shuts early on Mondays iirc but is >> open Saturday mornings. > > and i hear in england people work half day on wednesday > We do? First I heard of it! Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Does postgresql8.2 supports multithreading?
A. Kretschmer wrote: > am Tue, dem 11.09.2007, um 12:38:38 +0530 mailte Jyoti Seth folgendes: >> Hi , >> >> >> >> Does postgresql8.2 supports multithreading? > > No. In the bits that (should) matter to end users (ie. libpq), yes, it does. Regards, Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Bouncing replies
On Feb 11, 2008 5:06 AM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > If mail to my list address doesn't come from one of your list > servers (and those seems to change much more often than any other list I > belong to), it gets rejected. Those must be some *really* long-lived lists. Our mailserver has been mail.postgresql.org for probably 7 or 8 years now. Many moons ago we did use some additional relay servers, but I don't think we've done that for 5+ years. > I'm curious as to why the list server defaults to using a sender's > address as the "Reply-to" address, rather than using the list address. > That means that, unless the user is careful to "reply all" (or even > better, just reply to the list address), any discussion immediately goes > private. Because thats the convention on these lists that people are used to and prefer in general. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Bouncing replies
On Feb 11, 2008 9:30 AM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > > PostgreSQL list serve output has not been connecting from > mail.postgresql.org for a LONG time (see the headers below). Currently > they've been connecting from various hosts at the hub.org domain, but there > have several other non-postgresql.org connecting domains in the past. > Whenever I stop getting PostgreSQL mail, I go check my Postfix logs to see > what the new hostname's domain is, and update my whitelist. Without wishing to sound pedantic, the list server itself which was what you asserted kept changing has in fact not changed. Looking at the headers I see the message has been relayed through the spam filtering at hub.org as well which I didn't realise Marc was doing (I thought he only checked incoming posts), but regardless, all mail should be coming only from hub.org/postgresql.org machines. I'd be interested to see where else you've seen it come from, and when. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
On Mon, Feb 25, 2008 at 9:08 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > > On 2008-02-22 21:34, Scott Marlowe wrote: > > > Bouncing messages from a public list is kinda rude. > > No more so, than sending two copies of your reply to me, because you don't > go up to your mailer's "To:" line and manually delete the extra address (as > I do on EVERY reply I send to this list). It is considered polite to follow the customs of the groups in which one participates. For example, if you meet with Japanese people as part of your work you should bow whilst exchanging business cards, take care to read the card you are given, and not place it in the back pocket of your trousers. In this group we use the mj2 mailing list manager which by default will not send you a direct copy of any message which also has your email address explicitly listed. We prefer to use reply-all when responding to people, and that has become the custom here. It would be appreciated if you respected that custom during your time with us. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] dblinks
On Mon, May 19, 2008 at 4:06 AM, Ramasubramanian G
<[EMAIL PROTECTED]> wrote:
> HI Sumaya,
>
> This is the way you have to use dblink. And one more think. To
> excute this query you nedd to have dblink functions installed in your
> database schema.
>
> select * from dblink('YOUR_DB_LINK_NAME','select * from mytable')as
> tmp(column1 datatype,column2 datatype.)
The OP was using EnterpriseDB's Postgres Plus Advanced Server, which
has Oracle-style dblinks built in. They are not used in the same way
as the PostgreSQL dblink module.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig wrote: > I think this is a good time to propose some kind of CookBook, to > preserve this kind of answers. What, like this one? http://wiki.postgresql.org/wiki/Snippets :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
