Re: [SQL] triple self-join crawling
Define "crawling". Also, please post EXPLAIN and, if feasible, EXPLAIN ANALYSE output for your case. A On Sun, Mar 18, 2007 at 07:51:28PM +, T E Schmitz wrote: > The following self join of a table containing 5800 records is crawling: > > CREATE TABLE history > ( > stock VARCHAR(30) NOT NULL, > day date NOT NULL, > open NUMERIC (6,1) NOT NULL, > high NUMERIC (6,1) NOT NULL, > low NUMERIC (6,1) NOT NULL, > close NUMERIC (6,1) NOT NULL, > volume NUMERIC (12) NOT NULL, > PRIMARY KEY (stock,day) > ); > > > SELECT > history.stock, history.day, history.high, history.low, > MAX(past_week.high) AS week_high, > MAX(past_month.high) AS month_high > FROM history > INNER JOIN history AS past_month ON (past_month.stock = history.stock > AND past_month.day < history.day AND past_month.day >= (history.day - 30)) > INNER JOIN history AS past_week ON (past_week.stock = > past_month.stock AND past_week.day < history.day AND past_week.day >= > (history.day - 7)) > GROUP BY history.stock, history.day, history.high, history.low > ORDER BY history.stock, history.day DESC > > > How can I speed this up? > > > -- > > > Regards, > > Tarlika Elisabeth Schmitz > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] triple self-join crawling
T E Schmitz wrote: The following self join of a table containing 5800 records is crawling: SELECT history.stock, history.day, history.high, history.low, MAX(past_week.high) AS week_high, MAX(past_month.high) AS month_high FROM history INNER JOIN history AS past_month ON (past_month.stock = history.stock AND past_month.day < history.day AND past_month.day >= (history.day - 30)) INNER JOIN history AS past_week ON (past_week.stock = past_month.stock AND past_week.day < history.day AND past_week.day >= (history.day - 7)) GROUP BY history.stock, history.day, history.high, history.low ORDER BY history.stock, history.day DESC Things improved hugely when I changed the JOIN clauses: LEFT OUTER JOIN history AS past_month ON (past_month.stock = history.stockAND past_month.day >= (history.day - 30) AND past_month.day < history.day) LEFT OUTER JOIN history AS past_week ON (past_week.stock = past_month.stock AND past_week.day =past_month.day AND past_week.day >= (history.day - 7)) -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Encode
Hi list, It is possible to change the Encode of one database while it is on ? Sincerely Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Encode
For that you will need to drop and re-create it with a different encoding -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/19/07, Ezequias R. da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to change the Encode of one database while it is on ? Sincerely Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] [SQL] create view with check option
"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes: > On 3/19/07, Tom Lane <[EMAIL PROTECTED]> wrote: >>> ERROR: WITH CHECK OPTION is not implemented >> It seems perfectly clear to me ... > errors is clear, but maybe the information about check option should > be removed from docs to 8.2? There is no place in the 8.2 docs that claims it is implemented. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Multiple partition tables and faster queries
We've got three master tables: email, machines, and dests--each with seven
partition tables apiece. Each partitioned table is constrained to contain only
a single day's worth of data, with a CHECK contstraint
of the form
CHECK
(
***Timestamp >= 'start_constraint'
AND ***Timestamp < 'stop_constraint'
)
) INHERITS (dests);
and a rule
ON INSERT TO WHERE
(
Timestamp >= 'start_constraint'
AND Timestamp < 'stop_constraint'
)
DO INSTEAD
INSERT INTO
When we try to do a query across all three tables, when we only know the time
constraint on a column from one of them, the access to that particular table is
very fast, but the query planner is doing sequential scans on partitions of the
other master tables.
Something like:
EXPLAIN ANALYZE
SELECT email
FROM dests JOIN (email JOIN machines ON email.guid=machines.emailRefGuid)
ON machines.qId=dests.machineRefQId
WHERE dests.destsTimestamp
BETWEEN '03-16-2007 00:00:00' AND '03-16-2007 12:00:00'
AND dests.recipient LIKE '[EMAIL PROTECTED]'
LIMIT 25;
yields
QUERY PLAN
-
Limit (cost=20007.15..201462804.94 rows=25 width=32) (actual
time=26030.622..265488.189 rows=1 loops=1)
-> Nested Loop (cost=20007.15..77226363225222.11 rows=1319836613
width=32) (actual time=26030.614..265488.174 rows=1 loops=1)
Join Filter: (("inner".guid)::text = ("outer".emailrefguid)::text)
-> Hash Join (cost=10007.15..900597480.11 rows=85800 width=145)
(actual time=17778.694..58484.220 rows=9 loops=1)
Hash Cond: (("outer".qid)::text = ("inner".machinerefqid)::text)
-> Append (cost=1.00..900583744.97 rows=2573997
width=290) (actual time=0.032..48965.685 rows=2551618 loops=1)
-> Seq Scan on machines (cost=1.00..10010.10
rows=10 width=290) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on machines_2007_03_13_00 machines
(cost=1.00..100090684.70 rows=505670 width=65) (actual
time=0.018..3229.391 rows=505669 loops=1)
-> Seq Scan on machines_2007_03_14_00 machines
(cost=1.00..100071067.42 rows=714442 width=65) (actual
time=0.027..2559.969 rows=403298 loops=1)
-> Seq Scan on machines_2007_03_15_00 machines
(cost=1.00..17727.51 rows=7651 width=290) (actual
time=0.022..558.653 rows=113585 loops=1)
-> Seq Scan on machines_2007_03_16_00 machines
(cost=1.00..100013156.26 rows=13026 width=290) (actual
time=0.023..2846.147 rows=195868 loops=1)
-> Seq Scan on machines_2007_03_09_00 machines
(cost=1.00..100069699.15 rows=239015 width=65) (actual
time=9.795..3677.025 rows=239015 loops=1)
-> Seq Scan on machines_2007_03_10_00 machines
(cost=1.00..100125947.99 rows=410299 width=65) (actual
time=8.051..7268.208 rows=410299 loops=1)
-> Seq Scan on machines_2007_03_11_00 machines
(cost=1.00..100123002.88 rows=403488 width=65) (actual
time=13.922..5966.851 rows=403488 loops=1)
-> Seq Scan on machines_2007_03_12_00 machines
(cost=1.00..100082448.96 rows=280396 width=65) (actual
time=0.410..4756.257 rows=280396 loops=1)
-> Hash (cost=7.15..7.15 rows=2 width=145) (actual
time=0.353..0.353 rows=9 loops=1)
-> Append (cost=0.00..7.15 rows=2 width=145) (actual
time=0.085..0.307 rows=9 loops=1)
-> Index Scan using dests_pkey on dests
(cost=0.00..3.13 rows=1 width=145) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (recipient =
'[EMAIL PROTECTED]'::text)
Filter: ((desttimestamp >= '2007-03-16
00:00:00'::timestamp without time zone) AND (desttimestamp <= '2007-03-16
12:00:00'::timestamp without time zone))
-> Index Scan using
dests_2007_03_16_00_time_recip_idx on dests_2007_03_16_00 dests
(cost=0.00..4.02 rows=1 width=145) (actual time=0.066..0.220 rows=9 loops=1)
Index Cond: ((recipient =
'[EMAIL PROTECTED]'::text) AND (desttimestamp >= '2007-03-16
00:00:00'::timestamp without time zone) AND (desttimestamp <= '2007-03-16
12:00:00'::timestamp without time zone))
-> Append (cost=1.00..900046361.44 rows=138 width=177)
(actual time=0.030..17641.752 rows=1452762 loops=9)
-> Seq Scan on email (cost=1.00..10010.90 rows=90
width=177) (actual time=0.004..0.004 rows=0 loops=9)
-> Seq Scan on email_2007_03_13_00 email
(cost=1.
Re: [SQL] triple self-join crawling
T E Schmitz wrote:
T E Schmitz wrote:
Things improved hugely when I changed the JOIN clauses:
see explain analyze below - can this be improved further?
LEFT OUTER JOIN history AS past_month ON (past_month.stock =
history.stockAND past_month.day >= (history.day - 30) AND
past_month.day < history.day)
LEFT OUTER JOIN history AS past_week ON (past_week.stock =
past_month.stock AND past_week.day =past_month.day AND past_week.day >=
(history.day - 7))
QUERY PLAN
GroupAggregate (cost=1370368.19..1435888.88 rows=5801 width=56) (actual
time=11945.030..13163.156 rows=5801 loops=1)
-> Sort (cost=1370368.19..1379715.86 rows=3739067 width=56) (actual
time=11944.753..12462.623 rows=120117 loops=1)
Sort Key: history.stock, history."day", history.high, history.low
-> Hash Left Join (cost=160.02..391554.63 rows=3739067
width=56) (actual time=52.746..3778.409 rows=120117 loops=1)
Hash Cond: ((("outer".stock)::text =
("inner".stock)::text) AND ("outer"."day" = "inner"."day"))
Join Filter: ("inner"."day" >= ("outer"."day" - 7))
-> Nested Loop Left Join (cost=0.00..204441.26
rows=3739067 width=57) (actual time=0.077..2313.375 rows=120117 loops=1)
Join Filter: (("inner".stock)::text =
("outer".stock)::text)
-> Seq Scan on history (cost=0.00..131.01
rows=5801 width=34) (actual time=0.016..34.845 rows=5801 loops=1)
-> Index Scan using idx_history_day on history
past_month (cost=0.00..22.32 rows=645 width=23) (actual
time=0.020..0.185 rows=21 loops=5801)
Index Cond: ((past_month."day" >=
("outer"."day" - 30)) AND (past_month."day" < "outer"."day"))
-> Hash (cost=131.01..131.01 rows=5801 width=23)
(actual time=52.608..52.608 rows=5801 loops=1)
-> Seq Scan on history past_week
(cost=0.00..131.01 rows=5801 width=23) (actual time=0.010..25.110
rows=5801 loops=1)
Total runtime: 13187.729 ms
--
Regards,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] triple self-join crawling
Andrew Sullivan wrote:
Define "crawling". Also, please post EXPLAIN and, if feasible,
Total runtime: 191430.537 ms
EXPLAIN ANALYSE output for your case.
On Sun, Mar 18, 2007 at 07:51:28PM +, T E Schmitz wrote:
The following self join of a table containing 5800 records is crawling:
SELECT
history.stock, history.day, history.high, history.low,
MAX(past_week.high) AS week_high,
MAX(past_month.high) AS month_high
FROM history
INNER JOIN history AS past_month ON (past_month.stock = history.stock
AND past_month.day < history.day AND past_month.day >= (history.day - 30))
INNER JOIN history AS past_week ON (past_week.stock =
past_month.stock AND past_week.day < history.day AND past_week.day >=
(history.day - 7))
GROUP BY history.stock, history.day, history.high, history.low
ORDER BY history.stock, history.day DESC
QUERY PLAN
GroupAggregate (cost=1372725715.71..1414901439.85 rows=5801 width=56)
(actual time=185248.481..191393.056 rows=5800 loops=1)
-> Sort (cost=1372725715.71..1378750806.73 rows=2410036407
width=56) (actual time=185247.212..187763.008 rows=584203 loops=1)
Sort Key: history.stock, history."day", history.high, history.low
-> Nested Loop (cost=0.00..132245248.41 rows=2410036407
width=56) (actual time=0.124..148136.464 rows=584203 loops=1)
Join Filter: (("inner".stock)::text = ("outer".stock)::text)
-> Nested Loop (cost=0.00..555931.84 rows=3739067
width=53) (actual time=0.087..137531.941 rows=28147 loops=1)
Join Filter: (("outer"."day" >= ("inner"."day" -
7)) AND (("outer".stock)::text = ("inner".stock)::text))
-> Index Scan using history_pkey on history
past_week (cost=0.00..266.62 rows=5801 width=23) (actual
time=0.034..43.147 rows=5801 loops=1)
-> Index Scan using idx_history_day on history
(cost=0.00..57.11 rows=1934 width=34) (actual time=0.021..12.709
rows=2900 loops=5801)
Index Cond: ("outer"."day" < history."day")
-> Index Scan using idx_history_day on history
past_month (cost=0.00..22.32 rows=645 width=23) (actual
time=0.019..0.170 rows=21 loops=28147)
Index Cond: ((past_month."day" < "outer"."day") AND
(past_month."day" >= ("outer"."day" - 30)))
Total runtime: 191430.537 ms
--
Regards,
Tarlika Elisabeth Schmitz
---(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] Encode
On Mon, 2007-03-19 at 09:21 -0300, Ezequias R. da Rocha wrote: > Hi list, > > It is possible to change the Encode of one database while it is on ? > > Sincerely > Ezequias No it isn't. You would have to drop and recreate an entire DB for that. But it's possible to change encoding of your client. It might be of help to you, depending on why you want to change encoding. SET CLIENT_ENCODING TO 'LATIN2'; where you would change latin2 with encoding you wish to use. You can find more info on this topic on http://www.postgresql.org/docs/8.2/static/multibyte.html Best regards, Jure Kodzoman Ljubljana ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [ADMIN] create view with check option
Hi everybody, I have implemented in a different way as advised in the manual. I thought this will be useful for everbody. We don't have check option in the view. Instead we can create a rule to make the view as insertable, updatable or delete. Here is the script. create table test_tbl (a int4, b int4, c varchar(30)); create or replace view test_vw as select * from test_tbl; create or replace rule test_rule_ins as on insert to test_vw do instead insert into test_tbl values (new.a, new.b, new.c); insert into test_vw (a, b) values (1,2); insert into test_vw (a, b) values (3,4); create or replace rule test_rule_upd as on update to test_vw do instead update test_tbl set a=new.a, b=new.b, c=new.c where a=new.a; update test_vw set c='good' where a=1; select * from test_vw; regards skarthi From: "Karthikeyan Sundaram" <[EMAIL PROTECTED]> To: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Subject: [ADMIN] create view with check option Date: Sun, 18 Mar 2007 22:38:05 -0700 Hi Everybody, I have 2 versions of postgres 8.1.0 is my production version and 8.2.1 is my development version. I am trying to create a view in my development version (8.2.3) create view chnl_vw as select * from channel with check option; I am getting an error message: [Error] Script lines: 1-1 -- ERROR: WITH CHECK OPTION is not implemented Line: 1 what does this mean? I looked at the 8.2.1 manual and found the create view has check option. But it says before 8.2 those options are unsupported. How can I make this command to work. Regards skarthi _ 5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free quotes - *Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910 ---(end of broadcast)--- TIP 6: explain analyze is your friend _ i'm making a difference. Make every IM count for the cause of your choice. Join Now. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=hmtagline ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] triple self-join crawling
T E Schmitz escribió:
QUERY PLAN
GroupAggregate (cost=1370368.19..1435888.88 rows=5801 width=56) (actual
time=11945.030..13163.156 rows=5801 loops=1)
-> Sort (cost=1370368.19..1379715.86 rows=3739067 width=56) (actual
time=11944.753..12462.623 rows=120117 loops=1)
Sort Key: history.stock, history."day", history.high, history.low
-> Hash Left Join (cost=160.02..391554.63 rows=3739067
width=56) (actual time=52.746..3778.409 rows=120117 loops=1)
Hash Cond: ((("outer".stock)::text =
("inner".stock)::text) AND ("outer"."day" = "inner"."day"))
Join Filter: ("inner"."day" >= ("outer"."day" - 7))
-> Nested Loop Left Join (cost=0.00..204441.26
rows=3739067 width=57) (actual time=0.077..2313.375 rows=120117 loops=1)
It's estimating >3M, but it finds 120K rows.
Join Filter: (("inner".stock)::text =
("outer".stock)::text)
-> Seq Scan on history (cost=0.00..131.01
rows=5801 width=34) (actual time=0.016..34.845 rows=5801 loops=1)
-> Index Scan using idx_history_day on history
past_month (cost=0.00..22.32 rows=645 width=23) (actual
time=0.020..0.185 rows=21 loops=5801)
Index Cond: ((past_month."day" >=
("outer"."day" - 30)) AND (past_month."day" < "outer"."day"))
-> Hash (cost=131.01..131.01 rows=5801 width=23) (actual
time=52.608..52.608 rows=5801 loops=1)
-> Seq Scan on history past_week (cost=0.00..131.01
rows=5801 width=23) (actual time=0.010..25.110 rows=5801 loops=1)
Total runtime: 13187.729 ms
Try running a vacuum analyze on the database (or at least the tables
which differ in rows estimated and actual (history for example))
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-
Martín Marqués | Programador, DBA
Centro de Telemática| Administrador
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Issue with copying data from a text file.
I have a procedure in place that copies data from a caret delimited text file into a table storing some information. One of the fields in the table contains an item description which may contain item dimensions such as - 17" alloy wheels The problem I am getting when I do my load is I believe due to the presence of the double quotation marks giving the copy the impression that it is to include the information following as a single text string until it gets to the next set of double quotes. As a result, I get the following: AutoDRS=# COPY deal_lines_temp_load FROM 'c:/temp/autodrs_deal_lines.txt' WITH DELIMITER AS '^' CSV HEADER; ERROR: value too long for type character varying(30) CONTEXT: COPY deal_lines_temp_load, line 87, column order_desc: "17 5 spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^ The column as you can see is defined as a 30 character field, the load contains in this column ^17" 5 spoke alloy wheels.^ I note an option in the COPY command to specify the quote character, defaulting to double quote. The problem being a single quote will also be used in the data, as will other characters. Is there any way to get a copy to have no quote character? I.e. read the file and put whatever is between the caret characters straight into the appropriate field exactly as is. TIA, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Issue with copying data from a text file.
Can you not export the source file with escape characters? ie, ^17\" Alloy Wheels^ ~p On Tue, 2007-03-20 at 11:25 +0900, Paul Lambert wrote: > I have a procedure in place that copies data from a caret delimited text > file into a table storing some information. > > One of the fields in the table contains an item description which may > contain item dimensions such as - 17" alloy wheels > > The problem I am getting when I do my load is I believe due to the > presence of the double quotation marks giving the copy the impression > that it is to include the information following as a single text string > until it gets to the next set of double quotes. As a result, I get the > following: > > AutoDRS=# COPY deal_lines_temp_load FROM > 'c:/temp/autodrs_deal_lines.txt' > WITH DELIMITER AS '^' CSV HEADER; > ERROR: value too long for type character varying(30) > CONTEXT: COPY deal_lines_temp_load, line 87, column order_desc: "17 5 > spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^ > > The column as you can see is defined as a 30 character field, the load > contains in this column ^17" 5 spoke alloy wheels.^ > > I note an option in the COPY command to specify the quote character, > defaulting to double quote. The problem being a single quote will also > be used in the data, as will other characters. Is there any way to get a > copy to have no quote character? I.e. read the file and put whatever is > between the caret characters straight into the appropriate field exactly > as is. > > TIA, > Paul. > Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, Vic, 3131 AUSTRALIA P. +613 9845 0600 F. +613 9845 0655 E. [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Issue with copying data from a text file.
Phillip Smith wrote: Can you not export the source file with escape characters? ie, ^17\" Alloy Wheels^ ~p The source file comes from extracts on our main application which sits inside an in-house pretending-to-be-a-dbms file system. The content of these extracts would be difficult to change - the extract program would need to parse the data looking for quotes and preceed them with the necessary escape character. Not being a proper database dump it's not a simple matter of flicking a switch to get it to include the escape character. The way the extracts are written would require a few dozen lines of code to each extract, and theres about 40ish extracts. Plus I don't maintain that side of our code, and those that do can be a bit lazy and I'd likely be waiting months to get it done - if they even decide to do it. -- Paul Lambert Database Administrator AutoLedgers ---(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] Issue with copying data from a text file.
On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote: The source file comes from extracts on our main application which sits inside an in-house pretending-to-be-a-dbms file system. The content of these extracts would be difficult to change - the extract program would need to parse the data looking for quotes and preceed them with the necessary escape character. Not being a proper database dump it's not a simple matter of flicking a switch to get it to include the escape character. The way the extracts are written would require a few dozen lines of code to each extract, and theres about 40ish extracts. Plus I don't maintain that side of our code, and those that do can be a bit lazy and I'd likely be waiting months to get it done - if they even decide to do it. Pipe it through sed and replace the Carets with TABS? sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > c:/temp/autodrs_deal_lines.tab Then use copy like so: \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null '' Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Issue with copying data from a text file.
Andrej Ricnik-Bay wrote: On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote: The source file comes from extracts on our main application which sits inside an in-house pretending-to-be-a-dbms file system. The content of these extracts would be difficult to change - the extract program would need to parse the data looking for quotes and preceed them with the necessary escape character. Not being a proper database dump it's not a simple matter of flicking a switch to get it to include the escape character. The way the extracts are written would require a few dozen lines of code to each extract, and theres about 40ish extracts. Plus I don't maintain that side of our code, and those that do can be a bit lazy and I'd likely be waiting months to get it done - if they even decide to do it. Pipe it through sed and replace the Carets with TABS? sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > c:/temp/autodrs_deal_lines.tab Then use copy like so: \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null '' Cheers, Andrej The data contains tabs... don't ask why... I don't have a clue :P I'll do something along the lines of sed... but not with sed, I'll use the command line interpreter on the OpenVMS systems where the extracts run. I just thought there might have been a quicker way to switch it off in the copy command, i.e. specifying "quote none" as one of the parameters to the command. I guess not... Thanks for the pointers. P. -- Paul Lambert Database Administrator AutoLedgers ---(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] Issue with copying data from a text file.
Maybe use char 254 or 253 or something similar... Anything that isn't going to be found in the file. Have you tried using a string as a delimiter? QUOTE 'THIS.STRING.ISNT.IN.THE.TEXT.FILE' ~p On Tue, 2007-03-20 at 11:59 +0900, Paul Lambert wrote: > Andrej Ricnik-Bay wrote: > > On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote: > > > >> The source file comes from extracts on our main application which sits > >> inside an in-house pretending-to-be-a-dbms file system. The content of > >> these extracts would be difficult to change - the extract program would > >> need to parse the data looking for quotes and preceed them with the > >> necessary escape character. > >> > >> Not being a proper database dump it's not a simple matter of flicking a > >> switch to get it to include the escape character. The way the extracts > >> are written would require a few dozen lines of code to each extract, and > >> theres about 40ish extracts. > >> > >> Plus I don't maintain that side of our code, and those that do can be a > >> bit lazy and I'd likely be waiting months to get it done - if they even > >> decide to do it. > > Pipe it through sed and replace the Carets with TABS? > > sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > > > c:/temp/autodrs_deal_lines.tab > > > > Then use copy like so: > > \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null '' > > > > > > Cheers, > > Andrej > > > > > > The data contains tabs... don't ask why... I don't have a clue :P > > I'll do something along the lines of sed... but not with sed, I'll use > the command line interpreter on the OpenVMS systems where the extracts > run. I just thought there might have been a quicker way to switch it off > in the copy command, i.e. specifying "quote none" as one of the > parameters to the command. I guess not... > > Thanks for the pointers. > > P. > Phillip Smith IT Coordinator Weatherbeeta P/L 8 Moncrief Rd Nunawading, Vic, 3131 AUSTRALIA P. +613 9845 0600 F. +613 9845 0655 E. [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Issue with copying data from a text file.
On Tue, Mar 20, 2007 at 11:25:38AM +0900, Paul Lambert wrote: > I note an option in the COPY command to specify the quote character, > defaulting to double quote. The problem being a single quote will also > be used in the data, as will other characters. Is there any way to get a > copy to have no quote character? Are there any control characters that won't appear in the data? -- Michael Fuhr ---(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] Issue with copying data from a text file.
Phillip Smith wrote: Maybe use char 254 or 253 or something similar... Anything that isn't going to be found in the file. Have you tried using a string as a delimiter? QUOTE 'THIS.STRING.ISNT.IN.THE.TEXT.FILE' ~p I changed it to QUOTE '\f' to set the quote character to form-feed which is not going to appear in the file and that appears to do the trick without any changes to the source file. Thanks, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to declare cursor if tablename is a variable?
[Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Tue, Mar 20, 2007 at 02:28:15AM +, hu js wrote: > It doesn't work. Because can't use variable for table name in query. > > Please tell me another way. Thanks What exactly have you tried that doesn't work? Will neither of the following do what you want? What version of PostgreSQL are you using? DECLARE rec_objrecord; query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename); BEGIN FOR rec_obj IN EXECUTE query_obj LOOP [...] END LOOP; or DECLARE cur_objrefcursor; query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename); BEGIN OPEN cur_obj FOR EXECUTE query_obj; [...] -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
