Re: [SQL] Which version of PostgreSQL should I use.
Just thought I would update this myself. Last night I managed to have a chat with some people about this on IRC. Turns out we're in pretty good shape. We already know about the changes to implicit casting and we have always used UTF-8 for encoding, too. So the changes from 8.4 don't seem to be of a worry for us. (I am sure that we will find SQL hidden in our app somewhere that will need to be modified for implicit casting...and we'll just fix those as we find them.) Otherwise it seems like a simple case of using the 9.04 pg_dump / (pg_dumpall) application and restoring. I also read chapters 15 / 24 of the excellent documentation about running both 8.1 and 9.04 at the same time and migrating a database at a time. So, I suppose unless there is something obvious, (to someone else) - we have all the information we need. Gavin "Beau" Baumanis On 17/05/2011, at 9:04 AM, Gavin Baumanis wrote: > Hi there, > > Let me first preface this with, I am not a PostgreSQL admin. > I am a web developer who happens to use PSQL as the back-end for my company's > app. > We did have a dedicated DBA / system admin - but he has recently resigned. > I know enough about psql - to be able to create / drop databases... and > enough about SQL to get stuff in and out of the database. > Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail. > > > We're currently using psql 8.1 and are on the way to upgrading to 8.4. > This is a process that the last DBA had us start. > And we've slowly been going through our code, getting rid of implicit casts > as errors appear. > > I have now been asked to start replicating our databases between servers - as > a hot-copy / redundancy improvement. > And subsequently have some questions, please. > > Is there are a particular version of PostgreSQL that we should be "aiming" to > upgrade to that provides for synching of databases. > My initial thought is; > We should upgrade to the latest stable version - whatever that is; > But is the answer that simple? > > What we do we also need to take into account? > I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that > we had to upgrade to 8.3 first. > > So I guess I am hoping that someone might just simply know - or be able to > pint me in the correct direction for some information about what's in what > version and any upgrade requirements to get to XXX from 8.1 > > AS always - thanks in advance for any assistance you might be able to give > us! > > Gavin. > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
On 05/22/2011 09:42 PM, Craig Ringer wrote: On 23/05/2011 9:11 AM, Andrej wrote: On 23 May 2011 10:00, Tarlika Elisabeth Schmitz wrote: On Sun, 22 May 2011 21:05:26 +0100 Tarlika Elisabeth Schmitz wrote: A column contains location information, which may contain any of the following: 1) null 2) country name (e.g. "France") 3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen") 4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen") 5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen") I also need to cope with variations of COUNTRY.NAME and REGION.NAME. That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns. Really! How you get your raw data into those columns can be interesting. This is a hard problem. You're dealing with free-form data that might be easily understood by humans, but relies on various contextual information and knowledge that makes it really hard for computers to understand. If you want to do a good job of this, your best bet is to plug in 3rd party address analysis software that is dedicated to this task. Most (all?) such These aren't really addresses, as the OP presents them. packages are commercial, proprietary affairs. They exist because it's really, really hard to do this right. Another thing of great import is whether the city can occur in the data column all by itself; if yes, it's next to impossible to distinguish it from a country. Not least because some places are both, eg: Luxembourg The Vatican Singapore (The Grand Duchy of Luxembourg has other cities, but still serves as an example). And,of course, you have to distinguish the City of London from London. New York City comprises five boroughs (counties), each of which is itself a city. (Brooklyn is one of the largest cities in the world all by itself.) "Region" has different meanings in different areas - it can mean part of a county, or state / province, or nation, or continent. "The Baltic region", "the Northeast", "upstate", "the North Country", "Europe" are all regions. The OP should share more about the semantics of their problem domain and whether they really intend those table structures to be table structures. Really? -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
I would think that changing the location column to hold a FK to a location table, and setting up the location table with various columns for city, region, country and whatever else might be required would be the way to go. It reduces column bloat on the main table, provides reuse of location data and is easier to modify in the future. Edward W. Rouse -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Lew Sent: Monday, May 23, 2011 12:25 AM To: [email protected] Subject: Re: [SQL] extracting location info from string On 05/22/2011 09:42 PM, Craig Ringer wrote: > On 23/05/2011 9:11 AM, Andrej wrote: >> On 23 May 2011 10:00, Tarlika Elisabeth Schmitz >> wrote: >>> On Sun, 22 May 2011 21:05:26 +0100 >>> Tarlika Elisabeth Schmitz wrote: >>> A column contains location information, which may contain any of the following: 1) null 2) country name (e.g. "France") 3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen") 4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen") 5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen") >>> >>> >>> I also need to cope with variations of COUNTRY.NAME and REGION.NAME. That isn't a table structure, that's a freeform text structure. You didn't state your question, Tarlika, but your database structure is terrible. For example, "region" and "country" should be different columns. Really! How you get your raw data into those columns can be interesting. > This is a hard problem. You're dealing with free-form data that might be > easily understood by humans, but relies on various contextual information and > knowledge that makes it really hard for computers to understand. > > If you want to do a good job of this, your best bet is to plug in 3rd party > address analysis software that is dedicated to this task. Most (all?) such These aren't really addresses, as the OP presents them. > packages are commercial, proprietary affairs. They exist because it's really, > really hard to do this right. > >> Another thing of great import is whether the city can occur in the >> data column all by itself; if yes, it's next to impossible to distinguish >> it from a country. > > Not least because some places are both, eg: > > Luxembourg > The Vatican > Singapore > > (The Grand Duchy of Luxembourg has other cities, but still serves as an > example). And,of course, you have to distinguish the City of London from London. New York City comprises five boroughs (counties), each of which is itself a city. (Brooklyn is one of the largest cities in the world all by itself.) "Region" has different meanings in different areas - it can mean part of a county, or state / province, or nation, or continent. "The Baltic region", "the Northeast", "upstate", "the North Country", "Europe" are all regions. The OP should share more about the semantics of their problem domain and whether they really intend those table structures to be table structures. Really? -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Which version of PostgreSQL should I use.
Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text. Jasmin A1 präsentiert BlackBerry® von Vodafone -Original Message- From: Gavin Baumanis Sender: [email protected]: Sun, 22 May 2011 09:10:17 To: Gavin Baumanis Cc: Subject: Re: [SQL] Which version of PostgreSQL should I use. Just thought I would update this myself. Last night I managed to have a chat with some people about this on IRC. Turns out we're in pretty good shape. We already know about the changes to implicit casting and we have always used UTF-8 for encoding, too. So the changes from 8.4 don't seem to be of a worry for us. (I am sure that we will find SQL hidden in our app somewhere that will need to be modified for implicit casting...and we'll just fix those as we find them.) Otherwise it seems like a simple case of using the 9.04 pg_dump / (pg_dumpall) application and restoring. I also read chapters 15 / 24 of the excellent documentation about running both 8.1 and 9.04 at the same time and migrating a database at a time. So, I suppose unless there is something obvious, (to someone else) - we have all the information we need. Gavin "Beau" Baumanis On 17/05/2011, at 9:04 AM, Gavin Baumanis wrote: > Hi there, > > Let me first preface this with, I am not a PostgreSQL admin. > I am a web developer who happens to use PSQL as the back-end for my company's > app. > We did have a dedicated DBA / system admin - but he has recently resigned. > I know enough about psql - to be able to create / drop databases... and > enough about SQL to get stuff in and out of the database. > Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail. > > > We're currently using psql 8.1 and are on the way to upgrading to 8.4. > This is a process that the last DBA had us start. > And we've slowly been going through our code, getting rid of implicit casts > as errors appear. > > I have now been asked to start replicating our databases between servers - as > a hot-copy / redundancy improvement. > And subsequently have some questions, please. > > Is there are a particular version of PostgreSQL that we should be "aiming" to > upgrade to that provides for synching of databases. > My initial thought is; > We should upgrade to the latest stable version - whatever that is; > But is the answer that simple? > > What we do we also need to take into account? > I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that > we had to upgrade to 8.3 first. > > So I guess I am hoping that someone might just simply know - or be able to > pint me in the correct direction for some information about what's in what > version and any upgrade requirements to get to XXX from 8.1 > > AS always - thanks in advance for any assistance you might be able to give > us! > > Gavin. > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
On Mon, 23 May 2011 00:25:16 -0400 Lew wrote: >That isn't a table structure, that's a freeform text structure. You >didn't state your question, Tarlika, but your database structure is >terrible. For example, "region" and "country" should be different >columns. I presume you are referring to my original post: CREATE TABLE person ( id integer NOT NULL, "name" character varying(256) NOT NULL, "location" character varying(256), CONSTRAINT person_pkey PRIMARY KEY (id) ); Sorry, this was just a TEMPORARY table I created for quick analysis of my CSV data (now renamed to temp_person). The target table is: CREATE TABLE person ( id integer NOT NULL, "name" character varying(100) NOT NULL, country character varying(3), county character varying(3), town character varying(50), CONSTRAINT trainer_pkey PRIMARY KEY (id), CONSTRAINT country_person_fk FOREIGN KEY (country) REFERENCES country (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT county_person_fk FOREIGN KEY (country, county) REFERENCES county (country, code) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, ); -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new install)
I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
updates have been effected. I used webmin to restore the databases.
Since migrating to Linux around 2006 we were successful in converting our mdb
file to postgresql - only one glitch was a difference in -1 as YES. We
successfully used the systems with open office as a front end for the last few
years. Until now - after the upgrade I can view data but not update data.
Experience with sql is very limited as I have always used sql query builders
over the years. The system has worked well with multiple tables, and in the
past there was no problems with sending sql statements to postgresql.
My problem in OOO 3.2 base (other front ends also) is that there is
continually a problem with not allowing queries to update data back to
postgresql.
I believe something has changed perhaps in sql from 8.3 to 8.4, or I have
missed something with restoring the files.
I enclose a typical sql statement for a table.
-- Table: "FINANCEDETAILS"
-- DROP TABLE "FINANCEDETAILS";
CREATE TABLE "FINANCEDETAILS"
(
"FINDETID" serial NOT NULL,
"INVREF" integer DEFAULT 0,
"INITIALFINDATE" timestamp without time zone,
"RESTARTFIN" timestamp without time zone,
"PAYDAY" integer DEFAULT 1,
"AMOUNT" double precision DEFAULT (0)::double precision,
"DEBITACC" integer DEFAULT 1,
"DEBITDIVNO" integer,
"CREDITACC" integer DEFAULT 0,
"CREDDIVNO" integer,
"TRANSDESCRIPTION" character varying(50) DEFAULT 'Support'::character
varying,
"COMMITMENT" character varying(50) DEFAULT 'Casual'::character varying,
"SUPPORTBY" character varying(25),
"EXPDATE" timestamp without time zone,
"BANK" character varying(50),
"BRANCH" character varying(50),
"BRANCHCODE" character varying(50),
"BKACCOUNTTYPE" character varying(50) DEFAULT 'Cheque'::character varying,
"ACCOUNTNUMBER" character varying(50),
"TYPETRANS" integer DEFAULT 0,
"REMITTANCE" boolean,
"RECEIPT" boolean,
"JOURNAL" boolean,
"VOUCHERREQ" boolean,
"STANDINGORDER" boolean,
"STDORDAUTHDETAILS" text,
"OLDFINREF" numeric,
"COMMENTS" text,
CONSTRAINT "FINANCEDETAILS_pkey" PRIMARY KEY ("FINDETID"),
CONSTRAINT "FINANCEDETAILS_FINDETID_key" UNIQUE ("FINDETID")
)
WITH (
OIDS=TRUE
);
ALTER TABLE "FINANCEDETAILS" OWNER TO dean;
GRANT ALL ON TABLE "FINANCEDETAILS" TO public;
# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced
with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server,
e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run
time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytesTime units: ms = milliseconds
#MB = megabytes s = seconds
#GB = gigabytes min = minutes
# h = hours
# d = days
#--
# FILE LOCATIONS
#--
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
data_directory = '/var/lib/postgresql/8.4/main' # use data in another
directory
# (change requires restart)
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf' # host-based
authentication
file
# (change requires restart)
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf' # ident configuration
file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/8.4-main.pid' # write an
extra PID file
# (change requires restart)
#--
# CONNECTIONS AND AUTHENTICATION
#--
Re: [SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
On Mon, May 23, 2011 at 9:53 AM, Dean le Roux wrote: > postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new > install) > > I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other > updates have been effected. I used webmin to restore the databases. > > Since migrating to Linux around 2006 we were successful in converting our > mdb > file to postgresql - only one glitch was a difference in -1 as YES. We > successfully used the systems with open office as a front end for the last > few > years. Until now - after the upgrade I can view data but not update data. > > Experience with sql is very limited as I have always used sql query > builders > over the years. The system has worked well with multiple tables, and in the > past there was no problems with sending sql statements to postgresql. > > My problem in OOO 3.2 base (other front ends also) is that there is > continually a problem with not allowing queries to update data back to > postgresql. > > I believe something has changed perhaps in sql from 8.3 to 8.4, or I have > missed something with restoring the files. > > At minimum, we need to see the update statement that you are attempting to execute, preferably with any error message you receive.
Re: [SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
Here is the sql statements. We don't pass update, I guess base or the relevant
programmes does that. Some transactions that are based on forms seem to
update, but sometimes they don't, and it would seem that certain constraints
are not met, causing holes in the database. This didn't happen prior to
upgrade - it would tell you that you couldn't delete the record, upholding
constraints.
SELECT "HOMINVOLV"."PROJREF", "HOMNLPOST"."LANGREF", "HOMINVOLV"."HISTORYREC",
"HOMINVOLV"."RESTRICTEDACCESS", "HOMADDRESS"."ADDRESSSTOP",
"HOMNLPOST"."SENDNL", "HOMINVOLV"."TITLEREF", "HOMINVOLV"."INITIALS",
"HOMNLPOST"."PERSNAME", "HOMCONT"."LASTNAME", "HOMCONCERN"."POSTHELD",
"HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC", "HOMADDRESS"."ADD1",
"HOMADDRESS"."ADD2", "HOMADDRESS"."ADD3", "HOMADDRESS"."ADD4",
"HOMADDRESS"."ADD5", "HOMADDRESS"."ADD6", "HOMNLPOST"."ENVELOPE",
"HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM { OJ "public"."HOMCONCERN"
AS "HOMCONCERN" RIGHT OUTER JOIN "public"."HOMCONT" AS "HOMCONT" RIGHT OUTER
JOIN "public"."HOMINVOLV" AS "HOMINVOLV" RIGHT OUTER JOIN
"public"."HOMADDRESS" AS "HOMADDRESS" LEFT OUTER JOIN "public"."HOMNLPOST" AS
"HOMNLPOST" ON "HOMADDRESS"."ADDREF" = "HOMNLPOST"."ADDREF" ON
"HOMINVOLV"."INVREF" = "HOMNLPOST"."INVREF" ON "HOMCONT"."CONTREF" =
"HOMADDRESS"."CONTREF" ON "HOMCONCERN"."CONCREF" = "HOMADDRESS"."CONCREF" }
WHERE "HOMINVOLV"."PROJREF" = :proj AND "HOMNLPOST"."LANGREF" = :lang AND
"HOMINVOLV"."HISTORYREC" = '1' AND "HOMINVOLV"."RESTRICTEDACCESS" = '0' AND
"HOMADDRESS"."ADDRESSSTOP" = '0' AND "HOMNLPOST"."SENDNL" = '1' ORDER BY
"HOMCONT"."LASTNAME" ASC
This query which would have run before in prior setting is no longer working.
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: syntax error at or near "{"
LINE 1: ...HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM { OJ "publ...
^
(caused by statement 'SELECT "HOMINVOLV"."PROJREF", "HOMNLPOST"."LANGREF",
"HOMINVOLV"."HISTORYREC", "HOMINVOLV"."RESTRICTEDACCESS",
"HOMADDRESS"."ADDRESSSTOP", "HOMNLPOST"."SENDNL", "HOMINVOLV"."TITLEREF",
"HOMINVOLV"."INITIALS", "HOMNLPOST"."PERSNAME", "HOMCONT"."LASTNAME",
"HOMCONCERN"."POSTHELD", "HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC",
"HOMADDRESS"."ADD1", "HOMADDRESS"."ADD2", "HOMADDRESS"."ADD3",
"HOMADDRESS"."ADD4", "HOMADDRESS"."ADD5", "HOMADDRESS"."ADD6",
"HOMNLPOST"."ENVELOPE", "HOMINVOLV"."INVREF", "HOMNLPOST"."AFRIGREET" FROM {
OJ "public"."HOMCONCERN" AS "HOMCONCERN" RIGHT OUTER JOIN "public"."HOMCONT"
AS "HOMCONT" RIGHT OUTER JOIN "public"."HOMINVOLV" AS "HOMINVOLV" RIGHT OUTER
JOIN "public"."HOMADDRESS" AS "HOMADDRESS" LEFT OUTER JOIN
"public"."HOMNLPOST" AS "HOMNLPOST" ON "HOMADDRESS"."ADDREF" =
"HOMNLPOST"."ADDREF" ON "HOMINVOLV"."INVREF" = "HOMNLPOST"."INVREF" ON
"HOMCONT"."CONTREF" = "HOMADDRESS"."CONTREF" ON "HOMCONCERN"."CONCREF" =
"HOMADDRESS"."CONCREF" } WHERE "HOMINVOLV"."PROJREF" = '1017' AND
"HOMNLPOST"."LANGREF" = 'English' AND "HOMINVOLV"."HISTORYREC" = '1' AND
"HOMINVOLV"."RESTRICTEDACCESS" = '0' AND "HOMADDRESS"."ADDRESSSTOP" = '0' AND
"HOMNLPOST"."SENDNL" = '1' ORDER BY "HOMCONT"."LASTNAME" ASC')
SELECT "HOMNLEMAIL"."LANGUAGE", "HOMINVOLV"."PROJREF", "HOMNLEMAIL"."SEND",
"HOMINVOLV"."RESTRICTEDACCESS", "HOMINVOLV"."HISTORYREC",
"HOMINVOLV"."TITLEREF", "HOMINVOLV"."INITIALS", "HOMCONT"."LASTNAME",
"HOMCONCERN"."POSTHELD", "HOMCONCERN"."CONCERN", "HOMCONCERN"."BRANCHOFCONC",
"HOMINVOLV"."INVREF", "HOMEMAIL"."emailadd", "HOMEMAIL"."emailstop",
"HOMNLEMAIL"."PERSNAMES", "HOMNLEMAIL"."AFRIGREET" FROM "HOMCONT" AS "HOMCONT"
RIGHT OUTER JOIN "HOMCONCERN" AS "HOMCONCERN" RIGHT OUTER JOIN "HOMINVOLV" AS
"HOMINVOLV" LEFT OUTER JOIN "HOMNLEMAIL" AS "HOMNLEMAIL" ON
"HOMINVOLV"."INVREF" = "HOMNLEMAIL"."INVREF" LEFT OUTER JOIN "HOMEMAIL" AS
"HOMEMAIL" ON "HOMNLEMAIL"."EMAILREF" = "HOMEMAIL"."EMAILREF" ON
"HOMCONCERN"."CONCREF" = "HOMINVOLV"."CONCREF" ON "HOMCONT"."CONTREF" =
"HOMINVOLV"."CONTREF" WHERE "HOMNLEMAIL"."LANGUAGE" = 'English' AND
"HOMINVOLV"."PROJREF" = 1017 AND "HOMNLEMAIL"."SEND" = '1' AND
"HOMINVOLV"."RESTRICTEDACCESS" = '0' AND "HOMINVOLV"."HISTORYREC" = '1' AND
"HOMEMAIL"."emailstop" = '0' ORDER BY "HOMCONT"."LASTNAME" ASC
Not potential to edit this data
On Monday 23 May 2011 20:01:16 you wrote:
> On Mon, May 23, 2011 at 9:53 AM, Dean le Roux wrote:
> > postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new
> > install)
> >
> > I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
> > updates have been effected. I used webmin to restore the databases.
> >
> > Since migrating to Linux around 2006 we were successful in converting our
> > mdb
> > file to postgresql - only one glitch was a difference in -1 as YES. We
> > successfully used the systems with open office as a front end for the
> > last few
> > years. Until now - after the upgrade I can view data but not update data.
> >
> > Exp
Re: [SQL] extracting location info from string
On Mon, 23 May 2011 13:11:24 +1200 Andrej wrote: >On 23 May 2011 10:00, Tarlika Elisabeth Schmitz > wrote: >> On Sun, 22 May 2011 21:05:26 +0100 >> Tarlika Elisabeth Schmitz wrote: >> >>>A column contains location information, which may contain any of the >>>following: >>> >>>1) null >>>2) country name (e.g. "France") >>>3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen") >>>4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen") >>>5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen") >> >> >> I also need to cope with variations of COUNTRY.NAME and REGION.NAME. > >sanitising that data will be tedious, - particularly with the >variations on region. Indeed. However, the situation is not quite as bleak as it appears: - I am only dealing with 50 countries (Luxemburg and Vatican are not amongst them) - Only for two countries will city/region be displayed instead of country. - Ultimately, where the only important bit of imformation is the country. - The only really important persons are those from the two countries. >Another thing of great import is whether the city can occur in the >data column all by itself; if yes, it's next to impossible to >distinguish it from a country. Unfortunately this is the case. >Specially if we assume that >typos/misspelling are feasible on top of punctuation ... and former countries like Czechoslovakia ... >If I had a task like that to perform I'd dump the data out to file >and have a good go at it w/ sed & awk, or perl, depending on >how complex & voluminous the data is. I had a quick look at the data (maybe not the most efficient SQL) SELECT id, name, CASE WHEN location is null then null WHEN location !~ '.*,.*' then (select id from country where name = location) ELSE (select country from county where name = regexp_replace(location, '.*, (Rg\\.? )?(.*)', '\\2')) END AS country, location FROM temp_person Of 17000 historical records, 4400 don't match this simple pattern. Of the 4400, 1300 are "USA" or "Usa" instead of "United States", 900 "North America" whatever that is! There are plenty of common + valid region abbreviations. I get about 1000 new records of this type per year. I presume that more recent data are more accurate. I know I won't be able to clean them all up. However, the import process has to be as automatic as possible in such a way that inconsistencies are flagged up for later manual intervention. I say later because, for instance, a person's data will have to be imported with or without location info because other new data will link to it. -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Which version of PostgreSQL should I use.
On 23/05/2011 11:19 PM, [email protected] wrote: Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text. Er ... what? Can you elaborate on that? Do you mean large objects? bytea fields? Something else? -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
On 24/05/2011 6:39 AM, Tarlika Elisabeth Schmitz wrote: Indeed. However, the situation is not quite as bleak as it appears: - I am only dealing with 50 countries (Luxemburg and Vatican are not amongst them) - Only for two countries will city/region be displayed instead of country. - Ultimately, where the only important bit of imformation is the country. - The only really important persons are those from the two countries. Ah, see that's critical. You've just been able to restrict the problem domain to a much simpler task with a smaller and well-defined range of possibilities. Most of the complexity is in the nasty corner cases and weirdness, and you've (probably) just cut most of that away. Of 17000 historical records, 4400 don't match this simple pattern. Of the 4400, 1300 are "USA" or "Usa" instead of "United States", 900 "North America" whatever that is! There are plenty of common + valid region abbreviations. I get about 1000 new records of this type per year. I'd do this kind of analysis in a PL/Perl or PL/python function myself. It's easier to write "If then else " logic in a readable form, and such chained tests are usually better for this sort of work. That also makes it easier to do a cleanup pass first, where you substitute common spelling errors and canonicalize country names. However, the import process has to be as automatic as possible in such a way that inconsistencies are flagged up for later manual intervention. I say later because, for instance, a person's data will have to be imported with or without location info because other new data will link to it. That's another good reason to use a PL function for this cleanup work. It's easy to INSERT a record into a side table that flags it for later examination if necessary, and to RAISE NOTICE or to issue a NOTIFY if you need to do closer-to-realtime checking. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
On Monday, May 23, 2011 9:53:47 am Dean le Roux wrote:
> postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new
> install)
Did the OO version change also?
>
> I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other
> updates have been effected. I used webmin to restore the databases.
>
> Since migrating to Linux around 2006 we were successful in converting our
> mdb file to postgresql - only one glitch was a difference in -1 as YES. We
> successfully used the systems with open office as a front end for the last
> few years. Until now - after the upgrade I can view data but not update
> data.
Usually, in Base, that indicates that it can not find a primary key.
FYI:
The below is redundant:
CONSTRAINT "FINANCEDETAILS_pkey" PRIMARY KEY ("FINDETID"),
CONSTRAINT "FINANCEDETAILS_FINDETID_key" UNIQUE ("FINDETID")
From the docs:
http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html
"Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but
identifying a set of columns as primary key also provides metadata about the
design of the schema, as a primary key implies that other tables can rely on
this set of columns as a unique identifier for rows. "
>
> Experience with sql is very limited as I have always used sql query
> builders over the years. The system has worked well with multiple tables,
> and in the past there was no problems with sending sql statements to
> postgresql.
>
> My problem in OOO 3.2 base (other front ends also) is that there is
> continually a problem with not allowing queries to update data back to
> postgresql.
What are you using to connect to the database JDBC, ODBC, other?
>
> I believe something has changed perhaps in sql from 8.3 to 8.4, or I have
> missed something with restoring the files.
Did you do a full restore or selective?
>
>
> Any assistance offered will be appreciated.
--
Adrian Klaver
[email protected]
Re: [SQL] Which version of PostgreSQL should I use.
On 24/05/11 14:30, [email protected] wrote: > We had trbls with our drupal site, because some settings are stored in bytea > columns. I think lobs are a problem too, but pls see the pg_dump docs for > details. Is it possible that you are referring to the `bytea_output' setting in PostgreSQL 8.4 and above? If so, the issue isn't that pg_dump somehow fails to dump the bytea data. Rather, it's that drupal doesn't deal well with bytea data from newer versions of PostgreSQL until the bytea_output setting is changed to 'escape' because it doesn't understand the new hex format. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
