Re: [GENERAL] overwrite column data select - Postgres 9.2

[Patrick B]

>
> Rather than test.u...@example.com  I was hoping for
> values such as:
>
> test.4645364.@ example.com
> 
>
> test.8786756.@ example.com
> 
>
>
> With UNIQUE UUID
>
>
> is that possible?
>
>
>
I was able to do that using:

SELECT cast(''test.''|| uuid_generate_v1() AS varchar(30)) || ''@example.com
''

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Patrick B]

2016-09-22 10:02 GMT+12:00 Jim Nasby :

> On 9/21/16 1:50 PM, Steve Petrie, P.Eng. wrote:
>
>>
>> The reason I ask is -- the maximum length of a valid email address is
>> actually 256 characters (or 254, according comments in the PHP function
>> is_valid_email_address(...) that I found on the Internet at
>> http://code.iamcal.com/ and use myself).
>>
>> In my own PG DDL, I define email addresses like:
>>
>>  contact_email_addr varchar(256) NOT NULL,
>>
>
> FWIW, I stay away from varchar limits that are more than a "suggestion".
> Generally speaking it's just not worth limiting to something like 50, then
> the business decides they want 60, then 70, then... I still use varchar to
> ensure the database can't get DOS'd with garbage, but I'll just set
> something like varchar(100). That said, if there's a defined limit for
> email address length, might as well use it...
>
> Disclosure: I'm a PG newbie (and a relative SQL newbie, too), and not
>> familiar with the DEFAULT ":: notation in your DDL.
>>
>
> The :: is a cast that was presumably added by Postgres when the default
> was assigned. It's equivalent to DEFAULT ''. I definitely don't like
> defaults like that... if you don't know what the email is then it should be
> NULL. Or to put it another way, having a default set largely defeats the
> purpose of NOT NULL (IMHO).
> --
>
>





Rather than test.u...@example.com  I was hoping for
values such as:

test.4645364.@ example.com 

test.8786756.@ example.com 


With UNIQUE UUID


is that possible?


Cheers

Patrick

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Jim Nasby]

On 9/21/16 1:50 PM, Steve Petrie, P.Eng. wrote:


The reason I ask is -- the maximum length of a valid email address is
actually 256 characters (or 254, according comments in the PHP function
is_valid_email_address(...) that I found on the Internet at
http://code.iamcal.com/ and use myself).

In my own PG DDL, I define email addresses like:

 contact_email_addr varchar(256) NOT NULL,


FWIW, I stay away from varchar limits that are more than a "suggestion". 
Generally speaking it's just not worth limiting to something like 50, 
then the business decides they want 60, then 70, then... I still use 
varchar to ensure the database can't get DOS'd with garbage, but I'll 
just set something like varchar(100). That said, if there's a defined 
limit for email address length, might as well use it...



Disclosure: I'm a PG newbie (and a relative SQL newbie, too), and not
familiar with the DEFAULT ":: notation in your DDL.


The :: is a cast that was presumably added by Postgres when the default 
was assigned. It's equivalent to DEFAULT ''. I definitely don't like 
defaults like that... if you don't know what the email is then it should 
be NULL. Or to put it another way, having a default set largely defeats 
the purpose of NOT NULL (IMHO).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Patrick B]

kbran...@pwhome.com

Yes.. it is a conde issue and not a DB issue



2016-09-22 6:50 GMT+12:00 Steve Petrie, P.Eng. :

> Hi Patrick.
>
> - Original Message - From: "Patrick B" 
> To: "pgsql-general" 
> Sent: Wednesday, September 21, 2016 1:02 AM
> Subject: [GENERAL] overwrite column data select - Postgres 9.2
>
>
> I've got a table with email column:
>>
>> email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,
>>>
>>
>>
> An off-topic question.
>
> Does your application explicitly restrict the length of email addresses to
> 50 characters? Are you able to dictate an email address maximum length of
> 50 characters? Or do you actually expect your application to be able accept
> any valid email address from users?
>
> The reason I ask is -- the maximum length of a valid email address is
> actually 256 characters (or 254, according comments in the PHP function
> is_valid_email_address(...) that I found on the Internet at
> http://code.iamcal.com/ and use myself).
>
> In my own PG DDL, I define email addresses like:
>
>  contact_email_addr varchar(256) NOT NULL,
>
> Disclosure: I'm a PG newbie (and a relative SQL newbie, too), and not
> familiar with the DEFAULT ":: notation in your DDL.
>
> Steve
>
>
>
>> There are 30k rows and the email column is not null... there is data in
>> there.
>> But for testing purpose I need to overwrite the email. So the customer
>> won't get an email from me while testing code.
>>
>> The email could be replaced by: test@example.com
>>
>> How can I do that?
>> Thanks!
>> Patrick
>>
>>
>
That's just a test server it's not a copy from production or either
production environment
I'm just testing the commands... on production the email column is
varchar(255)

Patrick

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Steve Petrie, P.Eng.]

Hi Patrick.

- Original Message - 
From: "Patrick B" 

