Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread John R Pierce 

On 9/21/2016 8:37 PM, Patrick B wrote:

I'm using postgres 9.2 and got the following column:

start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL


SELECT start FROM test1;


2015-12-18 02:40:00

 I need to split that date into two columns on my select:

2015-12-18 = date column
02:40:00 = time column


select start::date as date_column start::time as time_column from 


BUT, you really gotta watch out for type conversions around timestamp 
WITHOUT time zone, as most conversions expect TIMESTAMP WITH TIME ZONE, 
which internally are stored in UTC but are converted from/to 
CLIENT_TIMEZONE on input output.  so timestamp without time zone can get 
converted to timestamp with time zone, then converted to date or time, 
and get all messed up.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread Adrian Klaver 

On 09/21/2016 08:37 PM, Patrick B wrote:

Hi guys,

I'm using postgres 9.2 and got the following column:

start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL


SELECT start FROM test1;


2015-12-18 02:40:00

 I need to split that date into two columns on my select:

2015-12-18 = date column
02:40:00 = time column

How can I do that without modifying any column/data?
Maybe in a select?


test=# select
'2015-12-18 02:40:00'::date,
'2015-12-18 02:40:00'::timestamp - '2015-12-18 02:40:00'::date AS time_val;

date| time_val
+--
 2015-12-18 | 02:40:00



Thanks!
Patrick



--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread Venkata B Nagothi 
On Thu, Sep 22, 2016 at 1:37 PM, Patrick B  wrote:

> Hi guys,
>
> I'm using postgres 9.2 and got the following column:
>
> start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL
>
>
> SELECT start FROM test1;
>
>
> 2015-12-18 02:40:00
>
>  I need to split that date into two columns on my select:
>
> 2015-12-18 = date column
> 02:40:00 = time column
>
> How can I do that without modifying any column/data?
> Maybe in a select?
>

postgres=# select to_char(start, 'DD-MM-') from test;
  to_char

 18-12-2015


Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread Ian Barwick 
On 22-09-2016 12:37, Patrick B wrote:
> Hi guys,
> 
> I'm using postgres 9.2 and got the following column:
> 
> start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL
> 
> 
> SELECT start FROM test1;
> 
> 
> 2015-12-18 02:40:00
> 
>  I need to split that date into two columns on my select:
> 
> 2015-12-18 = date column
> 02:40:00 = time column
> 
> How can I do that without modifying any column/data?
> Maybe in a select?

TO_CHAR() is your friend:

  https://www.postgresql.org/docs/current/static/functions-formatting.html

Regards

Ian Barwick


-- 
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services


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

[GENERAL] Extract date from a TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL column

2016-09-21 Thread Patrick B 
Hi guys,

I'm using postgres 9.2 and got the following column:

start TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL


SELECT start FROM test1;


2015-12-18 02:40:00

 I need to split that date into two columns on my select:

2015-12-18 = date column
02:40:00 = time column

How can I do that without modifying any column/data?
Maybe in a select?

Thanks!
Patrick

Re: [GENERAL] Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

2016-09-21 Thread Jeff Janes 
On Wed, Sep 21, 2016 at 2:18 PM, pinker  wrote:

> Jeff Janes wrote
> > Try swapping the order of the columns in the exclude constraint.  You
> want
> > the more selective criterion to appear first in the index/constraint.
> > Presumably "key with =" is the most selective, especially if many of your
> > periods are unbounded.
>
> I would not be so sure with that:
>

As a rule, I generally don't spout random nonsense. Or at least, not
without including a disclaimer.  I didn't test it on the OPs exact case,
because he has need blessed us with his data or his scripts.  But I have
tested it on other data, and it does work.


> http://use-the-index-luke.com/sql/myth-directory/most-selective-first


I don't see how anything there applies to GiST indexes.  Indeed, there
doesn't seem to be much there worth reading at all.  The only thing vaguely
informative, other than trivia about other RDBMS, is "It’s useless to have
the most selective column of the index on the left if very few queries
filter on it", which is rather obvious, but also obviously does not apply
to this case.

