Re: [SQL] begin update ... syntax error
On Saturday 10 April 2004 02:32 pm, Kemin Zhou wrote: begin; Note the semi-colon Then the update query; commit; or rollback; > I was trying to speed up a simple update query > > fri=# begin > fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend > fri-# from tmp > fri-# where tabA.acc=tmp.acc; > ERROR: syntax error at or near "update" at character 7 > > The same query can be run with no problem if not starting with BEGIN. > > Does mean that BEGIN cannot preceed UPDATE? > > Or I am making some obvious miskate? > > Kemin > > > > > ** > Proprietary or confidential information belonging to Ferring Holding SA or > to one of its affiliated companies may be contained in the message. If you > are not the addressee indicated in this message (or responsible for the > delivery of the message to such person), please do not copy or deliver this > message to anyone. In such case, please destroy this message and notify the > sender by reply e-mail. Please advise the sender immediately if you or your > employer do not consent to e-mail for messages of this kind. Opinions, > conclusions and other information in this message represent the opinion of > the sender and do not necessarily represent or reflect the views and > opinions of Ferring. > ** > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] ERROR: Invalid regular expression: parentheses ( ) not balanced
On Wednesday 25 August 2004 07:21 pm, Tom Lane wrote:
> "Kathrine S" <[EMAIL PROTECTED]> writes:
> > Below is a copy of my sql sentence including the error I am getting. What
> > does the error mean? What have I done wrong?
> >
> > kathrirs=# insert into faglaerer
> > kathrirs-# values ('f-001', '13056802876', 'Petter Lær',
> > lo_import('/home/studenter/it03/kathrirs/img/img01.jpg'),<1
> > kathrirs(# '2001-12-25', 100, 'Professor', 'A', 1,<--2
> > '{"linux","programmering","matematikk","neutrale nettverk"}',
> > kathrirs(# 12, '[EMAIL PROTECTED]', '8212');
> > ERROR: Invalid regular expression: parentheses ( ) not balanced
>
> There's no regular expression in what you've shown us. Maybe you have
> rules or triggers that are fired by this INSERT? If so, you need to
> look at what they are doing.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
I don't know what to make of it but the problem seems to start at the lines I
have marked 1 & 2 and involves the lo_import function. I have not used
lo_import and so do not know how to call it. What I do see is that it is
called at 1 and a '(' shows up to the left of the prompt at 2 and stays
there indicating to me at least the parser is not happy.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
On Sunday 20 November 2005 09:15 am, Andy Ballingall wrote: It works because of the way updates are done. When you do an update two versions of the row exist. The OLD version is the row as it existed before you updated. The NEW version contains the entire version with the update changes. The key thing to remember is the the NEW version contains both those fields that have changed as well as those that have not. So the UPDATE rule just passes along all the fields named in it regardless of whether they changed or not. It would be a good idea to read the following section of the manual (http://www.postgresql.org/docs/8.0/interactive/rules.html) as it explains when the rule picks up the values in the fields. This differs according to the type of rule. > >Try it. [snipped example] > > Ah. Basically, you set up the rule to assign every column, and if the > update doesn't redefine some columns, then it still works. I didn't > understand that you could get the rule to work generically like this. > > I'll presume that the rule will need amending if the table column > definition later changes. (E.g. if I add 'stalk_length' to my apples and > pears tables)... > > Thanks very much for your help. > > Andy > > > > ---(end of broadcast)------- > TIP 5: don't forget to increase your free space map settings -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
On Monday 16 January 2006 05:55 pm, Daniel CAUNE wrote:
> > -Message d'origine-
> > De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> > [EMAIL PROTECTED] De la part de John DeSoi
> > Envoyé : lundi 16 janvier 2006 08:51
> > À : Daniel CAUNE
> > Cc : pgsql-sql@postgresql.org
> > Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible?
> >
> > On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
> > > I would like to write some administration plpgsql scripts that
> > > populate some tables (dimension tables) and to execute them using
> > > psql. I’m not sure that is possible with psql as it is with Oracle
> > > sqlplus or SQL Server MSQuery:
> >
> > If you want to execute a plpgsql function from a file using psql,
> > just call it with SELECT. So your file might have:
> >
> > create or replace function my_function(params integer)
> > returns integer as $$
> > DECLARE
> >V_MyObjectID bigint;
> > BEGIN
> >V_MyObjectID := RegisterMyObject('a string', 'another string');
> >AddObjectProperty(V_MyObjectID, 'a string');
> >AddObjectProperty(V_MyObjectID, 'another string');
> >
> > END;
> > $$ language plpgsql;
> >
> >
> > SELECT my_function(1);
> >
> >
> > and then psql -f script.sql my_db
>
> Yes, but that requires creating a function while I would prefer not having
> do so, as I said in my previous mail: "I mean, without creating a function
> that wraps the whole, of course! :-)". Why? Actually this is not a
> function; this is a script that inserts static data into dimension tables
> such as Country, Language, etc.
>
> I have several scripts responsible for creating the database and all the
> objects (tables, views, constraints, indexes, user-defined functions, etc.)
> of my project. I would like to have some other scripts to initialize
> dimension tables, i.e. inserting static data in those tables. The idea is
> to automate the whole creation and initialization of a database on a
> PostgreSQL server; I already have an Ant task that searches for SQL files,
> orders them, and runs them against the specified database server. The
> database and all relative objects are set up in one step.
>
> So, I completely understand that I can write an SQL script that:
>
> 1 - creates a function that wraps SQL code that inserts static data into
> dimension tables. 2 - executes that function
> 3 - destroys that function
>
> But actually that is a bit weird, isn't it?
>
> Thanks,
>
>
> Daniel
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
If you want to use plpgsql it will need to be within a function. In your reply
you mention creating user-defined functions as part of the set up procedure.
It would not be weird to include the static data function as part of that
procedure and then call it to load the data. I see no reason to destroy the
function after use. If that is not the route you want to take you may want to
look at the following for information on using COPY to load data from a file
into a table-
www.postgresql.org/docs/8.1/interactive/sql-copy.html
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Trigger, record "old" is not assigned yet
For plpgsql use TG_OP. See link below. http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html On Thursday 13 July 2006 03:50 pm, Daniel Caune wrote: > Hi, > > > > I've created a trigger BEFORE INSERT OR UPDATE on a table and, indeed, > when the trigger is raised before insertion the record "old" is not > assigned. Is there a way to distinguish in the trigger procedure from > an insert statement to an update statement? > > > > Regards, > > > > > > -- > > Daniel CAUNE > > Ubisoft Online Technology > > (514) 490 2040 ext. 3613 -- Adrian Klaver [EMAIL PROTECTED] ---(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] Bit by "commands ignored until end of transaction block" again
On Thursday 23 July 2009 12:39:23 am Glenn Maynard wrote: > > The ORM on a whole is decent, but there are isolated areas where it's > very braindamaged--this is one of them. They have a stable-release > API-compatibility policy, which I think just gets them stuck with some > really bad decisions for a long time. > > -- > Glenn Maynard None of the options listed in the URL below work?: http://docs.djangoproject.com/en/dev/topics/db/transactions/#topics-db-transactions This is the development version of the docs so may contain some new options. In particular look at Savepoint rollback and Database-level autocommit. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
On Wednesday 12 August 2009 6:27:25 am Jan Verheyden wrote: > Hi, > > I got a bit stuck... > I was looking for a solution for option a) > > Maybe I'll first explain the situation a bit more: > > I have one database for patient registration > Another one for image storage > And a third one for book keeping > A patient should be registered first before the images are stored, so if > there is a new line in the second database with an id which does not exist > yet, it has to be notified in the book keeping database. > > Now the questions: > 1) Can I do this with the inner join (tables subject_id from DB1, pat_id > from DB2), there it is two different databases 2) Once it is notified in > the book keeping that is not registered yet, is it best to poll on this > column to send a warning, or use a trigger?? > > Thanks!! > If at all possible, try to move all that information into schema's of one database. As it stands now you have a lot of moving parts to keep track of via external processes. It is possible but you lose transactional support and trust me that turns into a royal pain. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Serious problems with non-primary foreign keys
On Friday 11 September 2009 6:26:13 am Janne wrote: > I'm having some serious problems using PostgreSQL with symfony and > Doctrine. Here is the error that I've been fighting with for the last > couple of days: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: > current transaction is aborted, commands ignored until end of transaction > block. Failing Query: CREATE INDEX company_id ON sf_guard_user_profile > (company_id) It seems to affect every single non-primary foreign key. > Turning the key to primary foreign fixes the problem but I don't want it to > be primary. I can't think of any logical reason why this error would > happen. Generally there's nothing wrong with using a non-primary (not null) > foreign key. But Postgre seems to think there is. I know PostgreSQL creates > an index for every primary key. Since turning the key also into primary > seems to fix it, should I just define an index for it? I've tried it but > nothing changes. Here's the relevant SQL: CREATE TABLE > sf_guard_user_profile (id BIGINT, company_id BIGINT NOT NULL, PRIMARY > KEY(id)); CREATE TABLE company (company_id INT, PRIMARY KEY(company_id)); > Any ideas on how to fix this? Thank you, > janneaa I think more information is required. First the complete transaction. Second the complete error message, especially the part that explains what is causing the CREATE INDEX command to fail. Third I do not see a FOREIGN KEY reference in the SQL you posted. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules, functions and RETURNING
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
> Hello list,
>
> I am trying to wirte a rule which calls a PLPgSQL-function upon an
> Insert in a table. Here is a somewhat simplified example of what i got
> so far:
>
> CREATE TABLE mytable (
> mytable_id serial PRIMARY KEY,
> something text
> );
>
>
> CREATE OR REPLACE FUNCTION _rule_insert_my(something text)
> RETURNS integer AS
> $BODY$
> BEGIN
> -- do something
> return mytable_id;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
>
> CREATE OR REPLACE RULE _insert AS
> ON INSERT TO mytable DO INSTEAD SELECT
> _rule_insert_my(new.something) AS mytable_id;
>
>
> So far this works quite well. But I got a few situations where I need to
> do a query which uses RETURNING to get the value of the newly generated
> primary key. Like this one:
>
> INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
>
> This breaks because I did not specify a RETURNING-Clause in the rule.
> But how can specify RETURNING with SELECT?
>
>
> Thank your in advance for your help.
>
> regards,
> nico
>
> --
> Nico Mandery
I am going to assume that '--do something' is more complicated then getting the
mytable_id. If that is the case why not create an INSERT function/trigger that
does the 'something' and then just do:
INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote: > Hello, list. > > I have a table with a varchar field that I would like to order by word, not > by ordinal, which seems to be the default on postgres. > > Does anyone have a clue on how this can be done? > > Many thanx in advance, > > Best, > Oliveiros Can you show the SQL you are using? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote: > Hello, Tom. > > Thank you for your e-mail > > Even though I am not familiar with the exact procedure to switch to other > locales, > I'm gonna research this more deeply. > > Anyway, I would appreciate if you could spare me some time and give me a > couple of pointers to this subject. > > Again, thank you > > Best, > Oliveiros > A good place to start is: http://www.postgresql.org/docs/8.4/interactive/locale.html Check out the Further Reading section at: http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078 -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sqldf - error message
On Friday 20 November 2009 6:43:53 am Pavel Stehule wrote:
> 2009/11/20 Tom Lane :
> > "Marvelde, Luc te" writes:
> >> If I run this SQL query:
> >>> sqldf("SELECT
> >>
> >> + dbo_tbl_Terrein.RingCentraleNaam,
> >> + dbo_tbl_Broedsels.BroedselID
> >> + FROM ((dbo_tbl_BroedselLocatie
> >> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
> >> dbo_tbl_Broedsels.BroedselID)
> >> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =
> >> dbo_tbl_Nestkasten.NestkastID)
> >> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
> >> dbo_tbl_Terrein.TerreinNummer
> >> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));")
> >>
> >> I get the following message:
> >>
> >> Error in sqliteExecStatement(con, statement, bind.data) :
> >> RS-DBI driver: (error in statement: no such column:
> >> dbo_tbl_Broedsels.BroedselID)
> >
> > It looks to me like sqldf is unaware of the rules about identifier
> > case-folding in Postgres. That column would come back named
> > "broedselid", but it's probably looking for "BroedselID".
> > Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
> > to be included in the returned column name. Either way, you
> > need to bug sqldf's authors to fix it.
>
> is it Postgres? I see "Error in sqliteExecStatement"
It is more complicated than that see:
http://code.google.com/p/sqldf/
It is a framework sqldf over a framework rsqlite over sqlite. The data frames
the OP is talking about are variables that are coerced to being 'tables'. I can
see no mailing list for sqldf itself. The closet I could come is
https://stat.ethz.ch/mailman/listinfo/r-help
which seems to have quite a few discussions on sqldf and would probably be the
better place to ask this question.
>
> regards
> Pavel Stehule
>
> > regards, tom lane
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Schema's, roles and privileges
On Monday 30 November 2009 6:50:27 am Michael Gould wrote: > I have a database with a schema called ISS. This is where all of our > application definitions are stored. We did add 2 contribute modules > (citext) and guid generator and both of these by default went to the public > schema. It is our intent to not allow any access to public by our users. > > A few questions > > 1. Can I reinstall the contrib modules in the ISS schema only or do they > need to be in the public schema > > 2. If they need to stay in the public schema and I don't want to give any > insert, update, delete or select access to public, can I revoke those > privileges and just give execute on the functions that were added by the > contrib module. > > 3. If I can reinstall the contrib modules in the application schema, can I > delete the public schema or does it still need to be there and I would just > revoke all except for the superuser id which would be for our installer or > tech support if needed. We have a separate userid for the security > administrator. All of the functions that the security administrator needs > are provided by a application module and they will not be directly > accessing the database via a SQL utility at all. > > Best Regards > > > -- > Michael Gould, Managing Partner > Intermodal Software Solutions, LLC > 904.226.0978 > 904.592.5250 fax >From a quick look it would seem the easiest solution would be to change the search_path in: citext.sql.in uuid-ossp.sql.in These files are found in the respective contrib directories. Uninstall the modules. Rerun make and then reinstall. From here: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html "There is nothing special about the public schema except that it exists by default. It can be dropped, too. " -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql loop question
On 02/10/2010 08:29 AM, Andrea Visinoni wrote: hi, i have a table called "zones": idzone, zone_name and several tables called zonename_records (same structure), where zonename is one of the zone_name in the "zones" table. What i want to do is a function that union all of this tables dinamically based on "zones" table, this is what i've done so far: CREATE OR REPLACE FUNCTION get_all_records() RETURNS SETOF record AS $BODY$DECLARE zones record; recs record; BEGIN for zones in select lower(zone_name) as n from zones loop for recs in select * from quote_ident(zones.n || '_records') loop return next recs; end loop; end loop; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; but i get this error! ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "get_all_records" line 9 at RETURN NEXT Andrea One thing I would do is rename your zones record variable. pgsql does not deal well with a variable having the same name as a schema object, in this case your table zones. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Please delete my email
On 03/15/2010 10:18 AM, Eduardo Palafox wrote: Hi!, I don't want to receive more emails from postgresql. Please remove my email from your delivery list. Thanks _ Prefiero un día sin coche que sin Messenger www.vivirmessenger.com To unsubscribe go here: http://www.postgresql.org/mailpref/pgsql-sql Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Remove my e-mail
On 03/15/2010 10:24 AM, Daniel Guedes wrote: Hi!, I don't want to receive more emails from postgresql. Please remove my email from your delivery list. Thanks To unsubscribe go here: http://www.postgresql.org/mailpref/pgsql-sql Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorry, now with subject... trigger & nextval(seq)
On Thursday 20 May 2010 5:53:51 pm tla...@gwdg.de wrote:
> Hello all,
>
> I'm new to triggers in PostgreSQL. I have to create a trigger on insert to
> increment a sequence to overcome MS-Access' limitation in acknowledging
> serial "datatype".
>
> Could anyone put me on right track? I was looking the whole day for
> references on that... Years ago I quickly found a reference how to do it
> in Oracle. However, I could not find anything that explained how to do
> this in postgresql... I think, it should go the direction see below... But
> how exactly :-/ ?
>
>
> Many thanks for any help, Torsten
>
>
> create table testtab (
> pid bigint,
> sometext text
> );
>
> create sequence test;
>
> -- hmm something like this...?
> CREATE FUNCTION count_up (varchar) RETURNS opaque AS '
> DECLARE
> pid_num bigint;
> BEGIN
> select into pid_num from select nextval($);
> RETURN pid_num;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- and how will the trigger looks like
> ???
You know serial is just a shortcut for:
pid int NOT NULL DEFAULT nextval('seq')
I think you will find Access will place nice if you use the long form to define
your autoincrement.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
On 06/23/2010 02:01 PM, bruno.scovoli wrote: I have an script with many "\copy" commands. For example: \copy privilegios from '/work/eclipse-workspace/Canoan Server/database/load/privilegios.dat' \copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan Server/database/load/privilegios_de_usuarios.dat' \copy classificacoes from '/work/eclipse-workspace/Canoan Server/database/load/classificacoes.dat' \copy tipo_de_produto from '/work/eclipse-workspace/Canoan Server/database/load/tipos.dat' This lines are OK. But when I remove the preceding backslash from them I get the error that you mentioned. Harold A. Giménez Ch. wrote: Hi all, In migrating an application from sql server to Postgres, I created a ruby script that extracts csv files from sql server (from a windows box), then SCPs them into a directory (/home/ruby_process) on the server running Postgres (a Fedora core 8) and finally runs the Postgres COPY command for each of the csv files. When the script runs the COPY commnand, I get the following error (for the genders table): ERRORC42501 M could not open file "/home/ruby_process/genders.csv" for reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError) My question is, what are the set of permissions required to perform a postgres copy? I've set the permissions on the directory so that the postgres group owns the directory and has read and execute permissions, as follows: drwxrwxr-x 3 ruby_process postgres I have also tried moving the files to /tmp and performing the copy from there, unsuccesfuly. Any ideas would be appreciated. Thanks, -Harold Are you connecting to the database as a superuser to run the COPY command? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Round integer division
On Friday 25 June 2010 3:53:01 pm Lee Hachadoorian wrote: > Is it documented anywhere that floating-point numbers round > "scientifically", that is 0.5 rounds to the nearest even number? Compare: > > SELECT round(2.5::real), round(2.5::numeric), round(3.5::real), > round(3.5::numeric); > > generates > > 2 | 3 | 4 | 4 > > I stumbled across this when I was trying to use round(a::real/b::real) > to generate a rounded result to dividing integers, and noticed sometimes > 0.5 was truncated and sometimes it was rounded up. Couldn't find > anything about this in the archives or the data type documentation. Is > there something obvious that I'm I missing? > > Thanks, > > -- > > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center See here for explanation: http://archives.postgresql.org/pgsql-general/2010-03/msg00969.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
On Friday 25 June 2010 6:07:35 pm Bruno Scovoli Santos wrote: > * Are you connecting to the database as a superuser to run the COPY > command? * > > Yes. This \copy command is in the database build script (create table > blablabla). So it almost (I think) must have to be a superuser. > > But hey, my intent was just to guide the guy (Harold A. Giménez Ch.) to > follow my practice (to use a preceding backslash). I dont bother to have to > add that preceding backslash in my script. ;-) > > Actually my question was directed to Harold, I put my reply in the wrong place. In any case COPY and \copy are different commands. COPY is done from the server's perspective, \copy from the clients. Hence the permission issues will be different. See below for more detail. http://www.postgresql.org/docs/8.4/interactive/sql-copy.html http://www.postgresql.org/docs/8.4/interactive/app-psql.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
On 11/12/2010 02:03 PM, Tarlika Elisabeth Schmitz wrote: The following command works fine when pasing it to psql via the -c option: cat event.csv | \ psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''" When executed from a file via -f, it does nothing (no error messages either): event.sql: COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''; ^ cat event.csv | psql -f event.sql What's the problem? Many thanks in advance. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help with plpgsql function.
On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote: > > } > > Hello > > you can use a RETURN QUERY statement - some like > > CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int) > RETURNS SETOF RECORD AS $$ > BEGIN > IF i = 1 THEN > RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40; > ELSE > RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90; > END IF; > RETURN; > END; > $$ LANGUAGE plpgsql; > > SELECT * FROM foo(1); > SELECT * FROM foo(2); > > Regards > > Pavel Stehule > FYI the OP is using 8.2 :) RETURN QUERY is 8.3+ -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert from a select
On Wednesday 24 November 2010 4:07:43 pm John Fabiani wrote:
> Hi,
> I have a strange issue that is mostly likely me not understanding
> something. I always thought that an insert statement would accept any
> select statement. I'm guessing I am wrong.
>
> I have created a temporary table ("tempclass") that is exact match to an
> existing table ('esclass').
>
> When I attempt to do the following
> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
>
> I get the following error:
>
> ERROR: column "schedule" is of type date but expression is of type
> character varying
> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
> ^
> HINT: You will need to rewrite or cast the expression.
>
> The error makes no sense to me. But most important if I just run the
> select statement it works perfectly.
>
> Like I said the table "tempclass" (a temporary) is a dup of table "esclass"
> so none of it makes sense. Of course I did say I'm missing something.
>
> So why isn't the select statement working with the insert?
Looks like an off by one situation. See error detail below:
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa..
^
Looks like the result of the 'select facility.." is being inserted into the
schedule column.
>
> Johnf
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
On 12/30/2010 09:35 AM, Pavel Stehule wrote: Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing the ELSE IF to ELSEIF should fix it. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Thursday 30 December 2010 2:14:23 pm Tony Capobianco wrote: > Hi, > I'm successfully executing the below: > > create role developer login; > alter role developer set default_tablespace=dev; > alter role developer set search_path=dev,staging, esave, support, email, > public; > > grant select on members to developer; > grant create on schema dev to developer; > > However, when I do this: > psql (8.4.5, server 8.4.2) > Type "help" for help. > > esave_dw=> \d members > Did not find any relation named "members". > esave_dw=> > esave_dw=> \d esave.members > Table "esave.members" >Column|Type | Modifiers > -+-+--- > memberid| numeric | not null > etc > > How can I get this so I don't have to preface the \d with the schema > name every time? > > Thanks. > Tony Did you log out and then back in as developer? Per: http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html "The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line." -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am tcapobia...@prospectiv.com wrote: > > On 31 December 2010 05:14, Tony Capobianco > > > > wrote: > >> esave_dw=> \d members > >> Did not find any relation named "members". > >> esave_dw=> > >> esave_dw=> \d esave.members > >> Table "esave.members" > >> Column | Type | Modifiers > >> -+-+--- > >> memberid | numeric | not null > >> etc > >> > >> How can I get this so I don't have to preface the \d with the schema > >> name every time? > > > > Hi Tony, you should changes the default search_path for the specified > > users. > > http://sql-info.de/postgresql/schemas.html > > take a look at practical schema usage section. > > Gibransyah, > That did the trick! Thanks for your help. I modified my role name from > developer to dev since I already have a dev schema. Here's the steps I > ran below to get it working: > > create role dev login; > alter role dev set default_tablespace=dev; > alter role dev set search_path=dev,staging, esave, support, email,public; > > grant usage on schema esave to dev; > grant usage on schema dev to dev; > grant select on members to dev; > grant create on schema dev to dev; > grant create on tablespace dev to dev; > > I am a little confused as to why I had to grant usage & create on dev to > dev since it's both the dev role's default_tablespace and has a schema > named after it. Either way, this corrects my issue. Thanks for your > help! > > Tony First tablespace != schema. From here http://www.postgresql.org/docs/9.0/interactive/manage-ag-tablespaces.html: "Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured. Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system. " Second, from the schema docs if you want 'user' schemas than "The value for search_path must be a comma-separated list of schema names. If one of the list items is the special value $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema. (If not, $user is ignored.) " "The default value for this parameter is '"$user", public' (where the second part will be ignored if there is no schema named public). This supports shared use of a database (where no users have private schemas, and all share use of public), private per-user schemas, and combinations of these. Other effects can be obtained by altering the default search path setting, either globally or per-user. " -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am tcapobia...@prospectiv.com wrote: > > On 31 December 2010 05:14, Tony Capobianco > > > > wrote: > >> esave_dw=> \d members > >> Did not find any relation named "members". > >> esave_dw=> > >> esave_dw=> \d esave.members > >> Table "esave.members" > >> Column | Type | Modifiers > >> -+-+--- > >> memberid | numeric | not null > >> etc > >> > >> How can I get this so I don't have to preface the \d with the schema > >> name every time? > > > > Hi Tony, you should changes the default search_path for the specified > > users. > > http://sql-info.de/postgresql/schemas.html > > take a look at practical schema usage section. > > Gibransyah, > That did the trick! Thanks for your help. I modified my role name from > developer to dev since I already have a dev schema. Here's the steps I > ran below to get it working: > > create role dev login; > alter role dev set default_tablespace=dev; > alter role dev set search_path=dev,staging, esave, support, email,public; > > grant usage on schema esave to dev; > grant usage on schema dev to dev; > grant select on members to dev; > grant create on schema dev to dev; > grant create on tablespace dev to dev; > > I am a little confused as to why I had to grant usage & create on dev to > dev since it's both the dev role's default_tablespace and has a schema > named after it. Either way, this corrects my issue. Thanks for your > help! > > Tony In my previous message I forgot to add the following. The set commands and grants are not linked. Setting something does not necessarily confer privileges for that object. The search_path for instance. It really only sets up the search order for unqualified object names. What you can see or do with those objects is determined by the privileges on those objects. Those privileges come from either the role that created the object or are GRANT(ed) by a sufficiently privileged role to another role. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '')
> as year,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
> num_days_in_month,
> to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as last_day,
> trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), -1)),
> 'DD') as days_in_last_month,
> to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date(:the_date, '-mm-dd'), -1),
> 'fmMonth') as prev_month_name
> from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR: function
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'-...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY: select to_date(date_trunc('month',add_months( $1
> ,1)),'-MM-DD') - 1
> CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri
If I am following this right the problem is in the last_day function and in
particular the return value of the add_months function used in the
date_trunc(). To be sure we would need to see those functions. As a shot in the
dark:
select to_date(date_trunc('month',add_months( $1 ,1)::date)...
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 5:22:30 pm Iuri Sampaio wrote:
> So far,
> I could write the following query
>
> select to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), '') as
> year,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
> to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as last_day,
> trunc(add_months(to_date('2010-01-02', '-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date('2010-01-02', '-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date('2010-01-02', '-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
> days_in_last_month,
> to_char(add_months(to_date('2010-01-02', '-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date('2010-01-02', '-mm-dd'), -1), 'fmMonth')
> as prev_month_name
> from dual
>
>
> But i still miss some lines in order to properly explicit casts in the
> query
>
> cheers,
> iuri
My guess is you upgraded from a version prior to 8.3. In 8.3 many of the
implied
casts where removed, so you probably have to go over your code and make the
corrections.
Go here for more detail:
http://www.postgresql.org/docs/8.4/interactive/release-8-3.html
Section
E.21.2.1. General
A temporary solution can be found here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html
An important tip from the blog-
"The gist was, only restore the casts you need, not all of them."
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help needed in skipping column for copy command
On 01/17/2011 05:59 AM, Amar Dhole wrote: I have table created as follows CREATE TABLE D_2147483927_2147484848_TAB( CP VARCHAR(256) , CPR VARCHAR(256) , CHOUSENO VARCHAR(256) , CSTREET VARCHAR(256) , CLOCALITY VARCHAR(256) , CCITY VARCHAR(256) , CPROVINCE VARCHAR(256) , CCOUNTRY VARCHAR(256) , CZIP VARCHAR(256) , CCO VARCHAR(256) ) I am using copy command to copy the content of file into the table. (one.txt) PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON ,,A-24 Siddi vihar apt.,Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 In the above data, data for last column is missing. Looks like two columns missing. I see 10 headers and 8 data values unless that is a cut and paste issue. copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter as ',' quote '"' csv HEADER ; I get the following error as ERROR: missing data for column "ccontains" Where does ccontains from? I am not seeing it in the table or the text file. CONTEXT: COPY d_2147483927_2147484848_tab, line 2: "q,q,A-24 Siddi vihar apt., Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 " Can any one please tell me how can I make copy command to ignore the data missing column ? as the data in file is unknown so it column name is which is missing is not known in advance. You can specify a column list to COPY. See here: http://www.postgresql.org/docs/9.0/interactive/sql-copy.html Thanks Amar -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] quotes etc
On Tuesday, February 22, 2011 12:26:41 pm John Fabiani wrote:
> Hi,
> I would have thought that there would be a simple built-in function that
> would escape the quotes as ('D' Andes') to ('D\' Andes'). But I did not
> see anything?
>
> I am I wrong?
>
> Johnf
Dollar quoting ? :
http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html
4.1.2.4. Dollar-Quoted String Constants
test(5432)aklaver=>SELECT $$D' Andes$$;
?column?
--
D' Andes
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [SQL] what's wrong in this procedure?
On 02/25/2011 09:46 AM, Camaleon wrote:
This error is returned Erro de SQL:
ERROR: column "Aguardando Pagto" does not exist at character 352>>>
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS '
SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS
transacoes
FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
WHERE o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em análise",
"Aprovado", "Completo")
GROUP BY o.data_fim;
'
language 'sql';
the column is t.status and not "Aguardando Pagto";
what's wrong ? thanks
Try single quotes, 'Aguardando Pagto'
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] is there a refactor
On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote: > Hi, > I would like to have a simple way to retrieve information for a field name. > By that I mean have some SQL select that will return all the tables a field > name exist within a database. I did not find anything with google but of > course google depends on the search string. > > Thanks in advance, > Johnf test(5432)aklaver=>SELECT table_name from information_schema.columns where column_name = 'id'; table_name user_test table2 table1 hours jedit_test topics t2 stone serial_test messages binary_test user_test timestamp_test role_t py_test money_test lock_test local_1 lang_test interval_test foob fooa fldlength fk_1 default_test csv_null check_two check_test array_test (29 rows) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 3:56:51 am f vf wrote: > Hello, > i'm using a pl/sql procedure and I prevent inserting duplicate tuples using > an exception for example: > > BEGIN >INSERT INTO "Triples"(id, subject, predicate, "object") > VALUES (id, sub_i, pred_i, obj_i); > * EXCEPTION WHEN unique_violation THEN > --do something. > > *In some cases I have interest in getting the id of the tuple that was > already in the table when the exception is triggered. Is there a way for > the EXCEPTION to return that id instead of using a select to know wich was > the id of the triple already existing in the table? If the id is the PRIMARY KEY then it would be the same as the id you tried to INSERT correct? > > Thanks, > Filipe -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 6:50:57 am f vf wrote: > No, the id is nextval( 'triples_seq'), if I do nothing, so its something > like: > BEGIN > INSERT INTO "Triples"(id, subject, predicate, "object") > VALUES (nextval( 'triples_seq'), sub_i, pred_i, > obj_i); EXCEPTION WHEN unique_violation THEN > --do something. > > The unique constraint is applyied to the subject, predicate and "object" > fields. So, if I try to insert anything that has these 3 fields equal to > any tuple that already exists in the table I want to get the id of the > original tuple. > To your original question I am not aware of a way of returning the id of the offending tuple, other than through a SELECT. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
On Monday, May 23, 2011 9:53:47 am Dean le Roux wrote:
> postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new
> install)
Did the OO version change also?
>
> I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
> updates have been effected. I used webmin to restore the databases.
>
> Since migrating to Linux around 2006 we were successful in converting our
> mdb file to postgresql - only one glitch was a difference in -1 as YES. We
> successfully used the systems with open office as a front end for the last
> few years. Until now - after the upgrade I can view data but not update
> data.
Usually, in Base, that indicates that it can not find a primary key.
FYI:
The below is redundant:
CONSTRAINT "FINANCEDETAILS_pkey" PRIMARY KEY ("FINDETID"),
CONSTRAINT "FINANCEDETAILS_FINDETID_key" UNIQUE ("FINDETID")
From the docs:
http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html
"Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but
identifying a set of columns as primary key also provides metadata about the
design of the schema, as a primary key implies that other tables can rely on
this set of columns as a unique identifier for rows. "
>
> Experience with sql is very limited as I have always used sql query
> builders over the years. The system has worked well with multiple tables,
> and in the past there was no problems with sending sql statements to
> postgresql.
>
> My problem in OOO 3.2 base (other front ends also) is that there is
> continually a problem with not allowing queries to update data back to
> postgresql.
What are you using to connect to the database JDBC, ODBC, other?
>
> I believe something has changed perhaps in sql from 8.3 to 8.4, or I have
> missed something with restoring the files.
Did you do a full restore or selective?
>
>
> Any assistance offered will be appreciated.
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [SQL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?
On 07/06/2011 12:03 PM, Emi Lu wrote:
A question about: ERROR: malformed record literal: ""
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column | Type
-+
run_date | character varying(128)
adm_year | character varying(4)
adm_sess | character varying(1)
faculty | character varying(128)
ac_cycle | character varying(128)
deg_code | character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed | numeric
accepted | numeric
confirmed | numeric
registered | numeric
hold | numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;
I have fixed it.
It should not be z_drop, it should be real column names.
The mailing list email appears so slow :-( Only after 4 hours it show!
What happens if you do?:
PREPARE test AS INSERT
My rough guess is that z_drop is being applied to the first parameter only.
Emi
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;
Thank you,
Emi
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using explain output within pgsql
On Sunday, July 10, 2011 11:54:10 am Uwe Bartels wrote: > Hi, > > I'm starting up a datawarehouse with patitioning. > my etl processes write directly into the corresponding partitions instead > of using triggers. > > The reports I run in the datawarehouse are stored in a cache within the > same database. > Now I'd like to store besides the results the dependencies to the tables > which were used to generate the report. with this information i could > invalidate cache results for the tables I'm going to import with my etl > processes. > > explain analyze gives me the information which table or patition is read > from for each report. e.g > explain analyze (FORMAT YAML) create table cache.report234 as select > col1,col2,sum(clicks) from dwh.event_log_weekly where week >= '2011-06-27' > and week <= '2011-07-11' group by col1,col2; > > now I'd like to store the output of explain analyze in a pgsql variable for > further processing. that looks something like this. > > DO $$declare l_explain text; > begin > l_explain := explain analyze (FORMAT YAML) create table cache.report234 as > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >= > '2011-06-27' and week <= '2011-07-11' group by col1,col2; > select l_explain; > end$$; > > But that doesn't work. I get a syntax error. From here: http://www.postgresql.org/docs/9.0/interactive/sql-explain.html I believe you are looking for: explain (ANALYZE, FORMAT YAML) create table... > > Does anybody has an idea how to retrieve the output of explain within pgsql > and store this in a variable? > An alternative would be any other way to extract the information about > tables used by arbitrary sql statements. > > best regards, > Uwe -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] why these results?
On Monday, August 01, 2011 3:50:00 pm Wes James wrote: > select count(*) from table; > > count > --- >100 > (1 row) > > > is correct > > select count(*) from table where col::text ~~* '%text%'; > > count > --- > 1 > (1 row) > > is correct. > > But now if I do: > > > select count(*) from table where col::text !~~* '%text%'; > count > --- > 98 > (1 row) > > Shouldn't it be 99? That is out of 100 records there is one that has > "text" in column "col" so the !~~* should return 99 rows. ?? NULL value in field? > > -wes -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgadmin debugger
On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > Greetings, > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I > have pgadmin version 1.10.2. I can't find debugger_plugin.so which is > needed to debug pgplsql using pgadmin: > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQL-De > bugger.html > > Any idea? http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 4:13:30 am Janiv Ratson wrote: > Hi, > > > > I have the following query: > > > > select ticket as ticket, time as created, author as reporter, > cast(extract(epoch from (date 'now' - integer '30')) as bigint) > > from ticket_change tc > > where field = 'status' > > and newvalue = 'reopened' > > and time > extract(epoch from (date 'now' - integer '30')) > > order by time > > > > I'm trying it get all records that their 'time' is within the past 30 > days. > > However, the time is bigint: 128732389900 > > While the extract(epoch from (date 'now' - integer '30')) is 1310677200 Bigint versus integer refers to the max values that the field can contain. For a given value of integer the storage should be the same for each up to the limit of the integer field. Would seem that whatever is putting values into time is inflating the values if they are actually referring to contemporary time values. > > > > As you understand, I always get all records ... > > > > How do I solve it? > > > > Thanks, > > Janiv. > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgadmin debugger
On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > > Greetings, > > > > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I > > > have pgadmin version 1.10.2. I can't find debugger_plugin.so which is > > > needed to debug pgplsql using pgadmin: > > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQ > > > L-De bugger.html > > > > > > Any idea? > > > > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html > > Trying to compile (no configure script found) the thing I get: > pldebugger # make > Makefile:42: ../../src/Makefile.global: No such file or directory > Makefile:43: /contrib/contrib-global.mk: No such file or directory > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. Looking at README.pldebugger indicates you need the development code for Postgres. Is your Postgres installation from the Ubuntu packages or did you compile it yourself? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgadmin debugger
On Sunday, August 14, 2011 11:33:13 am David Harel wrote: > On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote: > > On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > > > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > > > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > > > > Greetings, > > > > > > > > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also > > > > > I have pgadmin version 1.10.2. I can't find debugger_plugin.so > > > > > which is needed to debug pgplsql using pgadmin: > > > > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PL > > > > > PgSQ L-De bugger.html > > > > > > > > > > Any idea? > > > > > > > > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html > > > > > > Trying to compile (no configure script found) the thing I get: > > > pldebugger # make > > > Makefile:42: ../../src/Makefile.global: No such file or directory > > > Makefile:43: /contrib/contrib-global.mk: No such file or directory > > > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. > > > > Looking at README.pldebugger indicates you need the development code for > > Postgres. Is your Postgres installation from the Ubuntu packages or did > > you compile it yourself? > > I installed form Ubuntu package. I am currently trying to install > postgresql-server-dev-8.4.8-0ubuntu0.10.04 package. > Will that suffice? Probably would not hurt to install the contrib package also. I generally install from source, so I am wandering into relatively unknown territory here:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote: > Hi and thanks, > If my 'time' column is being saved as bigint: 128732389900. > How do I write a query to check if the 'time' field is greater than now - > 30 (past 30 days)? So what you want is not what values are greater than some point 30 days ago which is what your previous query asked and answered, but the values between a point 30 days ago and today. The easiest way is to use BETWEEN: test(5432)aklaver=>\d big_int_test Table "public.big_int_test" Column | Type | Modifiers +-+--- bint | bigint | rint | integer | test(5432)aklaver=>SELECT * from big_int_test ; bint |rint --+ 128732389900 | 1310799600 test(5432)aklaver=>SELECT bint FROM big_int_test WHERE bint BETWEEN extract(epoch from (date 'now' - integer '30')) AND extract(epoch from (date 'now')); bint -- (0 rows) That being said, if your time values are the order of magnitude shown they will not meet the criteria above. Is the time value supposed to be seconds? > > Thanks, > Janiv,. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Tuesday, August 16, 2011 2:12:52 am Janiv Ratson wrote: > Hi Adrain and thank you, > Trac 0.12 uses microseconds as time value. > What do you suggest? extract(epoch ..) returns seconds which you are trying to compare to microseconds. The solution would be to divide your 'time' values by 1,000,000 to make them seconds. Like: SELECT ticket, "time"/100, author, field, oldvalue, newvalue FROM ticket_change where "time"/100 BETWEEN extract(epoch from (date 'now' - integer '30')) AND extract(epoch from (date 'now')); > > Thanks, > Janiv. > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Hi
On Saturday, September 10, 2011 2:22:32 pm Mandana Mokhtary wrote: > > From: Mandana Mokhtary > Sent: 10 September 2011 23:21 > To: pgsql-sql-ow...@postgresql.org > Subject: Hi > > Hi All > I tried to import shapfile into postgres using this comand: > > pgsql -c -s 3006 -W LATIN1 c:/.. |psql _U postgres (database name) > > I could import some but not all. > I got this error that I do not have any idea about it: > > current transaction is aborted, commands ignored until end of transactions > block. > Means just what it says, a transaction was aborted due to error and at that point all further commands where ignored. > at fisrt, the name of the shapfiles have some Swedish alphabet which i > changed it to latin > > I would appreciate any help. The actual command string would be nice:) If the above was it then that explains the problem. First I think you are looking for psql not pgsql. Second it should be psql -U postgres ... > > Regards, Mandana -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
On Friday, December 16, 2011 3:56:48 am John Fabiani wrote:
> I have solved my problem. But this still does not explain the idea of
> "from"
From original query:
..(function_name(303, week_date::date)) as week_date where week_date..
Reason why that did not work:
http://www.postgresql.org/docs/9.0/interactive/sql-select.html
SELECT List
"...
An output column's name can be used to refer to the column's value in ORDER BY
and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must
write out the expression instead.
...
"
Why the below does work:
SELECT List
"The SELECT list (between the key words SELECT and FROM) specifies expressions
that form the output rows of the SELECT statement. The expressions can (and
usually do) refer to columns computed in the FROM clause"
FROM Clause
"select
A sub-SELECT can appear in the FROM clause. This acts as though its output
were created as a temporary table for the duration of this single SELECT
command. Note that the sub-SELECT must be surrounded by parentheses, and an
alias must be provided for it. A VALUES command can also be used here.
"
>
> select foo.week_date, xchromasun._chromasun_getqtyordered(303,
> foo.week_date) as week_qty from
> (select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
> week_date from generate_series(0,84,7)
> i ) as foo
>
> The above works!
>
> Johnf
>
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
On Saturday, December 17, 2011 8:50:52 am John Fabiani wrote: > As always I respect your insights - Adrian. I do understand what I did > wrong in my first attempt at getting my statement to work. But it is > either over my head or there is something missing. Where is the "from" in > select now()? If it makes you happier:) test(5432)aklaver=>SELECT * from now(); now --- 2011-12-17 10:00:34.929144-08 (1 row) > > I have been using similar SQL statements for years. I never questioned why > there was not a 'from' until this list noted that I was missing a 'From'. > I then went to the postgres site to read. That's how I determined what I > had done incorrectly. SELECT List "The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause." So items in the SELECT are not required to be derived from as FROM clause > > I hope this is not one of those things like javascript where all white > space is ignored unless it's not! I hate that language! It appears that > everything needs a 'From' in SQL (reading the doc's) and the above > statement is missing a 'From'! It is a Postgres extension to the SQL standard: http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-SELECT-LIST Compatibility " Omitted FROM Clauses PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions: SELECT 2+2; ?column? -- 4 Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables. For example, the following query is invalid: SELECT distributors.* WHERE distributors.name = 'Westward'; PostgreSQL releases prior to 8.1 would accept queries of this form, and add an implicit entry to the query's FROM clause for each table referenced by the query. This is no longer allowed. " > > As always everyone - thanks for your help! > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Column "..." does not exist (view + union)
On Saturday, December 17, 2011 2:21:30 pm Stefan Weiss wrote: > > I know, but the problem only occurs when I want to sort by a column > which hasn't been selected, and thus cannot be referred to by its index. > For normal (non-union) queries, this is possible: > > SELECT relname > FROM pg_class > WHERE relhasindex > ORDER BY relpages; > > In this trivial case, PostgreSQL knows where to look for "relpages". > Not so in a union: > > SELECT relname > FROM pg_class > WHERE relhasindex > UNION > SELECT relname > FROM pg_class > WHERE relhasoids > ORDER BY relpages; > > (ERROR: column "relpages" does not exist) > > I understand the error now (I think), and I know how to avoid it. SELECT relname FROM pg_class WHERE relhasindex UNION (SELECT relname FROM pg_class WHERE relhasoids ORDER BY relpages); > > > thanks, > stefan -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] avoid the creating the type for setof
On Friday, December 30, 2011 6:26:19 am John Fabiani wrote: > Hi, > I recall somewhere I saw a simple plpgsql function that returned a table > with more than one record that did not use a defined type or a temp table > ( at least I think I did). Is it possible to create such a function that > will return more than one record and not require a record type or temp > table? > > something like > > return setof record as > return query select ... http://www.postgresql.org/docs/9.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS 39.3.1. Declaring Function Parameters Search for RETURNS TABLE > > return > > > Johnf -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: > I have these 3 tables: > > tablename > > tmp_staging0109 > tmp_staging1229 > tmp_staging0108 > > > I'd like this query: > > select tablename from pg_tables where tablename like 'tmp_staging%' and > tablename < 'tmp_staging1230'; > > To return this result: > > tablename > > tmp_staging1229 > > However, I'm receiving: > > tablename > > tmp_staging0109 > tmp_staging1229 > tmp_staging0108 > > How can I write this correctly? As far as I can tell it is correct. 0108,0109 and 1229 are all less than 1230. What happens if you do?: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename < 'tmp_staging1230' and tablename > 'tmp_staging1228; > > Thanks. > Tony -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: > tablename > > tmp_staging0109 > tmp_staging1229 > tmp_staging0108 > > How can I write this correctly? Had another idea. If you are looking for the highest numbered table below a certain number then maybe this: test(5432)aklaver=>\d name_test Table "public.name_test" Column | Type| Modifiers +---+--- fld_1 | character varying | test(5432)aklaver=>SELECT * from name_test ; fld_1 - tmp_staging0109 tmp_staging0108 tmp_staging1229 (3 rows) test(5432)aklaver=>select fld_1 from name_test where fld_1 like 'tmp_staging%' and fld_1< 'tmp_staging1230' order by fld_1 desc limit 1; fld_1 - tmp_staging1229 > > Thanks. > Tony -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:28:43 am Tony Capobianco wrote: > I see what you're saying: > > pg=# select tablename from pg_tables where tablename like 'tmp_staging%' > and tablename < 'tmp_staging1230' and tablename > 'tmp_staging1228'; > tablename > > tmp_staging1229 > > > This query is part of a larger script where I want to dynamically select > tablenames older than 10 days and drop them. The tables are created in > a tmp_stagingMMDD format. I know postgres does not maintain object > create times, how can I write this to select tables from pg_tables that > are older than 10 days? Well with out a year number(i.e. YYMMDD) that is going to be difficult around the year break. As an example: test(5432)aklaver=>select * from name_test; fld_1 - tmp_staging0109 tmp_staging0108 tmp_staging1229 (3 rows) test(5432)aklaver=>SELECT fld_1 from name_test where fld_1 < 'tmp_staging'|| to_char(current_date-interval '10 days','MMDD') and fld_1 > 'tmp_staging0131'; fld_1 - tmp_staging1229 > > Thanks. > Tony > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On Thursday, January 12, 2012 8:42:59 am Carlos Mennens wrote: > I seem to have an issue where I can't modify a table due to another > tables foreign key association: > > [CODE]trinity=# \d developers > Table "public.developers" > Column| Type | Modifiers > --++--- > id | character(10) | not null > name| character(50) | not null > address | character(50) | > city| character(50) | > state | character(2) | > zip | character(10) | > country | character(50) | > phone | character(50) | > email | character(255) | > Indexes: > "developers_pkey" PRIMARY KEY, btree (id) > Referenced by: > TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) > REFERENCES developers(id) > [/CODE] > > > Now when I try and change the values before I alter the field TYPE, I > get an error that another table (orders) with a foreign key associated > with public.developers 'id' field still has old values therefor can't > change / modify the 'developers' table. > > [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; > ERROR: update or delete on table "developers" violates foreign key > constraint "fk_orders_developers" on table "orders" > DETAIL: Key (id)=(11) is still referenced from table "orders". > [/CODE] > > How does one accomplish my goal? Is this difficult to change or once > that foreign key is created, are you stuck with that particular > constraint? You are pushing in the wrong direction. You need to make the change in the table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On Thursday, January 12, 2012 9:02:35 am David Johnston wrote: > > > Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders" > the error in question would never have appeared and the UPDATE would have > succeeded. Carlos' goal is to change the value of a Primary Key that has > already been used in a FOREIGN KEY constraint and he needs to learn to use > the documentation to solve some of these basic questions instead of asking > the list. His approach is correct, execute UPDATE against the > "developers" table. My mistake, I got the table relationship order wrong. Sorry for the noise. > > David J. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE COMPATIBILITY
On Tuesday, January 17, 2012 12:49:44 am Thomas Kellerer wrote: > Gera Mel Handumon, 17.01.2012 07:31: > > None as far as I know. > > You need to rewrite it to: > > UPDATE accounts >SET contact_last_name = s.last_name, >contact_first_name = s.first_name > FROM salesmen s > WHERE s.id = accounts.sales_id For completeness, you could also do: UPDATE accounts SET (contact_last_name,contact_first_name)= (s.last_name,s.first_name) FROM salesmen s WHERE s.id = accounts.sales_id Gets you a little closer to what you want:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > The following code works in 8.4 but not 8.3. > Anyone know why, or what I need to do to change it? > > SELECT aid, asid, >date_range (asdate, afdate)::date AS asdate, >acomments > FROM availability > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html "Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; > > Is there a way to use the integer only generate_series in 8.3 to generate > dates by typecasting to/from integers? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On 01/19/2012 09:17 AM, Samuel Gendler wrote: On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver mailto:adrian.kla...@gmail.com>> wrote: On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > The following code works in 8.4 but not 8.3. > Anyone know why, or what I need to do to change it? > > SELECT aid, asid, >date_range (asdate, afdate)::date AS asdate, >acomments > FROM availability > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html "Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; I don't think you can have that cast there when it is in the from-clause. That was a cut and paste error on my part, I just copied that line from the original query. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Display Length Between Var & Varchar
On Tuesday, January 31, 2012 8:08:06 pm Carlos Mennens wrote: > I've noticed when I set a field to char, it takes up lots of space over > varchar: > > iamunix=# SELECT * FROM music; > id | band| album |date| > asin|label > +---+--++-- > --+-- 1 | Dance Gavin > Dance | Downtown Battle Mountain | 2007-05-15 | > B000OQF4PQ | Rise Records > (1 row) > > iamunix=# SELECT * FROM music; > id | band| album |date| > asin|label > +---+--++-- > --+-- 1 | Dance Gavin Dance | Downtown Battle Mountain | > 2007-05-15 | > B000OQF4PQ | Rise Records > (1 row) > > I don't know how well it will show in plain text via email but does > anyone know why the field display width is wasted with so much white > space when set to char? You will find that the Manual is very helpful in this regard:) http://www.postgresql.org/docs/9.0/interactive/datatype-character.html character varying(n), varchar(n)variable-length with limit character(n), char(n) fixed-length, blank padded -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] type cast about int to bit
On Sunday, February 05, 2012 10:11:12 pm zoulx1982 wrote: > hi， > there is a problem about type cast that i don't understand, follow is my > test. > > postgres=# select 10::bit(3); > bit > - > 010 > (1 row) > postgres=# select 10::bit varying(3); > ERROR: cannot cast type integer to bit varying > LINE 1: select 10::bit varying(3); > ^ > postgres=# > > my question is why int can cast to bit , i want to know the reason. > thank you for your timing. My guess it depends on the fact that bit types are stored as either char or varchar depending on whether they are bit or bit varying. In the first case you are basically doing an int-->char, for which there is a built in cast. In the second case you are doing int-->varchar for which there is not a cast. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] type cast about int to bit
On Monday, February 06, 2012 6:42:45 pm zoulx1982 wrote: > you mean there is no cast function for int to varchar ? > i see sure it is. > That is why I said my guess:) If you want to see what is actually going on take a look at: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/varbit.c;h=adb08369ed28ab6b52aa2cd5213bcd5b4d8de7ad;hb=HEAD The ERROR though is coming further up, in the parser , if I am following correctly. This because as you have found out there is no direct cast from integer to varbit. Why that is for someone else to answer, as I don't know. Though a little playing around got this, not pretty but it seems to work: test(5432)aklaver=>SELECT 10::bit(3)::varbit(3); varbit 010 (1 row) test(5432)aklaver=>SELECT 10::bit(3)::varbit(4); varbit 010 (1 row) test(5432)aklaver=>SELECT 10::bit(4)::varbit(4); varbit 1010 (1 row) test(5432)aklaver=>SELECT 10::bit(4)::varbit(3); varbit ---- 101 -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 10:59 AM, Edward W. Rouse wrote: I am working with a database set up by someone else. They set it up so that most tables are in the public schema, but they also have a reports schema; database.public.tables and database.reports.tables. If I do a pg_dump of the database, I only get the public schema. If I do a dump with --schema=reports, I only get the reports schema. Is there a way to get all the schemas from a single pg_dump or am I forced to use separate ones? This is also for future issues where there may be more than 2. The pg_dump should work. What is the exact command line statement you are using? Are doing both dumps as the same user? Thanks Edward W. Rouse -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 11:31 AM, Edward W. Rouse wrote: To answer the second question first, yes; both as the same user. pg_dump -v -f $bkfile -F c -U $USER $DATABASE So how are you determining that only the public schema is being dumped? One thing to check is the search_path setting in postgresql.conf. This can create the illusion that only one schema is available in a database. One way to check is to use the fully qualified name for a table you know to be in the reports schema. Ex: select * from reports.some_table; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 12:17 PM, Edward W. Rouse wrote: Well, when I do a restore using the created file, reports isn't there. i.e. the select from reports.table gives an error and, from psql, \l doesn't list it and \dn doesn't show it. But that all does work on the original database. What is the error reported? What do the Postgres logs show when you restore the data? Do a pg_restore -l against the dump file. This will list the contents of the dump. See if the schema and contents are there. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on insert rule with default value
On Wednesday, February 22, 2012 5:52:39 am Ron Peterson wrote: > 2012-02-21_15:51:30-0500 Ron Peterson : > > My rule below does not insert the the same uuid value into the test_log > > table as is created in the test table when I insert a new value. I know > > I've worked through this before, but I'm not remembering why this is. > > What's a right way to do this? > > Obviously I can use a trigger function. I'm mostly wondering if there > are any tricks to accomplishing this with rules (I like the simple > syntax). I suspect the problem here is that 'new' on insert refers to > the function used to calculate the new value, /not/ the new value that > is actually inserted into the table. There are probably reasons for > that; but it would seem nicer to refer to the actual new table value > rather than the expression used to calculate it. My 2c. The simplest explanation I could find is here: http://www.postgresql.org/docs/9.0/interactive/querytree.html "the target list... For INSERT commands, the target list describes the new rows that should go into the result relation. It consists of the expressions in the VALUES clause or the ones from the SELECT clause in INSERT ... SELECT. The first step of the rewrite process adds target list entries for any columns that were not assigned to by the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression. " If you want all the gory details read through section 37, in particular 37.3 :) The above is why I use triggers now. It is a lot easier to follow the logic in a trigger than in a rule. > > -Ron- -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL View to PostgreSQL View
On Sunday, February 26, 2012 10:50:16 am Rehan Saleem wrote: > Hi , > I am trying to convert sql > view to postgresql view but i am getting the > following error i dont know how > to handle dbo. > in postgresql and when i remove dbo. from table name then view got created > but it does not show any data, while this is working perfectly fine in > sql, here is my code and error details > > i am getting this error , how can i fix this. > > ERROR: schema "dbo" does not exist > LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr > ^ > > ** Error ** > > ERROR: schema "dbo" does not exist > SQL state: 3F000 > Character: 761 Do you in fact have a schema dbo? If so can you access the schema and table using some other method, for instance using psql? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problems with ODBC connections
On Saturday, February 25, 2012 10:05:07 am Sandeep Reddy wrote: > Hi, > I am completely new to postgres and I have some problems with ODBC > connection. > 1) I am running postgres server in Fedora Linux, and I am planning to > connect server from windows client. > 2) I have installed 64bit version of postgresql ODBC drivers > 3) I am making sure that my postgres is running with -p and > -i option to accept remote connections. > > Still I am getting error saying Connection can not be established. Any > quick pointers are highly appreciable. The actual error message would be helpful:)? A common reason for this is a firewall between the client and server that is preventing connection to the server port. Another reason is a pg_hba.conf that has not been configured to allow connections from the client. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On 03/01/2012 09:04 AM, Carlos Mennens wrote: On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang wrote: Hi You can try this command "REASSIGN OWNED BY TO ..." like this: REASSIGN OWNED BY previous_role TO new_role; DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) You are now connected to database "postgres" as user "carlos". You are working on the database 'postgres' not 'iamunix'. REASSIGN works on the current database only. So you just REASSIGNED any objects in postgres. postgres=# REASSIGN OWNED BY carlos TO lauren; REASSIGN OWNED postgres=# DROP OWNED BY carlos; DROP OWNED iamunix=# \d List of relations Schema | Name | Type | Owner +--+--+ public | dept | table| carlos public | dept_id_seq | sequence | carlos public | employees| table| carlos public | employees_id_seq | sequence | carlos public | manager_lookup | view | carlos public | managers | table| carlos public | managers_id_seq | sequence | carlos Change into iamunix and do the REASSIGN. That didn't work for some reason but mostly because I don't follow the concept of what's being done. I've now since changed the database role owner back to Carlos so now 'Carlos' owns both the database and all of it's tables. Can we start fresh and assume I just got the request to change the specified database and all of it's tables, sequences, views,& triggers to Lauren? See above. For future reference including the Postgres version would be helpful. This area ownership/grants/etc has undergone a lot of changes over the various versions. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On 03/01/2012 11:37 AM, Carlos Mennens wrote: I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! In your previous post my guess is this: iamunix=# \c postgres was really meant to be: iamunix=# \c - postgres The first changes to database postgres as current user, the second changes the user while remaining on the current database. On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens wrote: I did do a Google search for "PostgreSQL 9.1 change ownership recursively" but either couldn't find what I was looking for or missed it. On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver wrote: For future reference including the Postgres version would be helpful. This area ownership/grants/etc has undergone a lot of changes over the various versions. I specified above I was using 9.1 PostgreSQL. Oops, my mistake, I never got to the PS:( -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump: aborting because of server version mismatch
On 05/02/2012 12:55 PM, Mitesh Shah wrote: Hi, I am trying to create a daily backup cron script but it fails with an error as below: Any pointers to resolve this will be greatly appreciated. Thanks, Mitesh Shah mitesh.s...@stripes39.com <mailto:mitesh.s...@stripes39.com> *(1) Error:* bash-3.2$ sh pg_backup_rotated_orig.sh Making backup directory in /Users/miteshshah/Documents/2012-05-02-daily/ -e Performing full backups -e Plain backup of mitesh *pg_dump: server version: 9.1.2; pg_dump version: 9.0.5* The problem is you are using an older version of pg_dump to dump a newer database. That will not work. Possible solution: You are running via cron. Cron has its own environment. Unless you are explicit in your pathing you can get surprising results, see above. Find the path to the 9.1.2 version of pg_dump and use that absolute path in your script. *pg_dump: aborting because of server version mismatch* -e All database backups complete! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On 05/11/2012 12:30 PM, Carlos Mennens wrote: Thanks for all the help thus far everyone! I sadly didn't create/design the table and would love to create a SEQUENCE on that particular field but not sure how unless I DROP the table and create from scratch. Currently the data TYPE on the primary key field (users_id) is CHAR and I have no idea why...it should be NUMERIC or SERIAL but it's not so my question is if I want to ALTER the column and create a sequence, would I simply do: ALTER TABLE users ALTER COLUMN users_id TYPE serial ; Obviously if any of the data stored in users_id is actual CHAR, I'm guessing the database would reject that request to change type as the existing data would match. However the data type is CHAR but the field values are all numeric from 100010 - 100301 so I'm hoping that would work for SERIAL which is just INTEGER, right? Well the question to ask is if it is declared CHAR was that done for a legitimate reason? One reason I can think of is to have leading 0s in a 'number'. Might want to double check that code downstream is not depending on CHAR behavior. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On 06/14/2012 01:39 AM, Achilleas Mantzios wrote:
> Hello,one remote user reported a problem and i was surprised to witness the
> following behaviour.
> It is on postgresql 8.3.3
>
> dynacom=# BEGIN;
> BEGIN
> dynacom=#
> dynacom=#
> dynacom=# insert into xadmin(appname,apptbl_tmp,gao,id,comment)
> dynacom-#
> values('PMS','overhaul_report_tmp','INSERT',nextval('overhaul_report_tmp_pkid_seq'),'
> zzz ');
> INSERT 0 1
> dynacom=#
> dynacom=# insert into
> items_tmp(id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,
> dynacom(#
> classpostponed,classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,xid,classaa)
> dynacom-# select
> id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,classpostponed,
> dynacom-#
> classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,currval('xadmin_xid_seq'),
> dynacom-# classaa from items where id=1261319;
> INSERT 0 1
> dynacom=# -- in the above 'xadmin_xid_seq' has taken a new value in the first
> insert
> dynacom=# SELECT currval('xadmin_xid_seq');
> currval
> -
> 61972
> (1 row)
> dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
> id
> -
> 1261319
> (1 row)
> dynacom=# -- ok this is how it should be
> dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
> xid=currval('xadmin_xid_seq');
> id
>
> (0 rows)
> dynacom=# -- THIS IS INSANE
>
> This code has run fine (the last SELECT returns exactly one row) for
> 5,409,779 total transactions thus far, in 70
> different postgresql slave installations (mixture of 8.3.3 and 8.3.13) (we
> are a shipping company),
> until i got this error report from a user yesterday.
>
> What could be causing this? How could i further investigate this?
The only thing I could come up with is:
SELECT id, currval('xadmin_xid_seq') from items_tmp WHERE id=1261319 ;
Its grasping at straws, but I can not come up with a logical reason for the
above.
> Achilleas Mantzios
> IT DEPT
>
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] must appear in GROUP by clause issue
On 06/29/2012 01:02 PM, George Woodring wrote: Any suggestions would be appreciated. Are there any other errors before or after the above that might relate? Are the function bodies the same on both servers? George Woodring -- iGLASS Networks www.iglass.net <http://www.iglass.net> -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore problem
On 09/12/2012 12:23 AM, Kjell Øygard wrote:
Morning guys...
I have two servers , one with postgres 9.2rc1 and one with postgres
9.1.4. I need to do a restore from a dump from 9.1.4 to 9.2rc1 and I get
this error:
pg_restore: [archiver (db)] Error from TOC entry 177675; 2613 579519
BLOB 579519 primar
pg_restore: [archiver (db)] could not execute query: ERROR: duplicate
key value violates unique constraint "pg_largeobject_metadata_oid_index"
DETAIL: Key (oid)=(579519) already exists.
Command was: SELECT pg_catalog.lo_create('579519');
This just keep repeat itself in the log.
The command used is: pg_restore -O -U user -d database2 database2.dump
>dump.log 2>&1 &
Appreciate any help
Several things:
1) The production version of 9,2 is out(9.2.0).
2) When you did the dump from 9.1.4 did you use the 9.1.4 or 9.2 version
of pg_dump?
3) What was the pg_dump command you used?
--
Rgds
Kjell Inge Øygard
Electronic Chart Centre
www.ecc.no <http://www.ecc.no>
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore problem
On 09/14/2012 01:58 AM, Kjell Øygard wrote:
1 - Ok, I was not aware of that
2 - I used version 9.1.4 of pg_dump
3 - The command was in a script, se below
pdir=/usr/local/postgresql-9.1.4/
bdir=/backup/`hostname -s`/dump/
export PATH=${pdir}/bin:$PATH
# make sure tmp files are not readable by others
umask 0077
for db in `psql -l -t -h localhost | awk '{print $1}' |grep -v
template|grep -v postgres`
do
pg_dump -h localhost -F c -Z -b $db > ${bdir}/${db}.tmp && mv
${bdir}/${db}.tmp ${bdir}/${db}.dump
I do not see anything obviously wrong.
Two suggestions.
1) Use the 9.2 version of pg_dump. Newer versions know about changes in
data handling and are also backward compatible(to 7.0).
2) As of 8.3(I believe) the -b switch is redundant for whole database dumps.
When you do the above dump are there large objects in the 9.2 database
in spite of the errors?
rgds Kjell Inge Ø
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC
On 12/12/2012 09:13 AM, Emi Lu wrote: > Good morning, > > Is there a simple way to load UTF8 data in psql to mysql(with latin1 > encoding) through JDBC? This would seem to be dependent on the MySQL JDBC adapter. >From here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html "All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent " > > Thanks a lot! > Emi > > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC
On 12/12/2012 09:47 AM, Emi Lu wrote: Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) through JDBC? This would seem to be dependent on the MySQL JDBC adapter. From here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html "All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent " This does not help. The reason I asked this is because through mybatis + JDBC, loading data from psql@utf8 to mysql@latin1, the french character cannot be auto-loaded. You might get an answer sooner at: https://groups.google.com/group/mybatis-user Thanks. -- Emi -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query execution based on a condition
On 12/29/2012 11:05 AM, JORGE MALDONADO wrote: I have a query similar to the one shown below but, depending on the value of a field, only the first SELECT statement shoud execute and the other 3 should be ignored. Is there a way to achieve this situation? Probably so, but is hard to be specific without more information. In a particular the condition/field being tested and values being tested for. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] copy users/groups
On 01/08/2013 07:08 AM, Edward W. Rouse wrote: We have a database that was updated from 7.4.1 to 9.1.3. I've gotten everything working, but now it seems that the users and groups weren't restored. Probably because they weren't backed up. Is there a way to dump just the users and groups, plus the passwords and permissions, and restore them without overwriting what's been manually added to the new database? Users/groups i.e roles are global to a cluster, you need to use pg_dumpall: http://www.postgresql.org/docs/9.1/interactive/app-pg-dumpall.html pg_dumpall -g Will dump only the global items. Edward W. Rouse -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to generate drop cascade with pg_dump
On 01/08/2013 01:53 PM, Emi Lu wrote: Hello, May I know how to generate drop table cascade when pg_dump a schema please? E.g., pg_dump -h db_server -E UTF8 -n schema_name -U schema_owner --clean -d db_name >! ~/a.dmp In a.dmp, I'd like to get: drop table t1 cascade; drop table t2 cascade; ... ... Only dropping constraints within a schema is not good enough since there are dependencies on other schema. That is a limitation of dumping by schema. http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html "Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. If you want to reach across schemas you either need to do a whole database dump or modify a partial dump or create your own script. Thanks a lot! Emi -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 08:30 AM, James Sharrett wrote:
I have a function that generates a table of records and then a SQL
statement that does a COPY into a text file. I want to return the
number of records output into the text file from my function. The
number of rows in the table is not necessarily the number of rows in the
file due to summarization of data in the table on the way out. Here is
a very shortened version of what I'm doing:
CREATE OR REPLACE FUNCTION export_data(list of parameters)
RETURNS integer AS
$BODY$
declare
My variables
Begin
{ A lot of SQL to build and populate the table of records to export}
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return 0;
end
$BODY$
LANGUAGE plpgsql VOLATILE
strSQL gets dynamically generated so it's not a static statement.
This all works exactly as I want. But when I try to get the row count
back out I cannot get it. I've tried the following:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL into export_count;
Return export_count;
This give me an error saying that I've tried to use the INTO statement
with a command that doesn't return data.
2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Get diagnostics export_count = row_count;
This always returns zero.
3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return row_count;
This returns a null.
Any way to do this?
If it helps:
http://www.postgresql.org/docs/9.2/interactive/sql-copy.html
"
On successful completion, a COPY command returns a command tag of the form
COPY count
The count is the number of rows copied.
"
So it looks like you will need to parse the string for the count.
Thanks in advance,
James
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 09:16 AM, James Sharrett wrote: The problem I have is that I get nothing back when the COPY is run inside the function other than what I explicitly return from the function so I don't have anything to parse. It's odd that the record count in the function is treated differently than from sql query in GET DIAGNOSTIC since the format and information in the string (when run outside of the function) are exactly the same. Execute strSQL into export_count; What is export_count DECLAREd as? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 09:26 AM, James Sharrett wrote: Integer Well copy is returning a string, so try changing the type. You will have to parse that string for the count. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with extract(epoch from interval ...
On 01/23/2013 03:00 AM, Ian Lawrence Barwick wrote: Hi 2013/1/23 Kaloyan Iliev : Hi, I have a little problem with extract epoch from interval. It seems that the query works if the interval is in a string but not from DB field. Could someone provide support. (...) You have a casting error; instead of: dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a; ERROR: syntax error at or near "b" LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a; use SELECT extract(EPOCH FROM b::INTERVAL) FROM a; or SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a; Would it not be simpler: test=> SELECT extract(epoch from b) from a; date_part --- 2592000 7776000 15552000 (3 rows) As the b field is already an interval. Regards Ian Barwick -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a new database with a TEMPLATE did not work
On 02/25/2013 02:49 PM, mkumbale wrote: Hi, I am new to PostgreSQL. I have an empty PostgreSQL DB containing tables but no data. I issued the following command in PGADMIN SQL editor: CREATE DATABASE "NewDefault" WITH OWNER = postgres ENCODING = 'UTF8' TEMPLATE = Default TABLESPACE = pg_default LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1; Although it created the NewDefault DB, it does not contain any of the tables in Default. Default was disconnected when I executed this command. What am I doing something wrong? So you have a database named Default? Probably not a good name because: http://www.postgresql.org/docs/9.2/interactive/sql-createdatabase.html template The name of the template from which to create the new database, or DEFAULT to use the default template (template1). So at a guess you are actually creating the new database from template1. Thanks, Murali -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-a-new-database-with-a-TEMPLATE-did-not-work-tp5746627.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Reading from file without superuser privilege
On 03/21/2013 04:43 AM, Jose Antonio Quintana/UPC wrote: I need to read from a file in order to update a table. The manual says that it is necessary to have the superuser privilege to read from a file. Is it possible to read files without this privilege? What sort of file, any file or one you want to do a COPY or \copy from? For any file you would need to use one of the untrusted languages, plpythonu for example. They need to be installed by a superuser. It is possible to create a function in an untrusted language as the superuser and then confer the superuser privileges to other users for that function by using SECURITY DEFINER, see here: http://www.postgresql.org/docs/9.2/interactive/sql-createfunction.html For COPY : "The file must be accessible to the server and the name must be specified from the viewpoint of the server. " http://www.postgresql.org/docs/9.2/interactive/sql-copy.html For \copy: "This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required." http://www.postgresql.org/docs/9.2/interactive/app-psql.html Thanks. ___ José Antonio Quintana Romero Unitat de Projectes Vicegerència de Desenvolupament Organitzatiu i Personal Edifici Vèrtex. Planta 3 Pl. Eusebi Güell, 6 08034 - Barcelona -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres trigger issue with update statement in it.
On 04/03/2013 09:08 PM, Kaleeswaran Velu wrote: Hello Friends, I am new to Postgres DB. Recently installed Postgres 9.2. Facing an issue with very simple trigger, tried to resolve myself by reading documents or google search but no luck. I have a table A(parent) and table B (child). There is a BEFORE INSERT OR UPDATE trigger attached in table B. This trigger has a update statement in it. This update statement should update a respective record in table A when ever there is any insert/update happen in table B. The issue here is where ever I insert/update record in table B, getting an error as below : ** Error ** ERROR: cannot begin/end transactions in PL/pgSQL SQL state: 0A000 Hint: Use a BEGIN block with an EXCEPTION clause instead. Context: PL/pgSQL function func_update_payment() line 53 at SQL statement Line no 53 in the above error message is an update statement. If I comment out the update statement, trigger works fine. Can anyone shed some lights on this? Your help is appreciated. Without seeing the actual code it is a guess, but here it goes. From this: ERROR: cannot begin/end transactions in PL/pgSQL I would say you tried to create a transaction in the function. Could be you wrote BEGIN; instead of BEGIN ... END; Thanks and Regards Kaleeswaran Velu -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] transaction isolationa level - SERIALIZABLE
On 05/13/2013 02:22 AM, Marcin Krawczyk wrote: Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED) I don't think so: http://www.postgresql.org/docs/9.1/interactive/config-setting.html "Furthermore, it is possible to assign a set of parameter settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands ALTER ROLE and ALTER DATABASE, respectively, are used to configure these settings. Per-database settings override anything received from the postgres command-line or the configuration file, and in turn are overridden by per-user settings; both are overridden by per-session settings. regards mk -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] table constraint on two columns
On 07/22/2013 07:16 AM, Vik Fearing wrote: On 07/22/2013 04:05 PM, ldrlj1 wrote: Postgres 9.2.4. I have two columns, approved and comments. Approved is a boolean with no default value and comments is a character varying (255) and nullable. I am trying to create a constraint that will not allow a row to be entered if approved is set to false and comments is null. CHECK constraints work on positives, so restate your condition that way. A row is permissible if approved is true or the comments are not null, correct? So... ...add constraint chk_comments (approved or comments is not null)... This does not work. yada, yada, yada... add constraint "chk_comments' check (approved = false and comments is not null). The constraint is successfully added, but does not work as I expected. That's not the same check as what you described. An additional comment, did you put the check constraint on a column or the table? From the docs: http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html: .. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns... -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] openclinica
On 09/30/2013 04:20 PM, hugh holston wrote: I cant understand why I am not able to access and open my openclinica webpage, . So what do I do. My guess is you will get a answer sooner here: https://community.openclinica.com/forums/users-email-forum -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 09:48 AM, JORGE MALDONADO wrote: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. The way I think of it is, that since the SQL standard does not mention indices an INDEX (UNIQUE or otherwise) is just the databases application of a constraint. So for practical purposes they are the same thing. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 10:41 AM, luca...@gmail.com wrote: Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between "unique index" and "unique constraint" in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Not sure I follow, you can have a unique index that is not a primary key. A primary key is special kind of unique index: http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select into
On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote: > Hi, > Thanks very much for all the suggestions. Like Andreas said i have to > use EXECUTE to do this. That was my guess too but i was putting the INTO > cluase into the string before executing it. This is a step forward. The > problem now is getting the into to work with a record. Am using a RECORD > variable after declaring it in the INTO clause. pg is complaining > >ERROR: record "svc_data_rec" is not assigned yet >DETAIL: The tuple structure of a not-yet-assigned record is > indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute > statement > >I read in the docs the following, "The INTO clause specifies where the > results of a SELECT command should be assigned. If a row or variable list > is provided, it must exactly match the structure of the results produced by > the SELECT (when a record variable is used, it will configure itself to > match the result's structure automatically)." That i take to mean that the > above is ok. But it ain't. > > Thanks again for your guys help... > > "A. Kretschmer" <[EMAIL PROTECTED]> wrote: am Wed, dem 22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes: > > Hi guys, > > > >Am new to sql scripting so this might be a stupid question. Am getting > > an error while trying to do the following > > > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > > > where svc_data_rec is defined as record and svc_tbl_name is a varchar > > that holds the name of a table and sub_id is another varchar. the error > > message is > > You should rewrite your plpgsql-function. You can't handle with > string-vars in this way, you must create a string with your complete sql > and EXECUTE this string. > > Read > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG >SQL-STATEMENTS-EXECUTING-DYN > > > Andreas My guess is that the error message is correct, the svc_data_rec has not had any values assigned to it. In other words the EXECUTE statement is not working the way you think it is. Could you post the EXECUTE string? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] select into
On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: > Mulham freshcode <[EMAIL PROTECTED]> writes: > > execute sql_str1 into svc_data_rec ; > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > This should work --- in PG 8.1 or later. In older versions you'd have > to fool around with a FOR ... IN EXECUTE ... loop. > > regards, tom lane The documentation for pl/pgsql in 8.1 and higher says different. http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported within EXECUTE." The change is reflected in the HISTORY text though. How is the best way to get a change made to the documentation? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] select into
On Friday 24 November 2006 06:55 am, Andreas Kretschmer wrote: > Adrian Klaver <[EMAIL PROTECTED]> schrieb: > > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: > > > Mulham freshcode <[EMAIL PROTECTED]> writes: > > > > execute sql_str1 into svc_data_rec ; > > > > > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > > > > > This should work --- in PG 8.1 or later. In older versions you'd have > > > to fool around with a FOR ... IN EXECUTE ... loop. > > > > > > regards, tom lane > > > > The documentation for pl/pgsql in 8.1 and higher says different. > > > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PL > >PGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported > > within EXECUTE." > > Thats right. The solution with 8.1 or later is: > > EXECUTE INTO var, and _not_ SELECT INTO var. > > > Andreas Thanks for the information. Somewhere along the line I missed the distinction. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] select into
On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote: > Hi Tom, > > Thanks for the help. Am using version 8.0 and it seems like RECORD is not > that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it > does the trick. But am still finding it hard to move forward with this. I > have the name of table field in a varchar variable that i got from > information_schema.columns and I have the records variable that stores the > contains the data from that table. Usually I'd do something like > data_rec.col_name to extract the data from the record but now I don't know > the name per se. how can i say something like data_rec[col_name] where > col_name is a variable that has the actual column name. I found no examples > in the docs that explain this. Can it be done in version 8.0.1? > > I find variable substitution kind of confusing. I mean why is there no way > of saying explicitly replace this variable with its content before > executing the statement? > > Sorry for the long question, > and thanks again for the help > > Mustafa... > > Tom Lane <[EMAIL PROTECTED]> wrote: Mulham freshcode writes: > > execute sql_str1 into svc_data_rec ; > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > This should work --- in PG 8.1 or later. In older versions you'd have > to fool around with a FOR ... IN EXECUTE ... loop. > >regards, tom lane > I am trying to sort this out. Are you trying to find the data for a single field from each table, or for some set of fields?. If you are looking for data from a single field couldn't you dispense with the RECORD variable and just build a query of the form SELECT col_name FROM tbl_name. If you want to go through a set of fields then it would involve some nested loops. -- Adrian Klaver [EMAIL PROTECTED] ---(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] select into
On Friday 24 November 2006 08:17 pm, Mulham freshcode wrote: > Hi Adrian, > > I have number of similar tables that have different number of fields > (similar in functionality). An in my stored procedure am trying to select a > row from one of these tables (that i don't know in advance, hence the use > of record) and return the data in the form of a table that has > column_name:value pairs. where column name is that from the original table. > I have no problem finding the column names but I don't know how to say > data[column_name] to get the corresponding value. Is there a way to do it > in pgsql? > > here is my code so far > > sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || > sub_id ; > > for svc_data_rec in execute sql_str1 loop > end loop; > > -- get service_user table's column names > for col_name in select column_name > from information_schema.columns > where table_name~svc_tbl_name loop > > raise notice 'Column name:%', col_name.column_name; > raise notice 'Value: %', svc_data_rec[col_name.column_name]; > > end loop; > > Thank you, > > Mustafa ... > I am afraid I can't make it work either. -- Adrian Klaver [EMAIL PROTECTED] ---(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] select into
On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:
>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a
solution using pl/pythonu.
The function is as follows-
CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):
for j in range(len(cols_rs)):
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],
data_rs[i][cols_rs[j]['column_name'] ]))
$Body$
LANGUAGE plpythonu;
For this to work I created a table dat_col(table_name text,column_name
text,column_data text). The function deletes old data from the table before
it is run, based on table name. Just run as dat_col_py("table name"). This
assumes you have pl/pythonu installed.
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] select into
On Monday 27 November 2006 06:31 pm, Mulham freshcode wrote: > Hi Adrian, > > Thanks very much for your help...it is a pity you can't do this in plpgsql > coz i have almost every thing else I need, and I hate to use yet another > language. What does it take to add this mechanism to the language? Is any > one planning to add it? It can come it handy I bet. > > Thanks again, > > Mustafa... > It may not be a shortcoming of the language, but a shortcoming in how well I understand it. I haven't given up hope I can make it work in plpgsql. The problem was I was going in circles. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Grants
On Friday 01 December 2006 06:03 am, Ezequias Rodrigues da Rocha wrote: > > 2006/11/30, imad <[EMAIL PROTECTED]>: > > You did not grant access privileges to schema. > > Also GRANT administrators on the base schema as you did for the table. > > > > --Imad > > www.EnterpriseDB.com > > > > On 12/1/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > > Hi list, > > > > > > I am having problem with grants and users on PostgreSQL. > > > > > > I am using pgAdmin to connect like other user to test my permissions. > > > > > > As the owner of the database I have criated two roles: > > > > > > administrators (cannot connect) > > > ezequias (can connect) > > > > > > I give permissions to a table I have: > > > GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators; > > > > > > > > > My user: > > > CREATE ROLE ezequias LOGIN > > > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; > > > GRANT administradores TO ezequias; > > > > > > My group > > > CREATE ROLE administradores > > > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; > > > > > > When I try to access the table base.table1 with ezequias login the > > > pgAdmin reports: > > > (see attached image) > > > > > > Could someone tell me what I did wrong ? > > > Ezequias > > > > > > > > > > > > ---(end of > > > broadcast)--- TIP 5: don't forget to increase > > > your free space map settings > Schema grants > > CREATE SCHEMA base > AUTHORIZATION root; > GRANT ALL ON SCHEMA base TO root; > GRANT USAGE ON SCHEMA base TO administrators; > > Table grants > GRANT ALL ON TABLE base."local" TO root; > GRANT SELECT, UPDATE, INSERT ON TABLE base."local" TO administrators; > > Still the same problem. :( Two things I see. The first may only be a translation artifact. You have CREATE ROLE administradores and then GRANT to administrators on the table. Second in the CREATE ROLE ezequias you have NOINHERIT. This means ezequias does not automatically assume the privileges of the ROLES it belongs to. To acquire the privileges you have to do a SET ROLE administrators at the connection. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
