Re: [SQL] unused columns in copy
On Mon, Jul 20, 2009 at 3:22 PM, chester c youngchestercyo...@yahoo.com wrote: is there a way for COPY FROM to ignore unused columns in CSV? in other words, if table t1 has columns c1, c2, and if csv has columns c1, c3, c2, could I do something like COPY t1( c1, null, c2 ) FROM 'file.csv' No, but you can use pgloader which can easily deal with your problem and much more. http://pgfoundry.org/projects/pgloader/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Oracle to PostgreSQL
On Mon, Mar 16, 2009 at 12:35 AM, Greenhorn user.postgre...@gmail.com wrote: Hi, I have almost 1300 files from Oracle (no access to oracle server). I have to create the tables and data as follows. (...) snip (...) Any recommendation is greatly appreciated :) Try here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] xpath_table feature
Hello.
I've read the documentation, I think I have even found the article
after which this part of documentation has been updated
(http://markmail.org/thread/ys2a2yph5zgmnls5) but I still can't write
appropriate query :-(.
Let's consider the following:
create table test_xml (id serial primary key, xml text);
insert into test_xml (xml) values ('?xml version=1.0 encoding=utf-8?
lev1 attr1=a
lev2 attr2=x attr3=y
lev3 attr4=3
lev4
attr5=aaa/lev4
/lev3
/lev2
lev2 attr2=o attr3=u
lev3 attr4=7/lev3
/lev2
lev2 attr2=l attr3=w
lev3 attr4=9
lev4
attr5=bbb/lev4
/lev3
/lev2
/lev1');
Please note that second /lev1/lev2/lev3 ends here and has no lev4 child node
SELECT * from xpath_table ('id',
'xml',
'test_xml',
$$/lev1/lev2/@attr2|$$
|| $$/lev1/lev2/@attr3|$$
|| $$/lev1/lev2/lev3/lev4/@attr5$$
,
'id=1') AS (
id int,
attr2 text,
attr3 text,
attr5 text
) ;
gives:
id | attr2 | attr3 | attr5
+---+---+---
1 | x | y | aaa
1 | o | u | bbb
1 | l | w |
I think I understand why this happens, that's because '|' in xpath
indicates a simple join and /lev1/lev2/lev3/lev4/@attr5 returns the
first matched node.
However, I'd like to have:
id | attr2 | attr3 | attr5
+---+---+---
1 | x | y | aaa
1 | o | u |
1 | l | w | bbb
Could you please show me the way to achieve this?
Thank you for your time
Regards,
Marcin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] alter column from varchar(32) to varchar(255) without view re-creation
On Tue, Feb 24, 2009 at 4:27 PM, Emi Lu em...@encs.concordia.ca wrote: Good morning, I am using PostgreSQL 8.0.15. Is there a way that I can easily alter column type from varchar(32) to varchar(255) but do not have to worry about views dependent on it? You should test it carefully and it is considered a bad practice - I'll probably get sued for recommending this :-), but you may try: SELECT * from pg_attribute where attname = 'colname' and attrelid = (SELECT oid FROM pg_class WHERE relname='_tablename'); UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255 where attrelid = _attrelid_from_above_ and attname = 'colname'; Regards, Marcin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to change a view's owner in postgres
On Fri, Aug 1, 2008 at 11:41 AM, Anoop G [EMAIL PROTECTED] wrote: How to change a view's owner in postgres? ALTER TABLE view_name OWNER TO new_owner; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Function returning setof taking parameters from another table
Hello. I've got a function which returns set of records: select * from f_test(123); param | val1 | val2 --- 123 | 1 | 17 123 | 2 | 18 I'd like to execute it multiple times with parameters from other query, like (it doesn't work of course): select * from f_test(x.id) join x on (x.id in (123,124,125)); in order to get: param | val1 | val2 --- 123 | 1 | 17 123 | 2 | 18 124 | 4 | 179 125 | 13 | 18 125 | 15 | 12 125 | 14 | 112 So far the only method I can think of is to use union all with different parametrs, like: select * from f_test(123) union all select * from f_test(124) union all select * from f_test(125); But it is not flexible, I'd like to have parameters stored in another table. I can also write a pl/pg function, create a string like this with unions and EXECUTE it. However, it seems ugly to me. Is there another way? Regards, Marcin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add a ROWCOUNT to the output of a select.
On Wed, May 14, 2008 at 1:54 AM, Gavin 'Beau' Baumanis [EMAIL PROTECTED] wrote: Hi Everyone, After spending some time searching through our good friend Mr. Google and the mailing list I found a post that provided me with a query that does just what I need. I think that this should be helpful: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Add a ROWCOUNT to the output of a select.
On Wed, May 14, 2008 at 10:40 AM, Harald Fuchs
[EMAIL PROTECTED] wrote:
I think a sequence is much simpler:
create temp sequence tmp;
select nextval('tmp') as rownum,
contactdate
from
myTable
where
contactdate '2007-06-30 23:59:59'
order by
contactdate;
I used to do it this way myself, but the solution in my previous post
is really worth the trouble.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Export Access 97 to PostgreSQL
On Thu, Mar 27, 2008 at 11:06 AM, Shavonne Marietta Wijesinghe [EMAIL PROTECTED] wrote: COPY REGIONI FROM (...) Strange coz i created the table. Why doesn't it seem to find it?? CREATE TABLE REGIONI You need to quote the name in your COPY command as well. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune [EMAIL PROTECTED] wrote: Hi, Is there any way to define a SQL stored function that inserts a row in a table and returns the serial generated? Maybe you just need INSERT ... RETURNING? http://www.postgresql.org/docs/8.2/interactive/sql-insert.html Insert a single row into table distributors, returning the sequence number generated by the DEFAULT clause: INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL stored function inserting and returning data in a row.
On Jan 11, 2008 4:23 AM, Daniel Caune [EMAIL PROTECTED] wrote: Please ignore my post. I havent' read your message carefully enough. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Advice for generalizing trigger functions
Dnia 25-12-2007, Wt o godzinie 16:20 -0800, Richard Broersma Jr pisze:
I've created quite a few functions that log modifications to various history
tables.
(the history table has the same name as the base table but is prefixed by the
'History.' schema.)
The only difference between functions I can find is the table name.
Is there any way to generalize these myriad of functions into one?
Maybe just try http://pgfoundry.org/projects/audittrail2/ ?
Or there's something my friend wrote some time ago (I use audittrail
now, but I think that this function works with reasonably recent
PostgreSQL releases). Sorry for Polish comments, I've just taken it out
from old repository.
CREATE OR REPLACE FUNCTION create_history_table(text, text)
RETURNS text AS
$BODY$
DECLARE
tabela ALIAS FOR $1;
query TEXT;
fields RECORD;
grupa ALIAS FOR $2;
BEGIN
query := '';
-- poczatek definicji zapytania tworzacego historie
query := 'CREATE TABLE H' || tabela || '(\r\n';
-- petelka wyciagajaca pola tabeli do ktorej tworzona jest historia
FOR fields IN
SELECT a.attname AS name,
format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || fields.name || ' ' || fields.type || ',\r\n';
END LOOP;
-- doklejenie pol dotyczacych historii
query := query || 'log_usename text default current_user, '
|| 'log_time timestamp default now(), '
|| 'log_event text default '
|| ');\r\n';
-- ustawienie uprawnien do zapisu i odczytu z tabeli historii
query := query || 'GRANT SELECT, INSERT ON H' || tabela || ' TO GROUP '
|| grupa || ';\r\n';
--EXECUTE query;
--query := '';
-- definicja funkcji dla triggera historii
query := query || 'CREATE FUNCTION H' || tabela || '() RETURNS trigger
AS\r\n'
|| '$$\r\n'
|| 'begin\r\n'
|| 'if ( tg_op = ''INSERT'' ) then\r\n'
|| 'INSERT INTO H' || tabela || ' (\r\n';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || '' || fields.name || ', \r\n';
END LOOP;
query := query || 'log_event ) VALUES (\r\n';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || 'NEW.' || fields.name || ', \r\n';
END LOOP;
query := query || '''I'');\r\n';
query := query || 'end if;\r\n'
|| 'if tg_op = ''UPDATE'' then\r\n'
|| 'if OLD.id != NEW.id then\r\n'
|| 'UPDATE H' || tabela || ' SET id = NEW.id WHERE
id = OLD.id;\r\n'
|| 'end if;\r\n'
|| 'INSERT INTO H' || tabela || '(\r\n';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || '' || fields.name || ', \r\n';
END LOOP;
query := query || 'log_event ) VALUES (\r\n';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || 'NEW.' || fields.name || ', \r\n';
END LOOP;
query := query || '''U'');\r\n';
query := query || 'end if;\r\n'
|| 'if tg_op = ''DELETE'' then\r\n'
|| 'INSERT INTO H' || tabela || '(';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
query := query || '' || fields.name || ', \r\n';
END LOOP;
query := query || 'log_event ) VALUES (\r\n';
FOR fields IN
SELECT a.attname AS name
-- format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = tabela )
AND a.attnum 0
LOOP
Re: [SQL] Potential bug in postgres 8.2.4
Dnia Thu, 24 May 2007 12:54:48 +0100, Tomas Doran napisał(a): If I can do something to make it work in the postgres backend, then that'd be acceptable, and I'm investigating that.. From what I know it's impossible without touching the source. This is, at the very least, is a glaring inconsistency around how IN clauses are handled in different situations. Yes, I think you are right. If this was a deliberate tightning of the behavior, is there a changelog entry/link to come docs about when this change happened that anyone can point me to? I am not able to trace this particular change right now (http://www.postgresql.org/docs/8.2/static/release.html). While you are right that these changes should be perhaps better documented, such comparisions were a bad thing to do in the first place (I've learned my lesson while upgrading from I think 7.1b3 to 7.1.3). Unfortunately I see no other option than fixing them in your application. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] system table storing sequence attributes
Dnia Tue, 22 May 2007 10:03:28 +0300, Sabin Coanda napisał(a): Hi there, I'd like to read the global sequence attribute currval, but not using currval function, because it is session dependent and requires a nextval function to be used before. Do you know where is stored this value in the system tables ? select last_value from sequence_name -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(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] system table storing sequence attributes
Dnia Tue, 22 May 2007 15:23:44 +0300, Sabin Coanda napisał(a): ... select last_value from sequence_name Unfortunately there is the same problem. The documentation say: It is an error to call lastval if nextval has not yet been called in the current session. and I don't want to call nextval before. I think that you either misunderstood this statement or try to break your application in a nasty way ;). Please tell us more about your problem and/or what do you want to achive, because sequences behave this way for a reason (concurrency issues). Perhaps there is another solution. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(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] DISTINCT ON not working...?
Dnia Tue, 20 Feb 2007 15:36:32 +1100, Phillip Smith napisał(a): Removing the CASE statement all together: SELECT DISTINCT ON (ean) ean, count(*) FROMTMPTABLE WHERE code NOT IN (SELECT code FROM stock_deleted) ANDean IS NOT NULL GROUP BY ean Still gives me: 3246576919422 2 Wild guess - have you tried reindexing this table? I haven't seen corrupted indexes since 7.1, though - it usually means subtle hardware problems. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(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] Schema sample
Dnia Mon, 22 Jan 2007 17:38:09 -0200, Ezequias Rodrigues da Rocha napisał(a): Hi list, I would like to ask you if there is any schema that someone can send me. I must make some Replication testes and would like to use a simple schema with two or three tables would be nice. You can find some sample databases here: http://pgfoundry.org/frs/?group_id=1000150 -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Postgresql Oracle Heteregenous services - strange behaviour
Dnia Wed, 17 Jan 2007 13:04:28 +, Richard Huxton napisał(a): That'd be my guess. And then it's not fetching any rows, expecting cursor-like behaviour. Of course we fetch all the rows before returning any results. The real solution would be to add LIMIT 0 or LIMIT 1 to the column-finding query, but I doubt that's possible with the Oracle plugin. Perhaps check if there's a fetch N rows at a time option for the ODBC setup that might help you. I've tried various settings, upgraded to psqlodbc 8.02.0200, looked into the source (info.c, retry_public_schema label) and there's a query which gets the columns (select n.nspname, c.relname, a.attname, a.atttypid (...)), I now can even see it in logs but there's still this additional select before. I think it's not psqlodbc problem, because when I issue my queries directly from isql from unixodbc everything seems normal - I get this additional select only when quering from Oracle using @my_server. Therefore, I think it is Oracle's fault and try to move this topic to Metalink. Thank you again. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Postgresql Oracle Heteregenous services - strange behaviour
I'm not sure if it's proper group, if not then please direct me to more appropriate one (unixodbc?) I've set up connection from oracle to postgresql via unixodbc and oracle's heterogeneus services. The connection works, but when I issue the following: select p_nr_pesel from zew_patients@my_postgresql where p_patient_id=19300; I see this in postgresql logs: [6210] DEBUG: query: select * from zew_patients [6210] DEBUG: query: SELECT A1.p_nr_pesel FROM zew_patients A1 WHERE (A1.p_patient_id = 19300) The point is, I don't why oh why the first select is issued (some cache?), but it slows the query a lot for obvious reasons. When I issue it for the second time in the same session, I see only the second select (that's how it should be from my point of view). After reconnecting I see both selects again :(. If it's known issue, I'd be grateful even for simple RTFM and a link :). Thank you for your time, Marcin -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgresql Oracle Heteregenous services - strange behaviour
Dnia Wed, 17 Jan 2007 13:04:28 +, Richard Huxton napisał(a): Andrew Sullivan wrote: I bet it's getting the column list from the table or some such thing. This is a lousy way to do it (the information_schema would be more correct, although maybe no faster). That'd be my guess. And then it's not fetching any rows, expecting cursor-like behaviour. Of course we fetch all the rows before returning any results. Bingo! select * from from zew_patients@my_postgresql results in select a1.col1, a1.col2, a1.col3 from zew_patients in postgresql logs. The real solution would be to add LIMIT 0 or LIMIT 1 to the column-finding query, but I doubt that's possible with the Oracle plugin. Perhaps check if there's a fetch N rows at a time option for the ODBC setup that might help you. Thank you both, I'll poke around and drop a note when I find something :). -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Finding gaps in scheduled events
Hello.
I've been struggling with this one for over a week, but for some reason my
mind isn't compatibile with the problem - it seems simple, yet I'm unable
to find the proper solution :(.
I have a timeline divided to 15 minute periods:
start |
--+
8:00 |
8:15 |
8:30 |
8:45 |
(...)|
14:45 |
Then, I have two types of events that fit the schedule. Event A takes
15 minutes, event B takes 30 minutes. They're stored in a table like this:
start | finish | type_id
--++
8:30 | 8:45 |1- type A
9:30 | 10:00 |2- type B
Now I need to create a query to find hours at which each of the type can
start. So, if it's event A (which take 15 minutes) it can start at:
8:00 (to 8:15)
8:15 (to 8:30)
( 8:30 to 8:45 is already taken )
8:45 (to 9:00)
9:00 (to 9:15)
9:15 (to 9:30)
( 9:30 to 10:00 (9:30-9:45 and 9:45-10:00) is already taken))
10:00 (to 10:15)
(...)
and if it's event B (which takes 30 minutes) it can start at:
8:00 (to 8:30)
8:45 (to 9:15)
9:00 (to 9:30)
10:00 (to 10:30)
(...)
I have to deal with the existing schema, but if it can be done in a
better way please let me know so I could avoid mistakes in my own programs
(although I think it's quite flexible and I like the concept).
Example tables:
create table test_events (
id serial,
start time,
finish time,
type_id integer );
insert into test_events ( start,finish,type_id ) values('8:30','8:45','1');
insert into test_events ( start,finish,type_id ) values ('9:30','10:00','2');
create table test_timeline as
SELECT
('0:00'::TIME + (my_day.h || ' minutes')::INTERVAL)::TIME as my_hour
FROM
generate_series (0,1425,15) AS my_day(h);
I don't paste my tries because they've all failed and I think I miss
something fundamental here.
Thank you very much for your time.
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
|So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org
---(end of broadcast)---
TIP 6: explain analyze is your friend
