Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 12:32 PM, tango ward wrote: > > On Thu, May 31, 2018 at 12:18 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wednesday, May 30, 2018, tango ward wrote: >>> >>> Okay I will try it. >>>> >>

Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 12:06 PM, Rob Sargent wrote: > > > On May 30, 2018, at 9:57 PM, tango ward wrote: > > On Thu, May 31, 2018 at 11:53 AM, tango ward > wrote: > >> On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe >> wrote: >> >>> tango wa

Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 12:18 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 30, 2018, tango ward wrote: >> >> Okay I will try it. >>> >> >> When I tried it, I am getting an error: Invalid input syntax for UUID: >

Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 11:53 AM, tango ward wrote: > On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe > wrote: > >> tango ward wrote: >> > I found this: >> > >> > CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; >> > SELECT uu

Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe wrote: > tango ward wrote: > > I found this: > > > > CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; > > SELECT uuid_generate_v4(); > > > > My problem is I have a table like this: > > CR

Re: Insert UUID GEN 4 Value

On Thu, May 31, 2018 at 11:47 AM, tango ward wrote: > Hi, > > thanks. > > I found this: > > CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; > SELECT uuid_generate_v4(); > > My problem is I have a table like this: > CREATE TABLE enrollmentinfo ( >

Insert UUID GEN 4 Value

Hi, I would like to know how to properly use uuid_generate_v4 when inserting data into PostgreSQL table. When I run the command select * from pg_available_extensions; I can see this uuid-ossp | 1.0 | | generate universally unique identifiers (UUIDs) I am

Re: reduce number of multiple values to be inserted

On Wed, May 30, 2018 at 8:40 AM, Rob Sargent wrote: > > > > On 05/29/2018 06:36 PM, Adrian Klaver wrote: > >> On 05/29/2018 05:10 PM, tango ward wrote: >> >>> >>> Hi, >>> >> Not sure where you are pulling the data from and how it is o

Re: reduce number of multiple values to be inserted

On Wed, May 30, 2018 at 8:29 AM, Rob Sargent wrote: > > Is “current_timezone, current_timezone” just a typo? I think you need to > make the 117 data lines and load using \copy > > Sorry, yeah, it's current_timestamp.

Re: reduce number of multiple values to be inserted

On Wed, May 30, 2018 at 8:21 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 29, 2018, tango ward wrote: >> >> I will repeat the same process for 13 villages so that will be 117 of >> values. I would like to know if there's a way to red

reduce number of multiple values to be inserted

Hi, I am working on inserting multiple values for a table. I need to insert 3 values of data for each age of the students from the same village. It will be 3 different ages of student per village. My sample code: curr_pgsql.execute(''' INSERT INTO student (created, modified,

Re: case statement within insert

On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/25/2018 07:05 AM, Adrian Klaver wrote: > >> On 05/25/2018 06:52 AM, Adrian Klaver wrote: >> >>> On 05/25/2018 02:04 AM, tango ward wrote: >>> >>>> &

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:33 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > I advise you fiddle with it some more and see if you can stumble upon a > functioning solution. Maybe step away from the problem for a bit, get some > fresh air, maybe sleep on it. You've demostrated

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com> wrote: > >> >> >> curr.pgsql.execute(''' >> INSERT INTO my_table(name, age) >> SELECT %s, %s >

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/23/2018 05:11 PM, tango ward wrote: > >> Sorry, i forgot the values. >> >> curr.pgsql.execute(''' >> INSERT INTO my_table(name, age) >> SELECT name, age >>

Re: Insert data if it is not existing

Tried it, but it still I am not inserting data into the table. On Thu, May 24, 2018 at 8:14 AM, tango ward <tangowar...@gmail.com> wrote: > Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code > now. > > On Thu, May 24, 2018 at 8:04 AM, Adrian

Re: Insert data if it is not existing

Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code now. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/23/2018 04:58 PM, tango ward wrote: > >> Thanks masters for responding again. >> >> I've tried

Re: Insert data if it is not existing

Sorry I forgot to mention. The table that I am working on right now doesn't have any unique column. AFAIK, I can only use ON CONFLICT if there's an error for unique column. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/23/2018 04:58 PM, t

Re: Insert data if it is not existing

gave it a shot On Thu, May 24, 2018 at 8:04 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com> wrote: > >> Thanks masters for responding again. >> >> I've tried running the code: >> &g

Re: Insert data if it is not existing

<adrian.kla...@aklaver.com> wrote: > On 05/23/2018 10:00 AM, David G. Johnston wrote: > >> On Wednesday, May 23, 2018, tango ward <tangowar...@gmail.com > tangowar...@gmail.com>> wrote: >> >> I just want to ask if it's possible to insert data if it's not >

Re: Insert data if it is not existing

x/constraint: > > https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT > > > > Steve. > > > > > *From:* tango ward [mailto:tangowar...@gmail.com] > *Sent:* 23 May 2018 10:04 > *To:* pgsql-generallists.postgresql.org > *Subject:* Inser

Insert data if it is not existing

Hi, I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing. Any suggestion? Thanks, J

Re: Control PhoneNumber Via SQL

Noted thanks Sir. On Wed, May 16, 2018 at 11:55 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 15, 2018, tango ward <tangowar...@gmail.com> wrote: > >> Ah yeah, the concatenation. I was thinking of using "+" but I can't make >

Re: Control PhoneNumber Via SQL

. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 15, 2018, tango ward <tangowar...@gmail.com> wrote: > >> Sorry for bumping this email. >> >> I would just like to clarify regarding regexp_replace: >> >> WHEN mobilenumber ~'^9[

Re: Control PhoneNumber Via SQL

of source? It does what I want it to do but I am not sure if that's always the case if pattern param is empty. On Wed, May 16, 2018 at 9:04 AM, tango ward <tangowar...@gmail.com> wrote: > Okay, I figured it out. > > cur_t.execute(""" >

Re: Control PhoneNumber Via SQL

OM studeprofile ORDER BY lastname """) In my previous SELECT statement, I picked the mobilenumber before running a CASE statement to it instead of jumping directly to CASE statement after SELECT. On Wed, May 16, 2018 at 8:59 AM, tango ward <tangowar...@

Re: Control PhoneNumber Via SQL

Did the CASE Statement produce the other columns Sir? On Wed, May 16, 2018 at 8:53 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, May 15, 2018, tango ward <tangowar...@gmail.com> wrote: >> >> >> I can access the index 1 of the output

Re: Control PhoneNumber Via SQL

Hi All, Thanks for the suggestions. I really appreciate it specially the article of formatting international phone numbers. I also tried implementing the suggestion of Wolfgang: cur_t.execute(""" SELECT mobilenumber, CASE WHEN mobilenumber

Control PhoneNumber Via SQL

Hi, Sorry for asking question again. I would like to know if it is possible to control the phone number in SQL before inserting it to the destination DB? I have a model in Django: class BasePerson(TimeStampedModel): phone_number = PhoneNumberField(max_length=50, verbose_name=_(u'phone

Re: Query ID Values

AND department_id IN (SELECT id FROM profile_department WHERE school_id=1) """, [course, course]) On Tue, May 15, 2018 at 1:11 PM, tango ward <tangowar..

Re: Query ID Values

I thing its this: " Note cursor objects are iterable, so, instead of calling explicitly fetchone() in a loop, the object itself can be used: >>> cur.execute("SELECT * FROM test;")>>>

Re: Query ID Values

ame'], row['lastname'], course, x On Tue, May 15, 2018 at 12:12 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/14/2018 08:30 PM, tango ward wrote: > >> for row in cur_t: >> course = row['course'] >> cur_p.execute("""

Re: Query ID Values

e could suggest a reasonable query. > > > > Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone > > Original message > From: tango ward <tangowar...@gmail.com> > Date: 5/14/18 21:08 (GMT-05:00) > To: "pgsql-generallists.postgresql.org" <p

Query ID Values

Good Day, I need to run an SQL query and get a program_id and department_id of a specific course for each student. I am thinking of running an IF condition to check if the course name is in program and get it's ID but I don't know yet where to use the IF condition in the query. sample code: for

Re: ON CONFLICT DO UPDATE

Yes, that's what I figured out eventually. I thought, only the columns that I declared inside the ON CONFLICT() parenthesis can be called in SET. My bad. On Thu, May 10, 2018 at 5:57 PM, Alban Hertroys <haram...@gmail.com> wrote: > > > On 10 May 2018, at 7:13, tango ward <tan

Re: ON CONFLICT DO UPDATE

Okay, I think I manage to solve it by adding balance = excluded.balance inside the parenthesis of ON CONFLICT clause. On Thu, May 10, 2018 at 1:13 PM, tango ward <tangowar...@gmail.com> wrote: > Sorry Sir Adrian, updating the code. > > for row in cur_tdc: > print

Re: ON CONFLICT DO UPDATE

row['currentbalance'])) On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/09/2018 07:04 PM, tango ward wrote: > >> >> Hi, >> >> Sorry for asking question again. >> >> I would like to k

Re: ON CONFLICT DO UPDATE

Sorry, I modified the school_system_id in CONFLICT CLAUSE. On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/09/2018 07:04 PM, tango ward wrote: > >> >> Hi, >> >> Sorry for asking question again. >> >&

ON CONFLICT DO UPDATE

Hi, Sorry for asking question again. I would like to know if there's a workaround for this. I need to insert Student Balance data into a table. The source data have duplicate values for student_id, school_id and campus_name. My StudentBalance model in Djano have Class Meta of: class Meta:

Re: Concatenate 2 Column Values For One Column

...@aklaver.com> wrote: > On 05/08/2018 07:17 PM, tango ward wrote: > >> Hi, >> >> Sorry for asking question again. >> >> I am trying to concatenate the value of column firstname and lastname >> from source DB to name column of destination DB. >> >&g

Re: Concatenate 2 Column Values For One Column

Tue, May 8, 2018 at 7:17 PM, tango ward <tangowar...@gmail.com> wrote: >> >>> I am trying to concatenate the value of column firstname and lastname >>> from source DB to name column of destination DB. >>> >>> for row in cur_t

Concatenate 2 Column Values For One Column

Hi, Sorry for asking question again. I am trying to concatenate the value of column firstname and lastname from source DB to name column of destination DB. My code so far: cur_t.execute(""" SELECT firstname, lastname FROM authors; """) for row

Re: KeyError: self._index[x]

Thanks, now I understand. Thank you so much for being so helpful to a newbie same with Sir Adrian. On Tue, May 8, 2018 at 12:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, May 7, 2018, tango ward <tangowar...@gmail.com> wrote: > >> I di

Re: KeyError: self._index[x]

he columns will use the name of the function? On Tue, May 8, 2018 at 12:40 PM, tango ward <tangowar...@gmail.com> wrote: > Shall I loop using the 'translate' as key to my row? > > On Tue, May 8, 2018 at 12:10 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >&g

Re: KeyError: self._index[x]

Shall I loop using the 'translate' as key to my row? On Tue, May 8, 2018 at 12:10 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/07/2018 08:11 PM, tango ward wrote: > >> Sorry, accidentally pressed send. >> >> >> cur_p = conn_pr(cursor_factory=

Re: KeyError: self._index[x]

Sorry, accidentally pressed send. cur_p = conn_pr(cursor_factory=psycopg2.extras.DictCursor) cur_t = conn_t.cursor(cursor_factory=psycopg2.extras.DictCursor) cur_t.execute(""" SELECT TRANSLATE(snumber, ' ', '') FROM sprofile """) # This will result in KeyError

KeyError: self._index[x]

Good day, Apologies for asking again. I am trying to remove the whitespace on student number by using TRANSLATE() inside the execute() of psycopg2. Problem that I am getting is, even if I will just print the row with the column name(e.g. row['snumber']), I am getting KeyError error message. The

Re: psycopg2.DataError: invalid input syntax for integer: ""

at 9:49 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/07/2018 12:28 AM, tango ward wrote: > >> I think I've found the culprit of the problem. >> >> I have a field which is varchar from the source DB while on the >> destination DB its integer. &

Re: psycopg2.DataError: invalid input syntax for integer: ""

tabase table doesn't somehow have an integer column where you think its > text. > > > On Sunday, May 6, 2018, tango ward <tangowar...@gmail.com> wrote: > >> Yeah, the error doesn't really explain much. I have tried putting the >> string formatter in ' ', still n

psycopg2.DataError: invalid input syntax for integer: ""

Good day to all, I need advice on how to solve this problem. I am new to this kind of task. I want to insert data into my PostgreSQL DB. The field that I will store data is a textfield but I am getting an error invalid input syntax for integer: "" My code so far is this: cur_p.execute("""