[SQL] Update Mass Data in Field?
I'm new to SQL so I'm looking for a way to change several email addresses with one command. For example everyone has a 'holyghost.org' domain and I need to change a few 100 email addresses in the field 'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email' and change %holyghost.org to %ghostsoftware.com. I tried: UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE '%holyghost.org'; It didn't update anything when I ran the command. Does anyone know how I need to correct my SQL statement above to change everyone's email address? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Update Mass Data in Field?
On 01/26/2012 03:59 PM, Carlos Mennens wrote: I'm new to SQL so I'm looking for a way to change several email addresses with one command. For example everyone has a 'holyghost.org' domain and I need to change a few 100 email addresses in the field 'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email' and change %holyghost.org to %ghostsoftware.com. I tried: UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE '%holyghost.org'; It didn't update anything when I ran the command. Does anyone know how I need to correct my SQL statement above to change everyone's email address? ...set emp_email = regexp_replace(emp_email, '@holyghost.org$', '@ghostingsoftware.org') where emp_email ~ '@holyghost.org$'... This is using the regular expression match and regexp_replace to ensure that the pattern is anchored at the end of the field and includes the "@" sign in the expression to avoid accidentally matching something like ...@theholyghost.org. You can always do a select of the emp_email alongside the replacement expression to be sure it will do what you want before actually updating your database. Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Update Mass Data in Field?
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Thursday, January 26, 2012 6:59 PM To: PostgreSQL (SQL) Subject: [SQL] Update Mass Data in Field? I'm new to SQL so I'm looking for a way to change several email addresses with one command. For example everyone has a 'holyghost.org' domain and I need to change a few 100 email addresses in the field 'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email' and change %holyghost.org to %ghostsoftware.com. I tried: UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE '%holyghost.org'; It didn't update anything when I ran the command. Does anyone know how I need to correct my SQL statement above to change everyone's email address? Like Steve said, use the "regexp_replace" function. However, I am concerned that nothing updated when you executed the above. What you should have seen happen is that EVERY email address ending with "holyghost.org" became changed to the literal value "%ghostsoftware.com" - which obviously is not an e-mail address - and the original "holyghost.org" email address would have been gone beyond easy recovery. Takeaway: test update queries on sample data (or at least within a transaction block) and confirm your results before executing against live data (or committing the transaction). David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Getting a list of a table's attributes that are sortable sorted by uniqueness
I wrote: > I frequently use pg_dump to dump databases and compare them > with diff. To get rid of most "false positives", I'd like > to patch pg_dump to sort the table so that its dumped order > isn't changed more than necessary by insertions & Co. > So I'm looking for a query that will return a list of a > table's attributes that are sortable (e. g. no XML fields) > and sorted by "uniqueness", i. e. first attributes repre- > senting the primary key, then other unique keys, then the > rest. > Before I dive into the depths of PostgreSQL's system cata- > logues, has anyone already solved this problem? Progress report: The query: | SELECT attname, |attnum, |keyrank, |columnrankinkey | FROM pg_attribute | LEFT JOIN | (SELECT DISTINCT ON (tableid, |columnnr) indrelid as tableid, | indkey [subscript] AS columnnr, | CASE | WHEN indisprimary THEN 0 | WHEN indisunique THEN 1 | ELSE 2 | END as keyrank, | subscript as columnrankinkey |FROM | (SELECT indrelid, | indkey, | generate_subscripts(indkey, 1) as subscript, | indisprimary, | indisunique | FROM pg_index | ORDER BY indrelid, |indkey, |indisprimary DESC, indisunique DESC) AS s |ORDER BY tableid, columnnr, CASE |WHEN indisprimary THEN 0 |WHEN indisunique THEN 1 |ELSE 2 |END, columnrankinkey) AS s2 ON attrelid = tableid | AND attnum = columnnr | WHERE attrelid = 'tablename'::regclass | AND NOT attisdropped | AND attnum > 0 | ORDER BY keyrank, | columnrankinkey, | attnum; does almost what I want except: - Attributes that can't be sorted (XML) aren't skipped, and - "UNIQUE(A, B)" and "UNIQUE(C, D)" would give "A, C, B, D" (untested) so the "rank" of a non-primary key has yet to be included. Stay tuned. Tim (looking forward to "UNNEST ... WITH ORDINALITY") -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