Cheers,

Jeff

Re: [GENERAL] Unstable C Function

2016-09-21 Thread Tom Lane 
Ian Campbell  writes:
> The function works fine on first call, sometimes more, then either resets
> the connection or throws this on any further calls:
> ERROR: cache lookup failed for type 0 SQL state: XX000

I think the core problem here is that you're dealing with
pass-by-reference results from the SPI_execute() --- specifically,
the int4[] "vals" values --- as if they were pass-by-value.  You're
just saving the Datums, which are only pointers, and expecting what
they point to to still be good when you get around to doing
heap_form_tuple with them.  But in reality they stopped being good
the moment you did SPI_finish().

The failures would be a lot less intermittent if you were testing in
a debug build (with CLOBBER_FREED_MEMORY defined).

The two basic approaches you could take that would work reliably are

1. Copy all the int4[] values into the multi_call_memory_ctx before
doing SPI_finish.

2. Instead of using multi-call mode, form all the result tuples during
a single call and return them in a tuplestore, so that all the work
is done before you call SPI_finish.  You wouldn't really need the FIFO
data structure if you did it that way.

There are some other things I could criticize here, like labeling the
function IMMUTABLE when its results depend on table contents, but
they probably aren't causing your crashes.

regards, tom lane


-- 
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] json select question

2016-09-21 Thread John R Pierce 

On 9/21/2016 4:54 PM, CS DBA wrote:

How can i pull a unique list of all json column names? such as book_name, 
catalog_name, etc


try json_object_keys() ... 
https://www.postgresql.org/docs/current/static/functions-json.html


but this will only pull the top level keys, like (from the first row of 
your example) , book_name, author... it won't pull out any second level 
fields like first_name, last_name.


you'll likely need to run this on all rows, and do a group by to get 
what you need.


thats a pretty messy way of storing data, btw, and not very relational, 
having a key name indicate a record type, and tossing all the record 
types into the same table.



--
john r pierce, recycling bits in santa cruz

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

2016-09-21 Thread 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
''

[GENERAL] json select question

2016-09-21 Thread CS DBA 

All;


I'm working with a client running postgres 9.2, they have a table with a 
"json_data_string" column of type json



the data looks something like this with lots of rows for each (i.e. lots 
of json_data_string->book_name rows, lots of 
json_data_string->catalog_name rows, etc:


|'{ "book_name": "Book the Fourth", "author": { "first_name": "Charles", 
"last_name": "Davis" } }' ||'{ "catalog_name": "Catalog the Fourth", "author": { "first_name": 
"Charles", "last_name": "Davis" } }'|
|'{ "magazine_name": "mag4", "author": { "first_name": "Charles", 
"last_name": "Davis" } }' |||'{ "category_name": "region", "author": { "first_name": "Charles", 
"last_name": "Davis" } }'||



How can i pull a unique list of all json column names? such as book_name, 
catalog_name, etc

Thanks in advance

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

2016-09-21 Thread 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

2016-09-21 Thread 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

[GENERAL] Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

2016-09-21 Thread pinker 
Jeff Janes wrote
> Try swapping the order of the columns in the exclude constraint.  You want
> the more selective criterion to appear first in the index/constraint.
> Presumably "key with =" is the most selective, especially if many of your
> periods are unbounded.

I would not be so sure with that:
http://use-the-index-luke.com/sql/myth-directory/most-selective-first




--
View this message in context: 
http://postgresql.nabble.com/performance-problems-with-bulk-inserts-updates-on-tsrange-with-gist-based-exclude-constrains-tp5921498p5922219.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] PgSQL versions supported on ubuntu 16 and debian 8

2016-09-21 Thread Alvaro Herrera 
John R Pierce wrote:
> On 9/19/2016 4:18 AM, MEERA wrote:
> >
> >Could someone please provide us this information?
> 
> was answered nearly a week ago.

Hmm, yeah, but you didn't CC the OP, and she is not subscribed.

