On 22/05/2019 03:37, Justin Pryzby wrote:
On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:
On 5/21/19 6:34 PM, Justin Pryzby wrote:
Is it still impossible to distinguish whether a row was inserted vs updated ?
You will need to be more specific.
Sorry, I mean with UPSERT /
On Tue, May 21, 2019 at 7:24 PM Rich Shepard wrote:
> > From how you say it, I assume you have some data in your original
> > dumps which can relate boths, lets assume it's org_name, but may be an
> > org-code. If you do not have it it means you cannot match people to
> > orgs in your data, all
Jeremy:
On Tue, May 21, 2019 at 11:58 PM Jeremy Finzel wrote:
> Then take Francisco's suggestion, only use an md5 of the organization fields
> to create yourself a unique identifier. Then you can use ctid (unique
> internal identifier for each row) to join back. You use SQL like this:
On Wed, May 22, 2019 at 7:40 AM Rich Shepard
wrote:
> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > I'm curious, what org_id do you put (manually) to the people? you must
> > have some way to match it ( like, say, "I have an organization line,
> > followed by lines for people in this
Hi,
May be something like this could help
SELECT TestList.id
FROM ( SELECT * FROM ( VALUES( 1 ), (5), (12), (33), (55) ) t ) AS TestList( id
)
LEFT OUTER JOIN idList ON IdList.id = TEstList.id
WHERE IdList.Id IS NULL;
Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96
On Wed, 22 May 2019, Francisco Olarte wrote:
I'm curious, what org_id do you put (manually) to the people? you must
have some way to match it ( like, say, "I have an organization line,
followed by lines for people in this organization"
Francisco,
The first data entered was in small chunks so
On Wed, 22 May 2019, Adrian Klaver wrote:
So does the people data have an organization attribute?
Adrian,
Yes. It's the FK to the organization table.
If so why not just assign the org_id while cleaning up the data?
That's what I thought to do based on your suggestion yesterday. It would
Here is one without syntax error.
select generate_series(1,(select max(id) from theserecords))
EXCEPT
select id from theserecords;
Från: Gustavsson Mikael [mikael.gustavs...@smhi.se]
Skickat: den 22 maj 2019 15:51
Till: Durumdara; Postgres General
Ämne: SV: Which
>
> Each row in the source file (exported from the spreadsheet as .csv and
> renamed to .txt for processing in emacs and awk) is a mixture of attributes
Absolutely no need to rename the .csv for those tools.
> that belong in either or both of the organization and people tables in my
> database.
I have tried many ways to update a table :
UPDATE personnes T1 SET T1.nom_naiss=T1.nom FROM personnes T2,
personnes T3
WHERE ;
UPDATE personnes T1 SET T1.nom_naiss=T1.nom FROM personnes T1,
personnes T2, personnes T3
WHERE ..;
In my case , where clause is : WHERE
Hi,
You can use generate_series.
select generate_series(1,select max(id) from theserecords)
EXCEPT
select id from theserecords;
KR
Från: Durumdara [durumd...@gmail.com]
Skickat: den 22 maj 2019 15:43
Till: Postgres General
Ämne: Which records aren't in list? Use
On 5/22/19 3:19 PM, Rob Sargent wrote:
Don’t use the alias on the column(s) being set.
This passed the parser:
UPDATE personnes T1 SET nom_naiss=T1.nom FROM personnes T2, personnes T3
WHERE T1.id=T2.id_mere AND T2.id_pere=T3.id AND T1.nom != T3.nom;
Wow, fine ! you got it!
(I have no
On Wed, 22 May 2019, Jeremy Finzel wrote:
There's absolutely no need to use anything beyond SQL here, though you
could if you want to.
Jeremy,
This is a new experience for me so I didn't think of a SQL solution.
I really wonder how much we are just talking past each other simply because
we
On 5/22/19 7:38 AM, Rich Shepard wrote:
On Wed, 22 May 2019, Jeremy Finzel wrote:
There's absolutely no need to use anything beyond SQL here, though you
could if you want to.
Jeremy,
This is a new experience for me so I didn't think of a SQL solution.
I really wonder how much we are just
Rich:
On Wed, May 22, 2019 at 2:40 PM Rich Shepard wrote:
> > I'm curious, what org_id do you put (manually) to the people? you must
> > have some way to match it ( like, say, "I have an organization line,
> > followed by lines for people in this organization"
> The first data entered was in
Hi!
A very silly question. I have a limited list of identifiers.
I want to know which one IS NOT in a table.
The
select * from theserecords where id not in (1, 5, 12, 33, 55)
isn't listing missing records... because they are missing... :-)
For example, a pseudo:
idlist = (1, 5, 12, 33, 55)
On 5/21/19 7:37 PM, Justin Pryzby wrote:
On Tue, May 21, 2019 at 06:57:36PM -0700, Adrian Klaver wrote:
On 5/21/19 6:34 PM, Justin Pryzby wrote:
Is it still impossible to distinguish whether a row was inserted vs updated ?
You will need to be more specific.
Sorry, I mean with UPSERT /
On Wed, 22 May 2019, Jeremy Finzel wrote:
Are you saying your database already has an organizations table, and this
data file is appending to it with all-brand-new organizations and people?
Jeremy,
The database has both organizations and people tables (among others) which
are already
On Thu, May 23, 2019 at 9:18 AM Perumal Raj wrote:
> We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
> Both Source and Target versions of OS/DB are same . Also Configuration is
> Apple-Apple.
>
> But We started seeing lot of process name 'migration' at OS Level in new
On Wed, May 22, 2019 at 2:33 PM PegoraroF10 wrote:
> We forgot FOR EACH ROW/STATEMENT when created our trigger.
>
> On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why
> did
> it accepted our command to create that trigger ?
>
You only partially absorbed the syntax doc for
Thanks for the responses,
Adrian,
top -c not showing much , Process name itself [ Migration/n]
Thomas,
Thanks for hitting same page, yes i came through it , But was wondering any
one faced same issue in our community.
Regards,
Raju
On Wed, May 22, 2019 at 2:28 PM Thomas Munro wrote:
> On
On Wed, May 22, 2019 at 3:21 PM PegoraroF10 wrote:
> Try to create exactly what I sent you and you´ll see null values on that
> pk.
>
You should probably submit self-contained examples if you need the user to
do this.
> And that occurs just because that trigger was created without EACH
>
On 5/22/19 3:21 PM, PegoraroF10 wrote:
sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result > Try to create exactly what I sent you and you´ll see null values on
that pk.
Yes because FOR EACH STATEMENT may deal with multiple rows, so it
Hi,
I'm working on migrating postgresql 9.6 database from ubuntu 16.04 to
18.04. Please let me know if there are any document references and best
practices that can be followed.
Thanks,
venkatesh.
I´m not saying it should inspect function code, but I think it should deny
when I try to create a trigger missing a needed argument.
When I do ...
create table MyTable(integer);
gives me an "syntax error at end of input" because I forgot field name.
why when I do ...
create trigger MyTrigger
On 5/22/19 5:03 PM, a venkatesh wrote:
Hi,
I'm working on migrating postgresql 9.6 database from ubuntu 16.04 to
18.04. Please let me know if there are any document references and best
practices that can be followed.
Advice is going to depend on:
1) Size of Postgres cluster.
2) Method of
sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result.
Try to create exactly what I sent you and you´ll see null values on that pk.
And that occurs just because that trigger was created without EACH
ROW/STATEMENT.
So, my question is, PG
ok, you won. Sorry, I didn´t read that "If neither is specified, FOR EACH
STATEMENT is the default"
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Rich:
On Wed, May 22, 2019 at 6:07 PM Rich Shepard wrote:
> On Wed, 22 May 2019, Francisco Olarte wrote:
> > You are not reading what we write to you. Note YOU AND ONLY YOU are the
> > one speaking of PK. We are speaking of "unique identifier" ( that would
> > be, IIRC, "candidate keys", you can
User writes:
> CREATE TABLE public.test1 (
> x1 integer NOT NULL,
> x2 integer NOT NULL,
> CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
> ) PARTITION BY RANGE (x2);
> This query works in 11.1 but fails in 11.3 with messages:
> ERROR: insufficient columns in PRIMARY KEY
Rich:
On Wed, May 22, 2019 at 4:38 PM Rich Shepard wrote:
> Not necessary; see below. Also, these data come from a regulator and
> provided as an Excel spreadsheet. If they were extracted from a database
> then that was very poorly designed because there's no consistency in how
> fields/columns
On Wed, 22 May 2019, Francisco Olarte wrote:
You are not reading what we write to you. Note YOU AND ONLY YOU are the
one speaking of PK. We are speaking of "unique identifier" ( that would
be, IIRC, "candidate keys", you can peek any as your PK, or even introduce
a new synthetic one with a
On Wed, May 22, 2019 at 11:07 AM Rich Shepard
wrote:
> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > You are not reading what we write to you. Note YOU AND ONLY YOU are the
> > one speaking of PK. We are speaking of "unique identifier" ( that would
> > be, IIRC, "candidate keys", you can
CREATE TABLE public.test1 (
x1 integer NOT NULL,
x2 integer NOT NULL,
CONSTRAINT test1_pkey PRIMARY KEY (x1) INCLUDE(x2)
) PARTITION BY RANGE (x2);
This query works in 11.1 but fails in 11.3 with messages:
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL:
On Wed, 22 May 2019, Francisco Olarte wrote:
Also, when I speak of "unique identifier" I'm not speaking of the one if
your FINAL tables, I assume you would have at least the *_id field as
PKEY, so nothing else needed, but the one in your SOURCE data set (it can
be anything, like the row number
Hi,
On 2019-05-22 10:08:44 +0200, Luca Ferrari wrote:
> I've got a table named "root", partitioned on a date field into years
> (e.g., "2018") and into months like "y2018m11" using range
> partitioning on PostgreSQL 11.2.
> Tuples are inserted into root with an INSERT...SELECT. I have
> performed
On 5/22/19 4:18 PM, Perumal Raj wrote:
Hi All,
We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration is
Apple-Apple.
But We started seeing lot of process name 'migration' at OS Level in new
server
Hi all
I am wondering if table inheritance is possible over the boundaries of
schemata and different owners.
I have database act with schemata il and scd. When I issue as IL
create table IL.INHERITANCE_TEST() inherits (SCD.TEMPL_BK);
I get
[Code: 0, SQL State: 42501] ERROR: permission
Justin Pryzby wrote:
> Is it still impossible to distinguish whether a row was inserted vs updated ?
>
> The latest I can see is here:
> https://wiki.postgresql.org/wiki/UPSERT#RETURNING_behavior
>
> ..but I'm hopeful that the 4 year old wiki page is out of date.
Maybe this answer can help you:
On 5/22/19 10:53 AM, Rich Shepard wrote:
On Wed, 22 May 2019, Francisco Olarte wrote:
Also, when I speak of "unique identifier" I'm not speaking of the one if
your FINAL tables, I assume you would have at least the *_id field as
PKEY, so nothing else needed, but the one in your SOURCE data set
>
> A sample of the data you are cleaning up.
>
> I think what people are trying to wrap there head around is how 800
> lines in the file is being split into two subsets: the organization data
> and the people data. In particular how that is being done to preserve
> the relationship between
On Wed, May 22, 2019 at 12:53 PM Rich Shepard
wrote:
> On Wed, 22 May 2019, Francisco Olarte wrote:
>
> > Also, when I speak of "unique identifier" I'm not speaking of the one if
> > your FINAL tables, I assume you would have at least the *_id field as
> > PKEY, so nothing else needed, but the
On Wed, 22 May 2019, Adrian Klaver wrote:
A sample of the data you are cleaning up.
Adrian, et al.:
I have it working properly now. Both org_id and person_id numbers are
prepended to each row in the appropriate table and they are unique because
each series begins one greater than the
We created a usual trigger which seemed to be not firing or not working
properly. Then we put some raise notices on it and saw that it was returning
Null values. But why, it´s after insert and is a primary key, cannot be
null.
create function MyFunction() returns trigger as $$
begin
raise '%',
On 5/22/19 2:18 PM, Perumal Raj wrote:
Hi All,
We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration
is Apple-Apple.
Other programs running that connect to the db server?
More below.
But We started
Hi All,
We have recently migrated postgres DB to out of of server ( Centos 6.9 ) .
Both Source and Target versions of OS/DB are same . Also Configuration is
Apple-Apple.
But We started seeing lot of process name 'migration' at OS Level in new
server which is triggering Load average most of the
On 5/22/19 2:33 PM, PegoraroF10 wrote:
We created a usual trigger which seemed to be not firing or not working
properly. Then we put some raise notices on it and saw that it was returning
Null values. But why, it´s after insert and is a primary key, cannot be
null.
47 matches
Mail list logo
