[SQL] hints or suggestion for optimizer
How can I optimize Postrges SQL when I would like have ordering kolumns without clause ORDER BY - in Oracle you can use hints to suggestion optimizer. for example. SELECT /*+ INDEX_ASC TAB(TAB_PK) +/ * FROM TAB - most efficent SELECT * FROM TAB ORDER BY TAB_PK where TAB_PK - is unique index on KOD column WHERE CLAUSE - without OR ,UNION, accept LIKE, AND etc Does similar mechanizm has Postgres ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] psql question
>>>>> "Clayton" == clayton cottingham <[EMAIL PROTECTED]> writes: Clayton> you could use a version of pgsql on your own machine and Clayton> use the host switch to connect to an extrenal db Clayton> of course your db isp will have to allow your ip to Clayton> connect, not an easy thing if your dial up!! Or, assuming you have ssh access, use ssh to setup a tunnel between the two. Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
[SQL] bytea
Hello, I have a table containing a field of type bytea: CREATE TABLE a_table ( a_field bytea ); How can I import a file in a SQL script? What function I can use? Thank you very much. Adrian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] bytea
Hi Christoph, Thanks for your reply. But what I want to do is loading a file of a particular path with a sql statement in psql. Why I need to care about how the file looks like? Thanks. Adrian - Original Message - From: "Christoph Haller" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, February 05, 2003 6:15 PM Subject: Re: [SQL] bytea > > > > I have a table containing a field of type bytea: > > > > CREATE TABLE a_table ( > > a_field bytea > > ); > > > > How can I import a file in a SQL script? What function I can use? > > > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed > significantly since) > > Octets of certain values must be escaped (but all octet values may be > escaped) when used as part of a string literal in an SQL > statement. In general, to escape an octet, it is converted into the > three-digit octal number equivalent of its decimal octet value, and > preceded by two backslashes. > > In general it goes like this > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; > to load the first four ASCII characters. > You did not mention how your file looks like. > There is also a C function available called PQescapeBytea > which does all the required escaping to store memory areas in bytea > columns. > Refer to Command Execution Functions within libpq - C Library for > details. > > Regards, Christoph > > > > ---(end of broadcast)--- > TIP 3: 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 > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
[SQL] Trick to 'run' a view on two databases and combine the result ?
Hello, I have a 'big problem' : I have to show some data from two identical databases so I need to run a querry (view, ..etc) on both databases and show the united result ... Any ideea how to obtain this result ? Thank You, Adrian Din -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Record type in sql
Hello, I have a little problem I want to declare a type record for later use like that create type record_structure1 as (id int2, nume text); that is ok! next in a function I want to use something like that: select * from table as record_structure1 ? instead of writing select * from table as t1(id int2, nume text); is this possible? Thank You, Adrian Din -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] private table
Hello, I am want to use a private table in postgresql(every client to see his own data). Is this possible? How can I do it! Thank you, Adrian Din -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [PERFORM] Tunning postgresql on linux (fedora core 3)
sorry about cc ... this is the site: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html but I gues is not right ... hmm Adrian Din On Thu, 03 Feb 2005 14:52:04 +, Richard Huxton wrote: I'll repeat myself: Please CC the mailing list as well as replying to me, so that others can help too. Din Adrian wrote: On Thu, 03 Feb 2005 13:56:50 +, Richard Huxton wrote: Please CC the mailing list as well as replying to me, so that others can help too. b) in docs say that after 7.2 seting this to false does'n turn off the wall ...!? wich option does? The docs don't say that, as far as I can see. It doesn't make sense to turn off the WAL. hmm this is the doc about ... ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop checkpointing, however. This is a change in the notes that follow Turn WAL off (fsync=false) only for a read-only database or one where the database can be regenerated from external software. While RAID plus UPSes can do a lot to protect your data, turning off fsync means that you will be restoring from backup in the event of hardware or power failure.' I don't know what this is, and you don't give a URL, but it DOES NOT appear to be in the manuals. You should probably read the sections of the manuals regarding "run-time configuration" and "write ahead logs". The manuals are quite extensive, are available online at http://www.postgresql.org/ and also in most distributions. This is probably a good place to start. http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL If you turn it off you should have more speed ... !!!??? Basically, as I said in my last email - fsync=true makes sure transaction details are safely stored on disk. If you turn this off, the database doesn't have to wait for the data to physically be written to the disk. But, if power fails then data might be in OS or disk cache and so lost when you restart the machine. Please CC the mailing list if you reply to this message. -- Richard Huxton Archonet Ltd -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL error: function round(double precision, integer) does not exist
the round sintax is round(numeric,int) not round (double,int) you must cast the value into numeric: ex: round (cast(doublecolumn as numeric),2) should work ok Adrian Din, Om Computer & SoftWare On Sun, 27 Feb 2005 15:26:07 -0800, TJ O'Donnell <[EMAIL PROTECTED]> wrote: I received the following error when executing a SQL statement: SQL error: ERROR: function round(double precision, integer) does not exist In statement: select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count, round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa from structure,tpsa where id < 237610 and oe_count_matches(smiles,smarts) > 0 order by id; The functions described at: http://www.postgresql.org/docs/7.4/static/functions-math.html show that round(numeric,int) should work ok. If I use round() without a second argument, it works OK, but this gives a loss of precision which I do not want. Can anyone help me with this? Thanks, TJ ---(end of broadcast)--- TIP 3: 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 -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL and Delphi 6
we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is the server side cursor = doesn't work properly ... so we are using client side for datasets :) On Wed, 15 Jun 2005 23:49:29 -0400, Postgres Admin <[EMAIL PROTECTED]> wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL and Delphi 6
Yes, the client must have the psqlodbc driver and mdac at least 2.6. (Mdac2.5 is verry buggy - attention win2000 and win9x must be upgraded if you need mdac for your app). When using server side cursors for a dataset the update and delete functions act 'strange' as not refreshing corect the affected rows or by showing ony ane record for 20 times instead of 20 different records ... (this test was done with psqlodbc8.0 - postgresql DB 8.0 - I think my colegs didn't test it with 8.1 yet !! ). So we are using client side cursors - slower then server side cursors, but we are satified with the results (we are developing a big ERP app for two years - it also works over internet on 2-3 clients with relative slow net connections : 56-128 Kb/s). Adrian Din, Om Computer & Software, Bucuresti,Romania On Thu, 16 Jun 2005 08:09:56 -0400, Postgres Admin <[EMAIL PROTECTED]> wrote: So you installed psqlodbc 8 on the client machine with Delphi installed, correct? What problems did you have with cursors? Any other suggestions? Thanks a lot for the help! J Din Adrian wrote: we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is the server side cursor = doesn't work properly ... so we are using client side for datasets :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PostgreSQL and Delphi 6
We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] PostgreSQL and Delphi 6
I am sorry - I don't understand (or my english is bad or I don't know what you mean). What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). So: When the user push the 'post' button the driver automatically refresh the current dataset and the user have to wait many seconds (or min?!) until it's finish. ok? How did you say you avoided this ? Adrian Din, Om Computer & SoftWare Bucuresti, Romania On Thu, 16 Jun 2005 10:29:38 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] UPDATEABLE VIEWS ... Examples?
An example(found it some time ago somewhere ?! :) ): /* drop view a_and_b cascade; drop table tbla cascade; drop table tblb cascade; */ CREATE TABLE tbla ( id int4 NOT NULL, a int4, b varchar(12), CONSTRAINT tbla_pk PRIMARY KEY (id) ) --WITHOUT OIDS ; CREATE TABLE tblb ( id int4 NOT NULL, x bool, y timestamp, CONSTRAINT tblb_pk PRIMARY KEY (id), CONSTRAINT tblb_fk FOREIGN KEY (id) REFERENCES tbla (id) ON UPDATE CASCADE ON DELETE CASCADE ) --WITHOUT OIDS ; INSERT INTO tbla VALUES ( 3, 9034, 'test1' ); INSERT INTO tbla VALUES ( 6, -23, 'test2' ); INSERT INTO tblb VALUES ( 3, false, now() ); INSERT INTO tblb VALUES ( 6, true, now() ); CREATE OR REPLACE VIEW a_and_b AS SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y FROM tbla NATURAL LEFT JOIN tblb; CREATE OR REPLACE RULE a_b_insert AS ON INSERT TO a_and_b DO INSTEAD ( INSERT INTO tbla (id, a, b) VALUES (new.id, new.a, new.b); INSERT INTO tblb (id, x, y) VALUES (new.id, new.x, new.y); ); -- test your insert INSERT INTO a_and_b VALUES (99, 123, 'text', false, now() ); CREATE OR REPLACE RULE a_and_b_del AS ON DELETE TO a_and_b DO INSTEAD DELETE FROM tbla WHERE tbla.id = OLD.id; -- test your delete DELETE FROM a_and_b WHERE id=99; CREATE OR REPLACE RULE a_and_b_upd AS ON UPDATE TO a_and_b DO INSTEAD ( UPDATE tbla SET a = new.a, b = new.b WHERE tbla.id = new.id; UPDATE tblb SET x = new.x, y = new.y WHERE tblb.id = new.id ; ); -- test your update UPDATE a_and_b SET a=-333, b='neotext', x=false, y='2005-6-6' WHERE id=1; ... it works ok in pgadmin ... PS: but for me is a problem - I can't do update from delphi7 : Error is: "row cannot be located for updating" ... this is because I do 2 updates in rule of update view and the odbc driver (psqlodbc ) or delphi wants to do update based on every field ... (also is no key in view!!!???) ... if anybody have a solution to this problem !? Best Regards, Adrian Din, Om Computer & SoftWare Bucuresti, Romania -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help on Procedure running external function
Hello, I have a 'big' problem: I am trying to run from a procedure a function witch generate a new document number (max from table +1 ) and after to insert a document with this number, but the function returns me the same number each time because the tranzaction is not finished and the inserts are not commited and of course the next document number is the same. ... for ... loop nr=get_me_next_number(params); insert into table values (nr,...) end loop ... ... error inserting in table .. primary_key nr . Is any way in making the external function to 'know' that I inserted another row but this insert is in a tranzaction that is not finish yet ? ar onother solution ? for now I 'solved' by asking for a nr once and generate myself next number (+1) but this is not a correct solution (in this time somebody else could insert a document with the same nr as the procedure ) thank you, Adi -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 3: 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] Help on Procedure running external function
Yes , I know what sequence is, but our procedure for generating doc numbers is v. big and has manny (4) parameters and we did'nt use sequence in it for this reason any other advice ? thak you, Adi On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote: Din Adrian wrote: Hello, I have a 'big' problem: I am trying to run from a procedure a function witch generate a new document number (max from table +1 ) and after to insert a document with this number, but the function returns me the same number each time because the tranzaction is not finished and the inserts are not commited and of course the next document number is the same. ... for ... loop nr=get_me_next_number(params); insert into table values (nr,...) end loop ... ... error inserting in table .. primary_key nr . Is any way in making the external function to 'know' that I inserted another row but this insert is in a tranzaction that is not finish yet ? ar onother solution ? for now I 'solved' by asking for a nr once and generate myself next number (+1) but this is not a correct solution (in this time somebody else could insert a document with the same nr as the procedure ) thank you, Adi I think the best solution is to use a sequence, not "select max(id) +1 from table". Look here: http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html Bye ---(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 -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help on Procedure running external function
1. the function get_me_next_number is runing from this procedure (same
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each
maxnrdoc value - but the temp table give me sometimes a relation with OID
# does not exist - problem that I can only solve by using only
execute - but I don't think I can ...?! :))
here is the example:
create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare;
if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values
(dsgroup.magazie_implicita_lansare,vNrBon);
else
update MagMaxNrBon set maxnrbon=vNrBon where magazie =
dsgroup.magazie_implicita_lansare;
end if;
...
and in this way vNrBon is correct one ... I will try to use oly execute on
insert,update and select on temp table MagMaxNrBon (o_gen_calc_nr_doc
is the "get_me_next_number" function)
thank you,
Adria Din
On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Yes , I know what sequence is, but our procedure for generating doc
numbers is v. big and has manny (4) parameters and we did'nt use
sequence in it for this reason
any other advice ?
I think there is no way to have any information about non committed
transactions.
I don't know if I understand well your problem but from what I see
"get_me_next_number" function runs in a different transaction (Why? Is
it an externale procedure that make its own connection to the DB?)),
otherwise it would see the new inserted number. The better solution is
to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by
yourself the number (as you do) and lock the table until you end to
prevent others inserting documents.
I hope this helps you.
Bye
thak you,
Adi
On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a new
document number (max from table +1 ) and after to insert a document
with this number, but the function returns me the same number each
time because the tranzaction is not finished and the inserts are
not commited and of course the next document number is the same.
...
for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
...
error inserting in table .. primary_key nr .
Is any way in making the external function to 'know' that I
inserted another row but this insert is in a tranzaction that is
not finish yet ? ar onother solution ?
for now I 'solved' by asking for a nr once and generate myself
next number (+1) but this is not a correct solution (in this time
somebody else could insert a document with the same nr as the
procedure )
thank you,
Adi
I think the best solution is to use a sequence, not "select max(id)
+1 from table". Look here:
http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye
---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help on Procedure running external function
I 'solved' my 'relation with OID # does not exist' problem using only
execute on temp table ...
Is no need for me to 'lock'(for update) the temp table - is temporary
(lock at 'create temp table MagMaxNrBon')- everybody has his own copy
Your right I should change the get_number procedure to create 'on the fly
sequences' for each new user settings (in our app the user have the power
to set the way of generating number for every type of document) (for that
I need time wich I don't have now :) )...
anyway .. is working (not how I wanted but it's ok)
thank you,
Adrian Din
On Tue, 05 Jul 2005 13:47:24 +0200, Zac <[EMAIL PROTECTED]> wrote:
I think you should use 'FOR UPDATE' clause in your first "select":
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare FOR UPDATE;
In this way you lock the rows eventually returned and no one can update
them (or select them "for update") until your transaction finished.
Is this good for you?
However IMHO you should think your procedures to use sequences...
1. the function get_me_next_number is runing from this procedure (same
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store
each maxnrdoc value - but the temp table give me sometimes a relation
with OID # does not exist - problem that I can only solve by
using only execute - but I don't think I can ...?! :))
here is the example:
create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare;
if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values
(dsgroup.magazie_implicita_lansare,vNrBon);
else
update MagMaxNrBon set maxnrbon=vNrBon where magazie =
dsgroup.magazie_implicita_lansare;
end if;
...
and in this way vNrBon is correct one ... I will try to use oly
execute on insert,update and select on temp table MagMaxNrBon
(o_gen_calc_nr_doc is the "get_me_next_number" function)
thank you,
Adria Din
On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Yes , I know what sequence is, but our procedure for generating
doc numbers is v. big and has manny (4) parameters and we did'nt
use sequence in it for this reason
any other advice ?
I think there is no way to have any information about non committed
transactions.
I don't know if I understand well your problem but from what I see
"get_me_next_number" function runs in a different transaction (Why?
Is it an externale procedure that make its own connection to the
DB?)), otherwise it would see the new inserted number. The better
solution is to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by
yourself the number (as you do) and lock the table until you end to
prevent others inserting documents.
I hope this helps you.
Bye
thak you,
Adi
On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a
newdocument number (max from table +1 ) and after to insert a
document with this number, but the function returns me the same
number each time because the tranzaction is not finished and the
inserts are not commited and of course the next document number
is the same.
...
for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
...
error inserting in table .. primary_key nr .
Is any way in making the external function to 'know' that I
inserted another row but this insert is in a tranzaction that is
not finish yet ? ar onother solution ?
for now I 'solved' by asking for a nr once and generate myself
next number (+1) but this is not a correct solution (in this
time somebody else could insert a document with the same nr as
the procedure )
thank you,
Adi
I think the best solution is to use a sequence, not "select max(id)
+1 from table". Look here:
http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye
---(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] Help on Procedure running external function
I am sorry but: the table is TEMPORARY ... I don't need to do any lock on it (eventualy I should do a lock on the table where I want to insert the documents - the MagMaxNrBon is only a temp table used for storing the max document nr for each user's setings in this transaction - I did that temp table instead of running always the return_next_number function (the problem I postit first) - I run once the function for every user's settings - get the max doc free number, store in the temp table, do the insert in onother table and next time I get this number and raise by 1 instead of running again the get_next_number function (because as I said - it give the same max number (the insert into table is not 'visible') )) ... as I said it's 'solved' ... thank you, Adrian Din On Tue, 5 Jul 2005 07:39:48 -0500, Bruno Wolff III <[EMAIL PROTECTED]> wrote: On Tue, Jul 05, 2005 at 13:47:24 +0200, Zac <[EMAIL PROTECTED]> wrote: I think you should use 'FOR UPDATE' clause in your first "select": select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie = dsgroup.magazie_implicita_lansare FOR UPDATE; In this way you lock the rows eventually returned and no one can update them (or select them "for update") until your transaction finished. That doesn't work because you don't lock rows which aren't in the table yet. You need to use a lock table instead. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 : [email protected] > > 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 ([email protected])
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
--
Adrian Klaver
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Help with queries.
Dear group, I just started learning postgres and I have to analyze my data. So please bear with me, for all the simple questions that I am asking. Apologies. I have a table: snps table id | sample_id | chromosome | from | to | 1 1chr1 10 11 2 1 chr1 14 15 3 2 chr1 14 15 4 2 chr19 10 5 3 chr11415 6 3 chr14 3536 7 3 chr14 3940 8 2 chr14 3940 9 2 chr14 4344 101 chr14 4344 gene table: id | chromosome | from | to | genename 1 chr14 20SRC 2 chr1 25 45SRC 3 chr1 80 100 CSK 4 chr1 120 140 CSK My aim is to for a gene in gene table (SRC that has two ranges 4-20 and 25-45), I want to map all coordinates in snps table. Then I want to get those entries where sample 1 and sample 2 have in common and sample 1 and sampl3 in common. I do not know how to get these entries. Could any one help me please. thank you. adrian -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help with queries.
3 | chr19 | 38178828 | 38178829 | 0 | 0 | 0 |28 1 | chr19 | 38182424 | 38182425 |14 | 0 |54 | 0 2 | chr19 | 38182424 | 38182425 |17 | 0 |55 | 0 3 | chr19 | 38182424 | 38182425 |13 | 0 |26 | 0 Now from table snps, I want to get results that satisfy conditions that gave result X and combine this with results Y. How is it possible to do this? Thank you for your help. Adrian. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] unique fields
hi: I have a fairly large table. sample_id | chr | cfrom | cto | --- 1c219 20 2c219 20 3c219 20 1c510 11 3c510 11 (25,000 rows) I want to find out how many duplications are there for chr, cfrom and cto a. c2,19,20 are common to samples 1,2 and 3. since there will be many instances like that, do I have to loop over entire rows and find common chr, cfrom and c2 and ouput with sample_id. how can I do that. thanks adrian -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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: [email protected] > 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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:[email protected]>> 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] <mailto:[email protected]> *(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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) 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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