Meera: you can see the answer here:
https://www.postgresql.org/message-id/flat/CABZh%3DWaeB5%2BMBnoaJ6QB2dy%3D1G3DuO368VFxBoSiWXtiZpdcHw%40mail.gmail.com

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] generate CSV with Copy+Quotes - Postgres 9.2

2016-09-21 Thread Patrick B 
>
>
>>
>
> Perhaps you mean you want to quote of all strings? For that you use FORCE
> QUOTE.
> eg:
> COPY (SELECT
> name_first
> name_last,
> email,
> company
> FROM
> clients
> )
> TO '/var/lib/pgsql/test1.csv' DELIMITER ','
>  csv HEADER QUOTE '"';
>  FORCE QUOTE name_first, name_last, email, company;
>
> Please RTFM
> *https://www.postgresql.org/docs/9.4/static/sql-copy.html
> *
> --
>

>


Thanks! FORCE QUOTE was exactly what I needed.

Patrick.

Re: [GENERAL] PgSQL versions supported on ubuntu 16 and debian 8

2016-09-21 Thread John R Pierce 

On 9/19/2016 4:18 AM, MEERA wrote:


Could someone please provide us this information?


was answered nearly a week ago.

any current PG version (thats 9.1.latest to 9.5.latest) can be run on 
most any version of linux.Do note, 9.1 will be phased out soon, 
while 9.6 is nearly ready for release.



--
john r pierce, recycling bits in santa cruz



--
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

2016-09-21 Thread 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

[GENERAL] postgres failed to start from services manager on windows 2008 r2

2016-09-21 Thread PHANIKUMAR G 
hi,

   We are able to start the postgres from command line without any issue,
if postgres is registered as windows service and tried to start it, we are
facing an issue .

*Problem:*

We have registered postgres(version 9.3.4) as windows service on windows
2008 R2 and registration got succeeded.

we are performing below 2 commands to register postgres as windows service

*pg_ctl.exe register -N "prostgresService" -D
"fullpath_to_data_directory" -W*

*>sc description prostgresService "prostgresService Applicatio*
*n Server Database Service"*

when we try to start the service from services manager(services.msc) it is
failing and popping up below message

*"The prostgres service on Local Computer started and then stopped. Some
services stop automatically if they are not in use by other services or
programs "*

postgres does not even create log and its directory pg_log in pgsql\data
directory.

windows event log has a message

*The description for Event ID 0 from source PostgreSQL cannot be found.
Either the component that raises this event is not installed on your local
computer or the installation is corrupted. You can install or repair the
component on the local computer.*

*If the event originated on another computer, the display information had
to be saved with the event.*

*The following information was included with the event: *

*Timed out waiting for server startup*


could you please help us how to start, we are clueless since postgres is
not creating log except windows event log.


thanks in advance
Phani Kumar

[GENERAL] Unstable C Function

2016-09-21 Thread Ian Campbell 
I'm running PG 9.5 on Win 10 64-bit. I'm compiling C under VS 2016.

I am forming a function that will evolve into a somewhat complex beast. To
test out my initial efforts, the function accepts an array of int4 (this
works fine and the code for processing it is not shown here). The function
then grabs a few rows from a table, pushes the values into a FIFO, then
pulls them out and renders the results. This approach is strategic to how
the function will operate when complete.

The function works fine on first call, sometimes more, then either resets
the connection or throws this on any further calls:

ERROR: cache lookup failed for type 0 SQL state: XX000

I noticed that removing references to the FIFO improves stability, but
doesn't solve it. Here is my code as lean as I can get it for question
purposes:

PG:

CREATE TABLE md_key(
  idserial NOT NULL PRIMARY KEY,
  pid   int4 NOT NULL,
  key   integer NOT NULL,
  vals  int4[]);…

CREATE OR REPLACE FUNCTION md_key_query(int4[])
  RETURNS TABLE (
id int4,
vals int4[]) AS E'\RoctPG', --abreviated for question
'md_key_query'
  LANGUAGE c IMMUTABLE STRICT;…
