[SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
Hello all, I a m a new member of the list and at the moment , I am studiing informatica: sql. At the workplace, we use microsoft sql server 2000. At home, I use postgresql 7.2.1 and now I would import the data of the database at the workplace into the postgresql environment at home. I have paste a little part of the sql-code to create a table in a database called "tennisclub". To execute the code bellow with pgsql, I do the following steps: pgsql tennisclub \e file_with_sql_code.sql The contens of the file file_with_sql_code.sql is as follows: CREATE TABLE SPELERS (SPELERSNR SMALLINT NOT NULL, NAAM CHAR(15) NOT NULL, VOORLETTERS CHAR(3) NOT NULL, VOORVOEGSELS CHAR(7) , GEB_DATUM datetime , GESLACHT CHAR(1) NOT NULL, JAARTOE SMALLINT NOT NULL, STRAAT CHAR(15) NOT NULL, HUISNR CHAR(4) , POSTCODE CHAR(6) , PLAATS CHAR(10) NOT NULL, TELEFOON CHAR(10) , BONDSNR CHAR(4) , PRIMARY KEY (SPELERSNR) ); INSERT INTO SPELERS VALUES ( 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', '80', '1234KK', 'Den Haag', '070-476537', '8467' ); INSERT INTO SPELERS VALUES ( 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', '23', 'LJ', 'Rijswijk', '070-368753', '1124' ); INSERT INTO SPELERS VALUES ( 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', '16a', '1812UP', 'Den Haag', '070-353548', '1608' ); INSERT INTO SPELERS VALUES ( 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', '43', '3575NH', 'Den Haag', '070-237893', '2411' ); INSERT INTO SPELERS VALUES ( 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', '804', '8457DK', 'Zoetermeer', '079-234857', '2513' ); INSERT INTO SPELERS VALUES ( 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', '65', '9437AO', 'Zoetermeer', '079-987571', '7060' ); INSERT INTO SPELERS VALUES ( 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', '39', '9758VB', 'Den Haag', '070-347689', NULL ); INSERT INTO SPELERS VALUES ( 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', '16', '4377CB', 'Den Haag', '070-473458', '6409' ); INSERT INTO SPELERS VALUES ( 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', '78', '9629CD', 'Den Haag', '070-393435', NULL ); INSERT INTO SPELERS VALUES ( 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', '8', '6392LK', 'Rotterdam', '010-548745', '1319' ); INSERT INTO SPELERS VALUES ( 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', '4', '6584WO', 'Rijswijk', '070-458458', '2983' ); INSERT INTO SPELERS VALUES ( 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', '80', '6494SG', 'Den Haag', '070-494593', '6524' ); INSERT INTO SPELERS VALUES ( 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', '10', '1294QK', 'Leiden', '010-659599', NULL ); INSERT INTO SPELERS VALUES ( 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', '33a', '5746OP', 'Voorburg', '070-867564', NULL ); This code doesn't work. Can someone tell me how I can adjust the syntax of the code and in global: how can I convert sql-code , for microsoft sql server 2000, to sql-code for postgresql? Thanks in advance best regards William Windels ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
> Can someone tell me how I can adjust the syntax of the code and in global: > how can I convert sql-code , for microsoft sql server 2000, to sql-code for > postgresql? > Try, http://techdocs.postgresql.org/techdocs/sqlserver2pgsql.php regards, bhuvaneswaran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] subselect return in where clause
Group, Is it possible to use a subselect result in a where statement ? If so, how ? Fx. select some_stuff, more_stuff, (select other_stuff from other_table where other_table.stuff ilike stuff.match) as other_stuff from stuff where other_stuff = 'hello' thanks. -- Best Regards, Michael Landin Hostbaek FreeBSDCluster.org - an International Community */ PGP-key available upon request /* ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
On Tuesday 28 January 2003 10:08 am, william windels wrote: A lot of SQL! Instead of spending a lot of time trying to figure out what's wrong with your code, here's a couple of links that should help you: The most obvious is the PostgreSQL documentation which can be found in the doc directory of your PostgreSQL installation (usually /usr/local/pgsql/doc if you're using Linux), or on the Internet at: http://www.postgresql.org/ Then there's a book online - Practical PostgreSQL: http://www.commandprompt.com/ppbook/book1.htm Geraint. ---(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] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
Only 1 small change makes it acceptable to pgsql. change datetime to timestamp . regds mallah. On Tuesday 28 January 2003 03:38 pm, william windels wrote: > Hello all, > > I a m a new member of the list and at the moment , I am studiing > informatica: sql. > > At the workplace, we use microsoft sql server 2000. > At home, I use postgresql 7.2.1 and now I would import the data of the > database at the workplace into the postgresql environment at home. > > I have paste a little part of the sql-code to create a table in a database > called "tennisclub". > > To execute the code bellow with pgsql, I do the following steps: > > pgsql tennisclub > \e file_with_sql_code.sql > > The contens of the file file_with_sql_code.sql is as follows: > > CREATE TABLE SPELERS > > (SPELERSNR SMALLINT NOT NULL, > > NAAM CHAR(15) NOT NULL, > > VOORLETTERS CHAR(3) NOT NULL, > > VOORVOEGSELS CHAR(7) , > > GEB_DATUM datetime , > > GESLACHT CHAR(1) NOT NULL, > > JAARTOE SMALLINT NOT NULL, > > STRAAT CHAR(15) NOT NULL, > > HUISNR CHAR(4) , > > POSTCODE CHAR(6) , > > PLAATS CHAR(10) NOT NULL, > > TELEFOON CHAR(10) , > > BONDSNR CHAR(4) , > > PRIMARY KEY (SPELERSNR) ); > > INSERT INTO SPELERS VALUES ( > > 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', > > '80', '1234KK', 'Den Haag', '070-476537', '8467' > > ); > > INSERT INTO SPELERS VALUES ( > > 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', > > '23', 'LJ', 'Rijswijk', '070-368753', '1124' > > ); > > INSERT INTO SPELERS VALUES ( > > 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', > > '16a', '1812UP', 'Den Haag', '070-353548', '1608' > > ); > > INSERT INTO SPELERS VALUES ( > > 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', > > '43', '3575NH', 'Den Haag', '070-237893', '2411' > > ); > > INSERT INTO SPELERS VALUES ( > > 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', > > '804', '8457DK', 'Zoetermeer', '079-234857', '2513' > > ); > > INSERT INTO SPELERS VALUES ( > > 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', > > '65', '9437AO', 'Zoetermeer', '079-987571', '7060' > > ); > > INSERT INTO SPELERS VALUES ( > > 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', > > '39', '9758VB', 'Den Haag', '070-347689', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', > > '16', '4377CB', 'Den Haag', '070-473458', '6409' > > ); > > INSERT INTO SPELERS VALUES ( > > 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', > > '78', '9629CD', 'Den Haag', '070-393435', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', > > '8', '6392LK', 'Rotterdam', '010-548745', '1319' > > ); > > INSERT INTO SPELERS VALUES ( > > 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', > > '4', '6584WO', 'Rijswijk', '070-458458', '2983' > > ); > > INSERT INTO SPELERS VALUES ( > > 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', > > '80', '6494SG', 'Den Haag', '070-494593', '6524' > > ); > > INSERT INTO SPELERS VALUES ( > > 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', > > '10', '1294QK', 'Leiden', '010-659599', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', > > '33a', '5746OP', 'Voorburg', '070-867564', NULL > > ); > > > > This code doesn't work. > > > > Can someone tell me how I can adjust the syntax of the code and in global: > how can I convert sql-code , for microsoft sql server 2000, to sql-code for > postgresql? > > > > Thanks in advance > > > > best regards > > William Windels > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
[SQL] import error
Hi all! I'm trying to import a text file from Suunto Dive Manager. I've got following table structure: CREATE TABLE dives ( id int, dive_number int, dive_date date, time_of_day time, series int, dc_dive_number int, dive_time int, surface_interval int, max_depth float, mean_depth float, dc_type int, dc_serial_number int, dc_personal_data text, dc_sample_rate int, dc_altitude_mode int, dc_personal_mode int, solution_time_adj int, modified int, location text, site text, weather text, water_visibility text, air_temp int, water_temp int, water_temp_at_end int, partner text, dive_master text, boat_name text, cylinder_desc text, cylinder_size float, cylinder_units_code int, cylinder_work_pressure float, cylinder_start_pressure float, cylinder_end_pressure int, sac_rate float, sac_units int, user_field_1 text, user_field_2 text, user_field_3 text, user_field_4 text, user_field_5 text, weight int, oxygen_percent int, olf_percent int, otu_flag int ); Data is structured as follows: 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche Bucht","wolkenlos",">20m",35,0,30,"","Mehmet Semerkant","","",0.0,1,0.,0.,0,0.,0,"","","","","",12,0,0,0 My import gives following error: sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; ": can't parse "ne 1, pg_atoi: error in "0 My first thought was that the date and time is wrong (because it's the only "0), but even after removing it for a test results in the same error. How can I find out, which field exactly causes this error? My PostgreSQL version is (on Debian/Woody): sport=# select version(); version --- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) Regards, Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] import error
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > Semerkant","","",0.0,1,0.,0.,0,0.,0,"","","","","",12,0,0,0 Why dont you get rid of "'s ? e.g. 1,1,2001-08-07,11:35,1,1,...etc.. (Also see if there exists a DateStyle like yours 07.08.2001 in order to avoid the date format conversion) > > My import gives following error: > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > ": can't parse "ne 1, pg_atoi: error in "0 > > My first thought was that the date and time is wrong (because it's the > only "0), but even after removing it for a test results in the same > error. How can I find out, which field exactly causes this error? > > My PostgreSQL version is (on Debian/Woody): > > sport=# select version(); >version > --- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > (1 row) > > Regards, > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] import error
On Tue, 28 Jan 2003, Achilleus Mantzios wrote: > On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > > > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > > Semerkant","","",0.0,1,0.,0.,0,0.,0,"","","","","",12,0,0,0 > > Why dont you get rid of "'s ? > e.g. > 1,1,2001-08-07,11:35,1,1,...etc.. > (Also see if there exists a DateStyle like yours 07.08.2001 > in order to avoid the date format conversion) I think German DateStyle will do the trick. > > > > > My import gives following error: > > > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > > ": can't parse "ne 1, pg_atoi: error in "0 > > > > My first thought was that the date and time is wrong (because it's the > > only "0), but even after removing it for a test results in the same > > error. How can I find out, which field exactly causes this error? > > > > My PostgreSQL version is (on Debian/Woody): > > > > sport=# select version(); > >version > > --- > > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > > (1 row) > > > > Regards, > > Oliver > > > > -- > > VECERNIK Datenerfassungssysteme > > A-2560 Hernstein, Hofkogelgasse 17 > > Tel.: +43 2633 47530, Fax: DW 50 > > http://members.aon.at/vecernik > > > > > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > == > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: CR/LF conversion (was: [SQL] import error)
Oliver Vecernik schrieb: Hi all! I'm trying to import a text file from Suunto Dive Manager. I've got following table structure: Arghh ... It's always the same problem with CR/LF conversions ... After changing it to just LFs, everthing worked like a charm. Is there an elegant way to handle this automatically? Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: CR/LF conversion (was: [SQL] import error)
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > Oliver Vecernik schrieb: > > > Hi all! > > > > I'm trying to import a text file from Suunto Dive Manager. I've got > > following table structure: > > Arghh ... > > It's always the same problem with CR/LF conversions ... > > After changing it to just LFs, everthing worked like a charm. Is there > an elegant way to handle this automatically? If your dates were german in the first place, keep them german during the copy, otherwise you will have wrong dates. > > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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
[SQL] CSV import
Hi again! After investigating a little bit further my CSV import couldn't work because of following reasons: 1. CSV files are delimited with CR/LF 2. text fields are surrounded by double quotes Is there a direct way to import such files into PostgreSQL? I would like to have something like MySQL provides: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)] Has anybody written such a function already? Regards, Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CSV import
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > Hi again! > > After investigating a little bit further my CSV import couldn't work > because of following reasons: > > 1. CSV files are delimited with CR/LF See below > 2. text fields are surrounded by double quotes in vi :1,$ s/"//g > > Is there a direct way to import such files into PostgreSQL? > > I would like to have something like MySQL provides: > > LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' > [REPLACE | IGNORE] > INTO TABLE tbl_name > [FIELDS > [TERMINATED BY '\t'] > [[OPTIONALLY] ENCLOSED BY ''] > [ESCAPED BY '\\' ] > ] > [LINES TERMINATED BY '\n'] make it [LINES TERMINATED BY '\r\n'] > [IGNORE number LINES] > [(col_name,...)] > > Has anybody written such a function already? > > Regards, > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] import error
Oliver Vecernik <[EMAIL PROTECTED]> writes: > My import gives following error: > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > ": can't parse "ne 1, pg_atoi: error in "0 The weird formatting of the error message suggests that the problem is DOS-style newlines (CR/LF). COPY only likes Unix-style newlines (LF). It thinks the CRs are part of the data, and then the datatype input routines (like pg_atoi) spit up. Since pg_atoi spits back the text it couldn't parse (including the CR), you get this bizarre-looking overprinted message. Solution: save the import file with Unix newlines, or run it through a newline-fixing filter. regards, tom lane ---(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: CR/LF conversion (was: [SQL] import error)
Oliver Vecernik <[EMAIL PROTECTED]> writes: > It's always the same problem with CR/LF conversions ... Ah, you figured it out. (Didn't see your followup right away because of the changed subject line.) > After changing it to just LFs, everthing worked like a charm. Is there > an elegant way to handle this automatically? There are plans to make COPY treat all common styles of newline alike. We had to wait a release or two after taking the first step, though, since it's an incompatible change. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] subselect return in where clause
Michael Hostbaek <[EMAIL PROTECTED]> writes: > select some_stuff, more_stuff, > (select other_stuff from other_table where other_table.stuff ilike > stuff.match) as other_stuff from stuff where other_stuff = 'hello' The above is nonsensical: WHERE cannot refer to values computed in the SELECT list, because WHERE logically occurs before the SELECT list is executed (indeed, the SELECT list will not be executed at all, if WHERE returns false). You could repeat yourself: select some_stuff, more_stuff, (select other_stuff from other_table where other_table.stuff ilike stuff.match) as other_stuff from stuff where (select other_stuff from other_table where other_table.stuff ilike stuff.match) = 'hello' or if it really bugs you to write the expression twice, perhaps use a sub-select: select * from (select some_stuff, more_stuff, (select other_stuff from other_table where other_table.stuff ilike stuff.match) as other_stuff from stuff) as subselect where other_stuff = 'hello' although you shouldn't have any illusions about this being more efficient than writing the expression twice. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Cross-database references
Title: AIP - Assessoria Informática e Proteção LTDA Someone knows how is going the implementation of cross database references in pgsql ? Would have some future release with this great functionality ? Thanks, Pedro Igor
[SQL] Problem with query
Hi, I don't know what happend when I execute this query? select * from llamada where fecha='20030127' and tipo=1 and tiempo>0 and gwdes like '64.7.127.14%' order by hora desc; It's very slow, it takes approximately 6 minutes to show me the results. I saw that it's happend when I execute this types of queries "Select...gwdes like '64.7.127.14%'.." or with field gworg. But when i use others fields the time that it take is faster. I used PostgreSQL 7.0.3 and I have only problem with this type query. I have approximately 1904796 registers in this table "llamada". I did (VACUUM VERBOSE ANALYZE llamada) and (REINDEX INDEX gwdes_lla,gworg_lla) and the query follows slow. this is the struct of my Table "llamada" Attribute |Type | Modifier ---+-+-- callid| varchar(40) | not null fecha | date| hora | time| ani | varchar(25) | dnis | varchar(25) | tiempo| float8 | tipo | smallint| gworg | varchar(16) | gwdes | varchar(16) | Indices: ani_lla,callid_lla,dnis_lla,fecha_lla,gwdes_lla,gworg_lla,hora_lla, llamada_pkey,tiempo_lla,tipo_lla This is the result of EXPLAIN: hb=# explain select * from llamada where fecha='20030127' and tipo=1 and tiempo>0 and gwdes like '64.7.127.14%' order by hora desc; NOTICE: QUERY PLAN: Sort (cost=4.98..4.98 rows=2 width=82) -> Index Scan using gwdes_lla on llamada (cost=0.00..4.97 rows=2 width=82) And this is the EXPLAIN of other query: hb=# explain select * from llamada where fecha='20030127' and tipo=1 and tiempo>0 and dnis like '54%' order by hora desc; NOTICE: QUERY PLAN: Sort (cost=21362.07..21362.07 rows=1 width=82) -> Index Scan using fecha_lla on llamada (cost=0.00..21362.06 rows=1 width=82) And this is the result for VACUUM, for my table llamada: hb=# VACUUM VERBOSE ANALYZE llamada; NOTICE: --Relation llamada-- NOTICE: Pages 39167: Changed 0, reaped 113, Empty 0, New 0; Tup 1904796: Vac 12, Keep/VTL 0/0, Crash 2, UnUsed 102, MinLen 143, MaxLen 176; Re-using: Free/Avail. Space 16544/11084; EndEmpty/Avail. Pages 0/68. CPU 40.93s/23.27u sec. NOTICE: Index callid_lla: Pages 18051; Tuples 1904796: Deleted 12. CPU 18.00s/1.77u sec. NOTICE: Index gwdes_lla: Pages 7095; Tuples 1904796: Deleted 0. CPU 10.69s/1.86u sec. NOTICE: Index gworg_lla: Pages 6696; Tuples 1904796: Deleted 0. CPU 12.69s/1.69u sec. NOTICE: Index tipo_lla: Pages 3756; Tuples 1904796: Deleted 11. CPU 4.44s/1.80u sec. NOTICE: Index tiempo_lla: Pages 5016; Tuples 1904796: Deleted 10. CPU 5.96s/1.59u sec. NOTICE: Index dnis_lla: Pages 10013; Tuples 1904796: Deleted 10. CPU 2.95s/2.00u sec. NOTICE: Index ani_lla: Pages 7895; Tuples 1904796: Deleted 10. CPU 4.64s/1.86u sec. NOTICE: Index hora_lla: Pages 5731; Tuples 1904796: Deleted 10. CPU 1.48s/1.76u sec. NOTICE: Index fecha_lla: Pages 3831; Tuples 1904796: Deleted 10. CPU 4.04s/1.72u sec. NOTICE: Index llamada_pkey: Pages 23446; Tuples 1904796: Deleted 10. CPU 8.25s/2.13u sec. NOTICE: Rel llamada: Pages: 39167 --> 39167; Tuple(s) moved: 11. CPU 12.46s/0.36u sec. NOTICE: Index callid_lla: Pages 18051; Tuples 1904796: Deleted 11. CPU 16.85s/1.31u sec. NOTICE: Index gwdes_lla: Pages 7095; Tuples 1904796: Deleted 11. CPU 11.47s/1.26u sec. NOTICE: Index gworg_lla: Pages 6696; Tuples 1904796: Deleted 11. CPU 4.37s/1.22u sec. NOTICE: Index tipo_lla: Pages 3756; Tuples 1904796: Deleted 11. CPU 2.03s/1.26u sec. NOTICE: Index tiempo_lla: Pages 5016; Tuples 1904796: Deleted 11. CPU 6.60s/1.25u sec. NOTICE: Index dnis_lla: Pages 10013; Tuples 1904796: Deleted 11. CPU 3.00s/1.15u sec. NOTICE: Index ani_lla: Pages 7895; Tuples 1904796: Deleted 11. CPU 4.70s/1.33u sec. NOTICE: Index hora_lla: Pages 5731; Tuples 1904796: Deleted 11. CPU 1.71s/1.45u sec. NOTICE: Index fecha_lla: Pages 3831; Tuples 1904796: Deleted 11. CPU 4.69s/1.24u sec. NOTICE: Index llamada_pkey: Pages 23446; Tuples 1904796: Deleted 11. CPU 8.06s/1.32u sec. If someone can help with this query I will thankfull, Regards Ricardo _ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CSV import
Hi
You will need two text utilities {dos2unix and sed} to do this in the simplest
way. They are fairly standard text utilities and are probably already on your
machine.
This is how I would do it :
sed "s/\"//g" file_name.txt \
| dos2unix \
| pgsql -c "COPY table_name FROM STDIN USING DELIMITERS ',';" db
Where "file_name.txt" is the csv file you want to import and "table_name" is
the previously created table you want to insert the data into and db is the
database name.
How this works is "sed" {stream editor} removes all the double quote
characters '"' then pipes the output through "dos2unix" which converts all the
CRLF {DOS EOL} sequences into CR {UNIX EOL} characters, then pipes the data to
"pgsql" with a command that does a bulk insert into the table of the database
you have selected.
Guy
Oliver Vecernik wrote:
Hi again!
After investigating a little bit further my CSV import couldn't work
because of following reasons:
1. CSV files are delimited with CR/LF
2. text fields are surrounded by double quotes
Is there a direct way to import such files into PostgreSQL?
I would like to have something like MySQL provides:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
Has anybody written such a function already?
Regards,
Oliver
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
Hi You should use "date" instead of "datetime" since the data is only a date and a "date" type uses less storage than a "timestamp". For time only data use type "time" or "timetz" if you want the timezone as well. For data with a date and time use "timestamp" or "timestamptz" if you want to include the timezone as well. I hope this is helpful. There are other time based data types as well but you should read the documentation to determine when it is best to use them. One thing I really like about PostgreSQL is the variety of data types and functions for special operations on them. ## I believe the information below is correct. If you go through the documentation you can also find out how to CREATE your own data TYPE to allow more direct use of non-PostgreSQL data types. Here is an example that will allow input of any "datetime" data into PostgreSQL : CREATE TYPE datetime AS (datetime timestamptz); Then any time,date or date and time data can be input as type datetime. Without having to convert your tables to rename the "datetime" type. Guy Rajesh Kumar Mallah. wrote: Only 1 small change makes it acceptable to pgsql. change datetime to timestamp . regds mallah. On Tuesday 28 January 2003 03:38 pm, william windels wrote: Hello all, I a m a new member of the list and at the moment , I am studiing informatica: sql. At the workplace, we use microsoft sql server 2000. At home, I use postgresql 7.2.1 and now I would import the data of the database at the workplace into the postgresql environment at home. I have paste a little part of the sql-code to create a table in a database called "tennisclub". To execute the code bellow with pgsql, I do the following steps: pgsql tennisclub \e file_with_sql_code.sql The contens of the file file_with_sql_code.sql is as follows: CREATE TABLE SPELERS (SPELERSNR SMALLINT NOT NULL, NAAM CHAR(15) NOT NULL, VOORLETTERS CHAR(3) NOT NULL, VOORVOEGSELS CHAR(7) , GEB_DATUM datetime , GESLACHT CHAR(1) NOT NULL, JAARTOE SMALLINT NOT NULL, STRAAT CHAR(15) NOT NULL, HUISNR CHAR(4) , POSTCODE CHAR(6) , PLAATS CHAR(10) NOT NULL, TELEFOON CHAR(10) , BONDSNR CHAR(4) , PRIMARY KEY (SPELERSNR) ); INSERT INTO SPELERS VALUES ( 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', '80', '1234KK', 'Den Haag', '070-476537', '8467' ); INSERT INTO SPELERS VALUES ( 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', '23', 'LJ', 'Rijswijk', '070-368753', '1124' ); INSERT INTO SPELERS VALUES ( 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', '16a', '1812UP', 'Den Haag', '070-353548', '1608' ); INSERT INTO SPELERS VALUES ( 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', '43', '3575NH', 'Den Haag', '070-237893', '2411' ); INSERT INTO SPELERS VALUES ( 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', '804', '8457DK', 'Zoetermeer', '079-234857', '2513' ); INSERT INTO SPELERS VALUES ( 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', '65', '9437AO', 'Zoetermeer', '079-987571', '7060' ); INSERT INTO SPELERS VALUES ( 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', '39', '9758VB', 'Den Haag', '070-347689', NULL ); INSERT INTO SPELERS VALUES ( 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', '16', '4377CB', 'Den Haag', '070-473458', '6409' ); INSERT INTO SPELERS VALUES ( 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', '78', '9629CD', 'Den Haag', '070-393435', NULL ); INSERT INTO SPELERS VALUES ( 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', '8', '6392LK', 'Rotterdam', '010-548745', '1319' ); INSERT INTO SPELERS VALUES ( 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', '4', '6584WO', 'Rijswijk', '070-458458', '2983' ); INSERT INTO SPELERS VALUES ( 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', '80', '6494SG', 'Den Haag', '070-494593', '6524' ); INSERT INTO SPELERS VALUES ( 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', '10', '1294QK', 'Leiden', '010-659599', NULL ); INSERT INTO SPELERS VALUES ( 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', '33a', '5746OP', 'Voorburg', '070-867564', NULL ); This code doesn't work. Can someone tell me how I can adjust the syntax of the code and in global: how can I convert sql-code , for microsoft sql server 2000, to sql-code for postgresql? Thanks in advance best regards William Windels ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CSV import
--- Oliver Vecernik <[EMAIL PROTECTED]> wrote: > Is there a direct way to import such files into > PostgreSQL? > As I believe others have replied: no, not yet. If you are absolutely sure that your data will _never_ contain commas, then the simple solution of just deleting all of the quotes , then using COPY with comma delimiters, will work. Otherwise, parsing CSV files gets just too complicated, and you are better off using an existing solution (like a Perl module) to preprocess your data. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
--- william windels <[EMAIL PROTECTED]> wrote: > how can I convert sql-code , for microsoft sql > server 2000, to sql-code for > postgresql? > In addition to the suggestions given in other replies, have a look at PGAdminII: http://pgadmin.postgresql.org That is an excellent utility, and comes with a database migration utility (separate download) that will migrate MS SQL Server databases. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(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] Cross-database references
On Tue, Jan 28, 2003 at 05:30:06PM -0200, Pedro Igor Craveiro e Silva wrote: > AIP - Assessoria Informática e Proteção LTDASomeone knows how is going the >implementation of cross database references in pgsql ? > Would have some future release with this great functionality ? This is usually left up to the front-end. Usually for me, perl DBI. This way, I can connect to multiple databases, possibly all of different types (PgSQL, Oracle, Informix, ...). I'm sure the SQL gurus can corret me, but I was under the impression that cross-database references in the backend would be capital-D Difficult to implement with transaction support. Then there's the further tearing asunder of the ANSI SQL standard... Jason -- Indigo Industrial Controls Ltd. 64-21-343-545 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] LONG - Question on dealing w/ numerics
I have a function that is to create a Accounting JOURNAL entry. The strange thing is the function works for simple entries such as: Cash - Debit 100 A/R - Credit 100 But when I try to trick it or break it for testing purposes (IT DOES BREAK WHEN IT SHOULDN'T) on a entry like this: Cash - Debit 100 A/R - Credit 100 Cash - Credit 100 A/R - Debit 100 (Which should have a net affect of 0 on both accounts) But here is the resulting balance on accounts, Cash Debit Balance 200 A/R Credit Balance 200 Here is the function and I can't seem to figure out what is LOGICALLY wrong and would produce these results. create function create_journal_entry_line(integer,integer,integer,numeric(20,2)) returns INTEGER as ' DECLARE eid ALIAS FOR $1; aid ALIAS FOR $2; ltype ALIAS FOR $3; amount ALIAS FOR $4; new_balance NUMERIC(20,2); account_type RECORD; account RECORD; line RECORD; BEGIN select into account * from accounts where account_id = aid; IF NOT FOUND THEN return -1; END IF; IF account.account_active = ''f'' THEN return -1; END IF; insert into journal_lines (entry_id,account_id,line_type,line_amount) values (eid,aid,ltype,amount); select into line * from journal_lines where entry_id = eid AND account_id = aid AND ltype = ltype; IF NOT FOUND THEN return -1; END IF; select into account_type * from account_types where account_type_id = account.account_type; IF account_type.positive_account_balance_type = line.line_type THEN new_balance := account.account_balance + amount; ELSE new_balance := account.account_balance - amount; END IF; UPDATE accounts SET account_balance = new_balance WHERE account_id = account.account_id; return line.entry_id; END;' language 'plpgsql'; P.S. Line type represents 1 = Debit, 2 = Credit. The positive_account_balance_type tells eithier if the account should have a DEBIT or CREDIT balance (Represented the same as line type) ---(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] Cross-database references
On Tue, Jan 28, 2003 at 05:30:06PM -0200, Pedro Igor Craveiro e Silva wrote: > AIP - Assessoria Informática e Proteção LTDASomeone knows how is going the >implementation of cross database references in pgsql ? > Would have some future release with this great functionality ? Olá Pedro, Look at the dblink module under the contrib section of PostgreSQL. It implements the feature you're looking for. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "There's 2 kind of people: those who knows assembler and the rest" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
Guy Fraser <[EMAIL PROTECTED]> writes: > If you go through the documentation you can also find out how to CREATE your > own data TYPE to allow more direct use of non-PostgreSQL data types. Here is > an example that will allow input of any "datetime" data into PostgreSQL : > CREATE TYPE datetime AS (datetime timestamptz); I think what you probably really want is CREATE DOMAIN datetime AS timestamptz; or more SQL-spec-compliantly CREATE DOMAIN datetime AS timestamp with time zone; which essentially makes datetime a direct alias for timestamptz. The CREATE TYPE approach makes a rowtype containing one timestamptz column, which isn't really going to act the way you want --- for one thing, none of the predefined functions and operators for type timestamptz will accept it. With the DOMAIN approach, they will. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CSV import
You can acheive the same result with:
tr -d '"\015' < file_name.txt | psql {etc...}
Unix EOL is LF not CR.
Guy Fraser wrote:
>
> Hi
>
> You will need two text utilities {dos2unix and sed} to do this in the simplest
> way. They are fairly standard text utilities and are probably already on your
> machine.
>
> This is how I would do it :
>
> sed "s/\"//g" file_name.txt \
> | dos2unix \
> | pgsql -c "COPY table_name FROM STDIN USING DELIMITERS ',';" db
>
> Where "file_name.txt" is the csv file you want to import and "table_name" is
> the previously created table you want to insert the data into and db is the
> database name.
>
> How this works is "sed" {stream editor} removes all the double quote
> characters '"' then pipes the output through "dos2unix" which converts all the
> CRLF {DOS EOL} sequences into CR {UNIX EOL} characters, then pipes the data to
> "pgsql" with a command that does a bulk insert into the table of the database
> you have selected.
>
> Guy
>
> Oliver Vecernik wrote:
> > Hi again!
> >
> > After investigating a little bit further my CSV import couldn't work
> > because of following reasons:
> >
> > 1. CSV files are delimited with CR/LF
> > 2. text fields are surrounded by double quotes
> >
> > Is there a direct way to import such files into PostgreSQL?
> >
> > I would like to have something like MySQL provides:
> >
> > LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
> >[REPLACE | IGNORE]
> >INTO TABLE tbl_name
> >[FIELDS
> >[TERMINATED BY '\t']
> >[[OPTIONALLY] ENCLOSED BY '']
> >[ESCAPED BY '\\' ]
> >]
> >[LINES TERMINATED BY '\n']
> >[IGNORE number LINES]
> >[(col_name,...)]
> >
> > Has anybody written such a function already?
> >
> > Regards,
> > Oliver
> >
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with query
=?iso-8859-1?B?UmljYXJkbyBKYXZpZXIgQXJhbmliYXIgTGXzbg==?= <[EMAIL PROTECTED]> writes: > I used PostgreSQL 7.0.3 Please consider upgrading to something more recent. 7.0 predates a lot of work we've done on query optimization ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with query
Ricardo, For future notice, there is another mailing list, PGSQL-PERFORMANCE, devoted to questions like yours. > select * from llamada where fecha='20030127' and tipo=1 and tiempo>0 > and gwdes like '64.7.127.14%' order by hora desc; > > It's very slow, it takes approximately 6 minutes to show me the > results. I saw that it's happend when I execute this types of queries > "Select...gwdes like '64.7.127.14%'.." or with field gworg. > But when i use others fields the time that it take is faster. > I used PostgreSQL 7.0.3 and I have only problem with this type query. Are you using 7.0.3 now, or something more current? I can't really help you much with 7.0.3. -Josh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LONG - Question on dealing w/ numerics
David, > I have a function that is to create a Accounting JOURNAL entry. > The strange thing is the function works for simple entries such as: > Here is the function and I can't seem to figure out what is LOGICALLY > wrong and would produce these results. I'm not sure the problem is with the function. I think the problem is with your program logic, as the funciton just inserts a *single* journal line and updates the balance. How do you insert the 4 entries required by a full double-entry transfer as you described? Also, how about posting a schema? -Josh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LONG - Question on dealing w/ numerics
David, > Cash - Debit 100 > A/R - Credit 100 > Cash - Credit 100 > A/R - Debit 100 > (Which should have a net affect of 0 on both accounts) > > But here is the resulting balance on accounts, > > Cash Debit Balance 200 > A/R Credit Balance 200 Here may your problem, and it's in the schema: is "A/R" and account or is "A/R Credit" an account? You're being unclear in your e-mail, which makes me suspect that you're being vague in your code as well. If I'm totally off the mark, then please post the four calls to your function that produced the above mis-balance, and I can easily spot the problem for you. -Josh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CSV import
> --- Oliver Vecernik <[EMAIL PROTECTED]> wrote: > > Is there a direct way to import such files into > > PostgreSQL? > > > > As I believe others have replied: no, not yet. > > Otherwise, parsing CSV > files gets just too complicated, and you are better > off using an existing solution (like a Perl module) to > preprocess your data. The DBD::CSV module allows one to use a subset of SQL syntax on CSV files, as an example. Docs are at http://search.cpan.org/author/JZUCKER/DBD-CSV-0.2002/lib/DBD/CSV.pm -- Rodger Donaldson [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LONG - Question on dealing w/ numerics
"David Durst" <[EMAIL PROTECTED]> writes: > insert into journal_lines (entry_id,account_id,line_type,line_amount) > values (eid,aid,ltype,amount); > select into line * from journal_lines where entry_id = eid AND > account_id = aid AND ltype = ltype; I bet that last should be line_type = ltype? One thing you have to watch with plpgsql's SELECT INTO is that it doesn't complain if the WHERE would match multiple rows. You get one of the rows, and no indication that others would have matched. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] checking data integrity in a recursive table
Hi, I have a table that references itself to create a tree-like structure, eg: CREATE TABLE tree ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, parent INT NULL, customer IN NOT NULL, CONSTRAINT parent_key... CONSTRAINT customer_fk FOREIGN KEY (customer) REFERENCES customer ); ALTER TABLE tree ADD CONSTRAINT FOREIGN KEY (parent) REFERENCES tree; As you can see tree also references the customer table. What I need is a CHECK that will ensuer that any given "tree" row has the same customer as its parent. Remember that "parent" can also be NULL. Or would this be better done as a trigger? TIA! Luke Pascoe KMG (NZ) Limited. http://www.kmg.co.nz Mobile: (021) 303019 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] double linked list
>> I've got a table called 'link_t' containing a collection of seller - buyer relations between two parties. << That is not a real linked list, but let's ignore bad terminology. One way to do this is with cursors, but they will take time and trend to be proprietary. Anohter way is to build a tree, with the first seller as the root and the final buyer as a leaf node. The usual example of a tree structure in SQL books is called an adjacency list model and it looks like this: CREATE TABLE OrgChart (emp CHAR(10) NOT NULL PRIMARY KEY, boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp), salary DECIMAL(6,2) NOT NULL DEFAULT 100.00); OrgChart emp boss salary === 'Albert' 'NULL'1000.00 'Bert''Albert' 900.00 'Chuck' 'Albert' 900.00 'Donna' 'Chuck'800.00 'Eddie' 'Chuck'700.00 'Fred''Chuck'600.00 Another way of representing trees is to show them as nested sets. Since SQL is a set oriented language, this is a better model than the usual adjacency list approach you see in most text books. Let us define a simple OrgChart table like this, ignoring the left (lft) and right (rgt) columns for now. This problem is always given with a column for the employee and one for his boss in the textbooks. This table without the lft and rgt columns is called the adjacency list model, after the graph theory technique of the same name; the pairs of emps are adjacent to each other. CREATE TABLE OrgChart (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); OrgChart emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 The organizational chart would look like this as a directed graph: Albert (1,12) /\ /\ Bert (2,3)Chuck (4,11) /| \ / | \ /| \ / | \ Donna (5,6) Eddie (7,8) Fred (9,10) The first table is denormalized in several ways. We are modeling both the OrgChart and the organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the OrgChart that hold those positions. Another problem with the adjacency list model is that the boss and employee columns are the same kind of thing (i.e. names of OrgChart), and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles"; you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact, one place, one time. The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case. To show a tree as nested sets, replace the emps with ovals, then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other emp. The leaf emps will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The rgt and lft columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what shows the nesting. If that mental model does not work, then imagine a little worm crawling anti-clockwise along the tree. Every time he gets to the left or right side of a emp, he numbers it. The worm stops when he gets all the way around the tree and back to the top. This is a natural way to model a parts explosion, since a final assembly is made of physically nested assemblies that final break down into separate parts. At this point, the boss column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a emp can be put in a second table and they can be joined on employee number for queries. To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top, the root, makes a complete trip around the tree. When he comes to a emp, he puts a number in the cell on the side that he is visiting and increments his counter. Each emp will get two numbers, one of the right side and one for the left. Computer Science majors will recognize this as a modified preorder tree traversal algorithm. Finally, drop the unneeded OrgChart.boss column which used to represent the edges of a graph. This has some predictable results that we can use for building queries. The root is always (left = 1, righ
[SQL] Cross-table constraints
The problem: I have two tables, add_queue and sites, on a postgresql 7.2.x database. add_queue is where items go to be reviewed by a human before being moved into sites. One of the things I'd like to do is to cut down on the amount of work done by humans filtering out dupes and sundry other problems. I'd prefer to keep the logic entirely within the DB, rather than on the client side, to make it consistent across the various interfaces into add_queue. Now, adding a UNIQUE constraint on the pk for add_queue weeds out dupes there. However, attempting to add a cross-table UNIQUE check with: alter table add_queue add constraint add_queue_no_dupe_sites unique (sites.url); ERROR: parser: parse error at or near "." ,,,fails. Foreign key constraints will only require a match (of one sort or another) in the foreign table, according to the postgresql 7.2 documentation, and do not have an option to require no match. Am I missing something obvious (syntax for UNIQUE, for example), or trying to do something that just doesn't work that way? -- Rodger Donaldson[EMAIL PROTECTED] "How do I set my laser printer for stun?" -- William Tansil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] double linked list
Hi folks!, I've got a table called 'link_t' containing a collection of seller - buyer relations between two parties. sql> select * from link_t S B - - C X A B B C C D D E 5 rows selected. I am looking for a select statement that returns the concatenation of seller - buyer relations between the first seller 'A' and the last buyer 'B'. the result should be S B - - A B B C C D D E Currently I fumbling around with self joins but haven't figured out yet. Any suggestions Many tanks in advance Cheers Juergen ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Filter function
Hello there! is there any way to create filter function? i.e. function which will be accepting and returning row sets, like select foo(t) from (select blah from tab) t; something like this. contrib/tablefunc seems not accepting a row set. also, not related to above, i've typed query: term2=>select * from tab where tab=1; ERROR: exprType: Do not know how to get type for 711 node i know that query is wrong, but may be there must be more understandable error message? regards, --- .evgen ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Scheduling Events?
On Thursday January 23 2003 5:16, David Durst wrote: > Is there anyway to schedule DB Events based on time? If you're using one of the unices (linux, etc.), how about... crontab + perl + DBI + DBD::Pg? or crontab + bash/sh + psql + pl/pgsql? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql
What about a UNIQUEIDENTIFIER type? Is the only way casting it to a CHAR(38)? (Create a domain for it) And does the performance suffer if I do the Domain/create my own data type tricks? Thanks! Wei - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Guy Fraser" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, January 28, 2003 8:55 PM Subject: Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql > Guy Fraser <[EMAIL PROTECTED]> writes: > > If you go through the documentation you can also find out how to CREATE your > > own data TYPE to allow more direct use of non-PostgreSQL data types. Here is > > an example that will allow input of any "datetime" data into PostgreSQL : > > > CREATE TYPE datetime AS (datetime timestamptz); > > I think what you probably really want is > > CREATE DOMAIN datetime AS timestamptz; > > or more SQL-spec-compliantly > > CREATE DOMAIN datetime AS timestamp with time zone; > > which essentially makes datetime a direct alias for timestamptz. The > CREATE TYPE approach makes a rowtype containing one timestamptz column, > which isn't really going to act the way you want --- for one thing, > none of the predefined functions and operators for type timestamptz > will accept it. With the DOMAIN approach, they will. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] double linked list
Juergen wrote: > Hi folks!, > > I've got a table called 'link_t' containing a collection of seller - > buyer relations between two parties. > > sql> select * from link_t > > S B > - - > C X > A B > B C > C D > D E > > 5 rows selected. > > I am looking for a select statement that returns the concatenation of > seller - buyer relations between the first seller 'A' and the last > buyer 'B'. > > the result should be > > S B > - - > A B > B C > C D > D E > > Currently I fumbling around with self joins but haven't figured out > yet. > Any suggestions > > Many tanks in advance > > Cheers > > Juergen Look at the CONNECT BY built-in function. Daniel Morgan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Rename database?
No. Try instead CREATE DATABASE newname FROM TEMPLATE oldname; DROP DATABASE oldname; ""Wei Weng"" <[EMAIL PROTECTED]> wrote in message 000c01c2c32b$09526500$5301a8c0@monet">news:000c01c2c32b$09526500$5301a8c0@monet... > This is a multi-part message in MIME format. > > --=_NextPart_000_0009_01C2C301.1FA1DB80 > Content-Type: text/plain; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > Can I use a SQL statement to rename database? > > e.g Rename Database Bank to Database Bank_backup > > Thanks! > > Wei > > --=_NextPart_000_0009_01C2C301.1FA1DB80 > Content-Type: text/html; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > > > 2"> > > > > > Can I use a SQL statement to rename database? IV> > > e.g Rename Database Bank to Database Bank_backup= > > > Thanks! > > Wei > > > --=_NextPart_000_0009_01C2C301.1FA1DB80-- > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL to list databases?
On Fri, 24 Jan 2003 05:56, you wrote: > Is there a query that will return all the databases available, similar > to what psql -l does? select * from pg_database; Horst ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Creating tables from within functions
Is it possible to issue a CREATE TABLE statement from inside of a PostgreSQL function? If not, then how about from within a PL/pgSQL function? I have tried a lot of different permutations but can't seem to get it to compile and/or run cleanly. For example, consider: CREATE FUNCTION _testcreate () RETURNS text AS ' BEGIN CREATE TABLE switch_table2 (switch_id varchar(32), selection_name varchar(100)); end; ' language 'plpgsql'; What is wrong with this? What type should be returned? Or perhaps it is simply not possible to create a table from within a function? Please give me the reply soon as possible Thanks! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CSV import
Oliver Vecernik schrieb: Hi again! After investigating a little bit further my CSV import couldn't work because of following reasons: 1. CSV files are delimited with CR/LF 2. text fields are surrounded by double quotes Is there a direct way to import such files into PostgreSQL? The answer seems to be no. But after googeling a bit a found a wonderful Python module called csv at: http://www.object-craft.com.au/projects/csv/ A minimal script called 'csv2tab.py' for conversion to a tab delimited file could be: #!/usr/bin/env python import csv import sys def convert(file): try: f = open(file, 'r') lines = f.readlines() p = csv.parser() for line in lines: print '\t'.join(p.parse(line)) except: print 'Error opening file!' if __name__ == '__main__': convert(sys.argv[1]); Regards, Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(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] Cross-table constraints
On Tue, 28 Jan 2003, Rodger Donaldson wrote: > Now, adding a UNIQUE constraint on the pk for add_queue weeds out > dupes there. However, attempting to add a cross-table UNIQUE check > with: > > alter table add_queue add constraint add_queue_no_dupe_sites unique > (sites.url); > ERROR: parser: parse error at or near "." > > ,,,fails. Foreign key constraints will only require a match (of one > sort or another) in the foreign table, according to the postgresql 7.2 > documentation, and do not have an option to require no match. > > Am I missing something obvious (syntax for UNIQUE, for example), or > trying to do something that just doesn't work that way? I'm not sure what a unique constraint on sites.url on table add_queue is supposed to mean precisely. You can probably come pretty close by making a set of triggers on both tables however. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cross-table constraints
On Wed, 29 Jan 2003, Rodger Donaldson wrote: > > On Tue, 28 Jan 2003, Rodger Donaldson wrote: > > > > > Now, adding a UNIQUE constraint on the pk for add_queue weeds out > > > dupes there. However, attempting to add a cross-table UNIQUE check > > > with: > > > > > > alter table add_queue add constraint add_queue_no_dupe_sites unique > > > (sites.url); > > > ERROR: parser: parse error at or near "." > > > > > > ,,,fails. Foreign key constraints will only require a match (of one > > > sort or another) in the foreign table, according to the postgresql > 7.2 > > > documentation, and do not have an option to require no match. > > > > > > Am I missing something obvious (syntax for UNIQUE, for example), or > > > trying to do something that just doesn't work that way? > > > > I'm not sure what a unique constraint on sites.url on table add_queue > > is supposed to mean precisely. > > Sorry; to clarify, it's table.column notation. I meant to signify what's the behavior? Is it different from a unique constraint on sites(url)? If so, how? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Please include hier-patch in next PostgreSQL version
Hello! I want to say that it would be fantastic when you include the Oracle like "CONNECT BY" patch (see gppl.terminal.ru/readme.html) in the next version of PostgreSQL. It is very usefull for people that have to handle such kind of hierarchical data. -- Dipl. Inform. Boris Klug, control IT GmbH, Germany ---(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