To: "pgsql-general" 
Sent: Wednesday, September 21, 2016 1:02 AM
Subject: [GENERAL] overwrite column data select - Postgres 9.2



I've got a table with email column:


email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,




An off-topic question.

Does your application explicitly restrict the length of email addresses 
to 50 characters? Are you able to dictate an email address maximum 
length of 50 characters? Or do you actually expect your application to 
be able accept any valid email address from users?


The reason I ask is -- the maximum length of a valid email address is 
actually 256 characters (or 254, according comments in the PHP function 
is_valid_email_address(...) that I found on the Internet at 
http://code.iamcal.com/ and use myself).


In my own PG DDL, I define email addresses like:

 contact_email_addr varchar(256) NOT NULL,

Disclosure: I'm a PG newbie (and a relative SQL newbie, too), and not 
familiar with the DEFAULT ":: notation in your DDL.


Steve



There are 30k rows and the email column is not null... there is data 
in

there.
But for testing purpose I need to overwrite the email. So the customer
won't get an email from me while testing code.

The email could be replaced by: test@example.com

How can I do that?
Thanks!
Patrick





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] overwrite column data select - Postgres 9.2

> Hi guys,

> I've got a table with email column:
> email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,

> There are 30k rows and the email column is not null... there is data in there.
> But for testing purpose I need to overwrite the email. So the customer won't 
> get an email from me while testing code.

> The email could be replaced by: test@example.com

> How can I do that?
> Thanks!
> Patrick

In my mind, that's not a DB issue but an application issue since Pg doesn't 
send email (or I haven't seen that ability in it). So change the application 
that does the sending and comment out the "send" call. If you don't have the 
source for the app and can't do that, then disable the sending part at the OS 
level; a good sys-admin should have no trouble with that (turning off sendmail 
or whatever).

HTH,
Kevin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Patrick B]

2016-09-21 18:31 GMT+12:00 John R Pierce :

> On 9/20/2016 10:56 PM, Patrick B wrote:
>
> update table tablename set email = 'test@example.com'; ?
>>
>>
>>
>
> I can't overwrite the data into that column...
>
> I was hopping that in a SELECT I could replace the data from the email
> column to something else...
>
>
> select 'test@example.com' as email
>
> --
> john r pierce, recycling bits in santa cruz
>
>

That solved my problem! Thanks man!!

Patrick

Re: [GENERAL] overwrite column data select - Postgres 9.2

[John R Pierce]

On 9/20/2016 10:56 PM, Patrick B wrote:


update table tablename set email = 'test@example.com
'; ?




I can't overwrite the data into that column...

I was hopping that in a SELECT I could replace the data from the email 
column to something else...


select 'test@example.com' as email


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Rick Widmer]

I was hopping that in a SELECT I could replace the data from the email
column to something else...


maybe email = name_first + name_last? Is that possible?

I can't overwrite the data into that column... that has to be done by
the select (if possible)


SELECT , , ..., name_first || ' ' || name_last AS email
FROM ...

You can't use SELECT *, you must list everything but email.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] overwrite column data select - Postgres 9.2

[Patrick B]

2016-09-21 17:27 GMT+12:00 John R Pierce :

> On 9/20/2016 10:02 PM, Patrick B wrote:
>
> I've got a table with email column:
>
>> email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,
>
>
> There are 30k rows and the email column is not null... there is data in
> there.
> But for testing purpose I need to overwrite the email. So the customer
> won't get an email from me while testing code.
>
> The email could be replaced by: test@example.com
>
> How can I do that?
>
>
> update table tablename set email = 'test@example.com'; ?
>
>
>

I can't overwrite the data into that column...

I was hopping that in a SELECT I could replace the data from the email
column to something else...


maybe email = name_first + name_last? Is that possible?

I can't overwrite the data into that column... that has to be done by the
select (if possible)

Re: [GENERAL] overwrite column data select - Postgres 9.2

[John R Pierce]

On 9/20/2016 10:02 PM, Patrick B wrote:

I've got a table with email column:

email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,


There are 30k rows and the email column is not null... there is data 
in there.
But for testing purpose I need to overwrite the email. So the customer 
won't get an email from me while testing code.


The email could be replaced by: test@example.com 



How can I do that?


update table tablename set email = 'test@example.com'; ?





--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] overwrite column data select - Postgres 9.2

[amul sul]

​First try to get backup using pg_dump & replace all data in email column.

Restore table backup once you done with your testing.

Regards,
Amul.


On Wed, Sep 21, 2016 at 10:32 AM, Patrick B 
wrote:

> Hi guys,
>
> I've got a table with email column:
>
>> email CHARACTER VARYING(50) DEFAULT ''::CHARACTER VARYING NOT NULL,
>
>
> There are 30k rows and the email column is not null... there is data in
> there.
> But for testing purpose I need to overwrite the email. So the customer
> won't get an email from me while testing code.
>
> The email could be replaced by: test@example.com
>
> How can I do that?
> Thanks!
> Patrick
>