select * from md_key_query(array[1,2,3,4]::int4[])

C:

PG_FUNCTION_INFO_V1(md_key_query);

typedef struct
{
Datum  id;
Datum  vals;
} MdKeyNode;

typedef struct fifoAry
{
MdKeyNode   nodes[32];
struct fifoAry *next;
int32   readpos;
int32   writepos;
} FifoAry;

typedef struct
{
FifoAry*fifo;
FifoAry*tail;
FifoAry*head;
uint32  nodescount;
Datum  *retvals[2];
bool   *retnulls[2];
} CtxArgs;

inline void push(CtxArgs *args, Datum id, Datum vals)
{
if (args->head->writepos == 32)
args->head = args->head->next = (FifoAry*)palloc0(sizeof(FifoAry));

MdKeyNode  *node = &(args->head->nodes[args->head->writepos++]);
node->id = id;
node->vals = vals;
args->nodescount++;
}

inline MdKeyNode* pop(CtxArgs *args){//  if (!args->nodescount)//
return NULL;
if (args->tail->readpos == 32)
args->tail = args->tail->next;

args->nodescount--;

return &(args->tail->nodes[args->tail->readpos++]);}
// use STRICT in the caller wrapper to ensure a null isn't passed in
PGMODULEEXPORT Datum md_key_query(PG_FUNCTION_ARGS){
uint32  i;
FuncCallContext*funcctx;
HeapTuple   tuple;
MdKeyNode  *node;
CtxArgs*args;

if (SRF_IS_FIRSTCALL())
{
funcctx = SRF_FIRSTCALL_INIT();

MemoryContext   oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
ArrayType  *a = PG_GETARG_ARRAYTYPE_P(0);
Datum  *in_datums;
bool   *in_nulls;
boolfieldNull;
SPITupleTable  *tuptable = SPI_tuptable;
int32   ret;
uint32  proc;

if (get_call_result_type(fcinfo, NULL, >tuple_desc)
!= TYPEFUNC_COMPOSITE)
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("function returning record called in context that cannot accept
type record")));

deconstruct_array(a, INT4OID, 4, true, 'i', _datums,
_nulls, );

if (!ret)
PG_RETURN_NULL();

(SPI_connect();

// initialize and set the cross-call structure
funcctx->user_fctx = args = (CtxArgs*)palloc0(sizeof(CtxArgs));
args->fifo = args->tail = args->head =
(FifoAry*)palloc0(sizeof(FifoAry));
args->retvals = (Datum*)palloc(sizeof(Datum) * 2);
args->retnulls = (bool*)palloc0(sizeof(bool) * 2);

BlessTupleDesc(funcctx->tuple_desc);
// do some work here

// this is simply a test to see if this function is behaving as expected
ret = SPI_execute("select id, vals from public.md_key where
vals is not null limit 64", true, 0);

if (ret <= 0)
ereport(ERROR, (errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),
errmsg("could not execute SQL")));

proc = SPI_processed;

if (proc > 0)
{
TupleDesc   tupdesc = SPI_tuptable->tupdesc;
SPITupleTable  *tuptable = SPI_tuptable;

for (i = 0; i < proc; i++)
{
tuple = tuptable->vals[i];
push(args, SPI_getbinval(tuple, tupdesc, 1,
), SPI_getbinval(tuple, tupdesc, 2, ));
}
}

SPI_finish();
MemoryContextSwitchTo(oldcontext);
}

funcctx = SRF_PERCALL_SETUP();
args = funcctx->user_fctx;

if (args->nodescount > 0)
{
node = pop(args);
args->retvals[0] = node->id;
args->retvals[1] = node->vals;
tuple = heap_form_tuple(funcctx->tuple_desc, args->retvals,
args->retnulls);
SRF_RETURN_NEXT(funcctx,

Re: [GENERAL] PgSQL versions supported on ubuntu 16 and debian 8

2016-09-21 Thread MEERA 
Hi,

Could someone please provide us this information?

On Thu, Sep 15, 2016 at 1:10 PM, MEERA  wrote:

> Hi,
>
> Could someone share below information?
>
> - PgSQL versions supported on ubuntu 16
> - PgSQL versions supported on debian 8
>
>
> --
> thanks and regards,
> Meera R Nair
>



-- 
thanks and regards,
Meera R Nair

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

2016-09-21 Thread 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] generate CSV with Copy+Quotes - Postgres 9.2

2016-09-21 Thread Melvin Davidson 
On Wed, Sep 21, 2016 at 9:33 AM, Adrian Klaver 
wrote:

> On 09/21/2016 02:23 AM, Patrick B wrote:
>
>> Hi guys,
>>
>> I'm exporting some data for testing purpose.
>>
>> COPY (SELECT
>> name_first
>> name_last,
>> email,
>> company
>> FROM
>> clients
>> )
>> TO '/var/lib/pgsql/test1.csv' DELIMITER ',' csv HEADER QUOTE '"';
>>
>>
>>
>>  cat /var/lib/pgsql/test1.csv
>>
>> "","",hiddenem...@hotmail.com
>> ,"",John Wayne
>>
>>
> In your COPY you have fields and in the CSV file you have five.
> Are you looking at the correct file?
>
>
>> See how the email and the company aren't into two "?
>>
>
> Have no idea what you are trying to say.
>
> hiddenem...@hotmail.com looks like a correct email to me.
>
> Maybe if you show what the data is in the table?
>
>
>> *What I need is:*
>>
>> "","","hiddenem...@hotmail.com
>> ","","John Wayne"
>>
>>
>> I thought that by putting QUOTE the COPY would do that?
>>
>> Is there any way I can put the " into those fields?
>>
>> Cheers
>>
>> Patrick
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Perhaps you mean you want to quote of all strings? For that you use FORCE
QUOTE.
eg:
COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ','
 csv HEADER QUOTE '"';
 FORCE QUOTE name_first, name_last, email, company;

Please RTFM
*https://www.postgresql.org/docs/9.4/static/sql-copy.html
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] generate CSV with Copy+Quotes - Postgres 9.2

2016-09-21 Thread Adrian Klaver 

On 09/21/2016 02:23 AM, Patrick B wrote:

Hi guys,

I'm exporting some data for testing purpose.

COPY (SELECT
name_first
name_last,
email,
company
FROM
clients
)
TO '/var/lib/pgsql/test1.csv' DELIMITER ',' csv HEADER QUOTE '"';



 cat /var/lib/pgsql/test1.csv

"","",hiddenem...@hotmail.com
,"",John Wayne



In your COPY you have fields and in the CSV file you have five.
Are you looking at the correct file?



See how the email and the company aren't into two "?


Have no idea what you are trying to say.

hiddenem...@hotmail.com looks like a correct email to me.

Maybe if you show what the data is in the table?



*What I need is:*

"","","hiddenem...@hotmail.com
","","John Wayne"


I thought that by putting QUOTE the COPY would do that?

Is there any way I can put the " into those fields?

Cheers

Patrick




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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

2016-09-21 Thread 
> 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

[GENERAL] generate CSV with Copy+Quotes - Postgres 9.2

2016-09-21 Thread Patrick B 
Hi guys,

I'm exporting some data for testing purpose.

COPY (SELECT
> name_first
> name_last,
> email,
> company
> FROM
> clients
> )
> TO '/var/lib/pgsql/test1.csv' DELIMITER ',' csv HEADER QUOTE '"';



 cat /var/lib/pgsql/test1.csv

"","",hiddenem...@hotmail.com,"",John Wayne


See how the email and the company aren't into two "?

*What I need is:*

> "","","hiddenem...@hotmail.com","","John Wayne"


I thought that by putting QUOTE the COPY would do that?

Is there any way I can put the " into those fields?

Cheers

Patrick

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

2016-09-21 Thread 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

2016-09-21 Thread 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

2016-09-21 Thread 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