Re: Format an Update with calculation
út 18. 12. 2018 v 8:15 odesílatel Bret Stern < bret_st...@machinemanagement.com> napsal: > My statement below updates the pricing no problem, but I want it to be > formatted with 2 dec points eg (43.23). > > Started playing with to_numeric but can't figure it out. Lots of examples > with to_char in the > manual, but still searching for answer. > > Can it be done? > postgres=# select random(); ┌───┐ │ random │ ╞═══╡ │ 0.261391982901841 │ └───┘ (1 row) postgres=# select random()::numeric(7,2); ┌┐ │ random │ ╞╡ │ 0.67 │ └┘ (1 row) Regards Pavel > I want suggested_retail_price to be formatted to 2 decimal points > > UPDATE im_ci_item_transfer >SET suggested_retail_price=(suggested_retail_price + > (suggested_retail_price * .13)) > WHERE item_code='0025881P2'; > > Feeling lazy, sorry guys >
Format an Update with calculation
My statement below updates the pricing no problem, but I want it to be formatted with 2 dec points eg (43.23). Started playing with to_numeric but can't figure it out. Lots of examples with to_char in the manual, but still searching for answer. Can it be done? I want suggested_retail_price to be formatted to 2 decimal points UPDATE im_ci_item_transfer SET suggested_retail_price=(suggested_retail_price + (suggested_retail_price * .13)) WHERE item_code='0025881P2'; Feeling lazy, sorry guys
Re: IF NOT EXIST
Hi út 18. 12. 2018 v 7:11 odesílatel Igor Korot napsal: > Hi, ALL, > I have a following statement: > > IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS > ns ) CREATE FUNCTION(); > > Unfortunately trying to execute it thru the ODBC interface with: > > ret = SQLExecDirect( m_hstmt, query, SQL_NTS ); > > gives syntax error near IF. > > What is the proper way to do that? > IF is only plpgsql statement - so you cannot to call it from SQL environment. CREATE FUNCTION is not expensive command, and you can replace existing function by statement CREATE OR REPLACE FUNCTION ... This is usual solution. You cannot to use in SQL IF statement ever in PostgreSQL - This T-SQL syntax is not supported. But lot of DDL statements has integrated IF DROP TABLE IF EXISTS ... CREATE TABLE IF NOT EXISTS ... CREATE OR REPLACE FUNCTION ... second solution is using SQL command DO - inside is plpgsql code, and you can use conditional statement like IF, or other. regards Pavel > > Thank you. > >
IF NOT EXIST
Hi, ALL, I have a following statement: IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS ns ) CREATE FUNCTION(); Unfortunately trying to execute it thru the ODBC interface with: ret = SQLExecDirect( m_hstmt, query, SQL_NTS ); gives syntax error near IF. What is the proper way to do that? Thank you.
Re: Creating 2D arrays for pg_copy_from, reading tab-delimited text file that contains comma and double quotes
Hello Rob,
Thank you for your comments.
As I wrote just a few minutes ago, using a tab delimiter inside the implode as
suggested by Daniel (and specifying the read length ("0") for the fgetcsv has
solved this problem.
At one point, I had tried to use the last parameter - enclosure character - in
your reply, but I got an error, something like,
Warning: pg_copy_from(): Copy command failed: ERROR: missing data for column
"item1" CONTEXT: COPY test_table, line 1: "1 Denny's orange juice 1,500
yen" ...
Then Daniel's reply came and I tried that, still made errors here and there
(removed the read length- but forgot to put it back in subsequent
tests), and was looking for ways to strip off the leading and trailing
double quotes while leaving the comma intact.
FF now..
Anyway, crisis averted this time!
Thank you again for taking time to share your thoughts.
Cheers!
- Original Message -
> From: rob stone
> To: s4...@yahoo.co.jp; "pgsql-general@lists.postgresql.org"
>
> Cc:
> Date: 2018/12/17, Mon 23:38
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text
> file that contains comma and double quotes
>
> Hello,
>
> On Mon, 2018-12-17 at 21:34 +0900, s4...@yahoo.co.jp wrote:
>> Hello Good People of the Forum!
>>
>> I am trying to insert some data into a PostgreSQL database using PHP
>> and struggling to create an array so that pg_copy_from function will
>> accept and process the data.
>>
>> I can insert data but not the way I want- my data this case contains
>> comma, space, double quotes and unpaired double quote.
>> I need to use Excel to create the data and save it as tab delimited
>> text file, and then convert encoding to UTF-8 (from Excel's ANSI)
>> before passing it to the PHP.
>>
>> Here is my dummy data in Excel:
>> rec_no item1 item2 item3 item4 item5
>> 1 Denny's orange juice 1,500 yen "Dear John"
> 32" TV
>>
>> Explanation: the first row is header.
>> The second row is data for each column.
>> I tried to create variation like apostrophe, space between words,
>> comma, double quotes and unpaired double quote (32" TV).
>>
>> When I save that Excel as a tab delimited text file, I get this:
>> rec_no item1 item2 item3 item4 item5
>> 1 Denny's orange juice "1,500 yen"
> """Dear John"""
>> "32"" TV"
>> (As seen when I opened that file with Notepad)
>>
>> Because my data also contains non-ascii, I saved the file with UTF-8
>> encoding using the Notepad.
>>
>> Then I created a two-dimensional array with PHP:
>>
>> 1．$file = 'test.txt'; //tab delimited file
>> 2．$fileRead = fopen($file, 'r');
>>
>> 3．$row = 1;
>> 4．$twoDarray = array();
>> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {
> //0 means
>> I can read row whatever its length
>> 6． if($row == 1){ $row++; continue; } //skip header
>> 7． $line = implode(" ",$line). "\n";
>> 8． $twoDarray[] = $line;
>> 9．}
>> 10．fclose($fileRead);
>>
>> Then I passed that twoDarray to pg_copy_from.
>>
>> $con=pg_connect("host=$host dbname=$dbname port=5432 user=$user
>> password=$password");
>>
>> 11．if (!$con) {
>> 12． die("Couldn't open..\n");
>> 13．}
>>
>> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
>> 15． print "OK!";
>> 16．}
>> 17．else{
>> 18． print "Not OK.";
>> 19．}
>>
>> When I run the program, I have this error:
>> Warning: pg_copy_from(): Copy command failed:
>> ERROR: value too long for type character varying(32) CONTEXT: COPY
>> test_table, line 1, column rec_no: "1 Denny's orange juice 1,500
> yen
>> "Dear John" 32" TV" in testProgram.php line xx.
>>
>> My table definition is:
>> CREATE TABLE test_table (
>> rec_no VARCHAR(32) PRIMARY KEY NOT NULL,
>> item1 VARCHAR(255),..item2 .. until item5.);
>>
>> Obviously, my program thinks everything in the data row is for the
>> first field.
>> No, no.
>>
>> How to make it think that
>> 1 is for the 'rec_no' field,
>> Denny's is for the 'item1' field,
>> orange juice is for the 'item2' field,
>> 1,500 yen is for the 'item3' field,
>> "Dear John" is for the 'item4' field and
>> 32" TV is for the 'item5' field?
>>
>> When I tried removing '0' from line 5, that is,
>> while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {
> //without read
>> length
>>
>> I can see data written in the database, but with some extra double
>> quotes and a missing comma!
>> That is data was saved as
>> Denny's, orange juice, "1 500 yen",
> """Dear John""", and
>> "32"" TV"
>> into the respective fields.
>> I cannot have those extra double quotes, and I cannot have missing
>> comma in my data.
>
>
>
> fgetscsv returns an array from reading a record from a text file.
> So $line = fgetcsv($fileRead, 0, "\t", '"') would
> use tab as the
> delimiter and remove any enclosure character equal to ".
>
> I don't use Excel. With Libreoffice you can
Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
>and now I can see my data saved in the database without those extra double
>quotes.
.. and with my comma intact as well.
- Original Message -
> From: "s4...@yahoo.co.jp"
> To: Daniel Verite
> Cc: "pgsql-general@lists.postgresql.org"
> Date: 2018/12/18, Tue 14:15
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text
> file that contains comma and double quotes
>
> Hello Daniel!
>
> This afternoon, I tried it again, and yes, your suggestion that I use a tab
> delimited symbol ("\t") instead of a space for the implode works
> flawlessly.
>
> Yesterday, I reported I got "Warning: pg_copy_from(): Copy command failed:
> ERROR: extra data after last expected..".
> That was because while reading file, I had used this:
> while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {
>
>
> Today, I used this:
> while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {
>
>
> .. and now I can see my data saved in the database without those extra double
> quotes.
>
> It made my day!
>
> Thank you.
>
>
>
>
>
> - Original Message -
>> From: Daniel Verite
>> To: s4...@yahoo.co.jp
>> Cc: pgsql-general@lists.postgresql.org
>> Date: 2018/12/18, Tue 00:35
>> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text
> file that contains comma and double quotes
>>
>> wrote:
>>
>>> When I save that Excel as a tab delimited text file, I get
> this:rec_no
>>> item1 item2 item3 item4 item5
>>> 1 Denny's orange juice "1,500 yen"
>> """Dear John""" "32""
>>> TV"(As seen when I opened that file with Notepad)
>>
>> This looks good. Fields are properly enclosed and double quotes
>> in contents are doubled, as expected in CSV.
>>
>>> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !==
> FALSE) {
>> //0 means I can
>>> read row whatever its length
>>> 6． if($row == 1){ $row++; continue; } //skip header
>>> 7． $line = implode(" ",$line). "\n";
>>> 8． $twoDarray[] = $line;
>>> ...
>>> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
>>
>> It goes wrong at line 7. pg_copy_from() expects lines in the
>> COPY "text format" documented at
>> https://www.postgresql.org/docs/current/sql-copy.html
>>
>> It implies that:
>> - since your call to pg_copy_from() doesn't specify a delimiter
>> it uses tab, not a space, so implode() must be passed a tab,
>> not a space.
>> - if there are backslashes in the contents they must be quoted
>> by doubling them.
>> - if there are newline or carriage return characters in the contents
>> they must be replaced by \n and \r respectively, so as to
>> not be confused with an end of record.
>> - if there are tabs in the contents they must be replaced by \t.
>>
>> These replacements can all be done by a single strtr() call in php.
>>
>>
>> Best regards,
>> --
>> Daniel Vérité
>> PostgreSQL-powered mailer: http://www.manitou-mail.org
>> Twitter: @DanielVerite
>>
>
Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
Hello Daniel!
This afternoon, I tried it again, and yes, your suggestion that I use a tab
delimited symbol ("\t") instead of a space for the implode works flawlessly.
Yesterday, I reported I got "Warning: pg_copy_from(): Copy command failed:
ERROR: extra data after last expected..".
That was because while reading file, I had used this:
while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {
Today, I used this:
while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {
.. and now I can see my data saved in the database without those extra double
quotes.
It made my day!
Thank you.
- Original Message -
> From: Daniel Verite
> To: s4...@yahoo.co.jp
> Cc: pgsql-general@lists.postgresql.org
> Date: 2018/12/18, Tue 00:35
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text
> file that contains comma and double quotes
>
> wrote:
>
>> When I save that Excel as a tab delimited text file, I get this:rec_no
>> item1 item2 item3 item4 item5
>> 1 Denny's orange juice "1,500 yen"
> """Dear John""" "32""
>> TV"(As seen when I opened that file with Notepad)
>
> This looks good. Fields are properly enclosed and double quotes
> in contents are doubled, as expected in CSV.
>
>> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {
> //0 means I can
>> read row whatever its length
>> 6． if($row == 1){ $row++; continue; } //skip header
>> 7． $line = implode(" ",$line). "\n";
>> 8． $twoDarray[] = $line;
>> ...
>> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
>
> It goes wrong at line 7. pg_copy_from() expects lines in the
> COPY "text format" documented at
> https://www.postgresql.org/docs/current/sql-copy.html
>
> It implies that:
> - since your call to pg_copy_from() doesn't specify a delimiter
> it uses tab, not a space, so implode() must be passed a tab,
> not a space.
> - if there are backslashes in the contents they must be quoted
> by doubling them.
> - if there are newline or carriage return characters in the contents
> they must be replaced by \n and \r respectively, so as to
> not be confused with an end of record.
> - if there are tabs in the contents they must be replaced by \t.
>
> These replacements can all be done by a single strtr() call in php.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>
Does idle sessions will consume more cpu and ram? If yes,how to control them
In my application, the idle sessions are consuming cpu and ram. refer the ps command output. How idle session will consume more ram/cpu? How to control it? We are using Postgresql 9.2 with Centos 6 os. Please guide me. [image: image.png] -- Regards, Raghavendra Rao J S V Mobile- 8861161425
How to insert bulk data with libpq in C?
May I ask the proper (fast) way of inserting bulk data and if possible, an example would be very appreciated. Please note that the data I would like to insert contains arrays (float8[]). By the way, my environment is Windows 10, PostgreSQL 11 and compiling with Visual Studio 2017.
Re: Alter table column constraint [RESOLVED]
On Mon, 17 Dec 2018, Ron wrote: Melvin is saying to: 1. create a table named valid_industry, 2. populate it with the valid industries, 3. create an FK constraint on your main table's industry column to valid_industry.industry, and then 4. drop the constraint invalid_industry. Got it. Hadn't before considered making column check constraints into separate tables, but now I see the value of doing this. Thanks, all, Rich
Re: NL Join vs Merge Join - 5 hours vs 2 seconds
On Tue, 18 Dec 2018 at 09:21, Kumar, Virendra wrote: > I tried query with function dependency statistics but it did not help. I > don't see we can create such statistics on table join. There's mention of why this is the case in the docs in [1]: "14.2.2.1.1. Limitations of Functional Dependencies Functional dependencies are currently only applied when considering simple equality conditions that compare columns to constant values. They are not used to improve estimates for equality conditions comparing two columns or comparing a column to an expression, nor for range clauses, LIKE or any other type of condition." [1] https://www.postgresql.org/docs/10/planner-stats.html -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Alter table column constraint
On 12/17/18 12:20 PM, Rich Shepard wrote:
On Mon, 17 Dec 2018, Melvin Davidson wrote:
Yes, you must drop then add the revised constraint. However, from your
statement above, it sounds to me as if you would be better off using A
FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
Melvin,
I don't follow. Here's the DDL for that column:
industry varchar(24) NOT NULL
CONSTRAINT invalid_industry
CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
'Ports/Marine Services', 'Transportation')),
and I want to remove Municipalities for the more general Government.
What Melvin suggested was to:
1) Move this ('Agriculture', 'Business, other', 'Chemicals', ..)
into its own table say something like:
CREATE TABLE industry(industry_code varchar PRIMARY KEY, industry_desc
varchar)
2) Change the industry field in your existing table to:
industry varchar(24) NOT NULL REFERENCES industry(industry_code) ON
UPDATE CASCADE.
Where this helps is that in the situation you describe in your original
post you just change 'Municipalities' to 'Government' in the industry
table and the referring table automatically gets the change via the ON
UPDATE CASCADE.
Regards,
Rich
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Alter table column constraint
See https://www.postgresql.org/docs/current/tutorial-fk.html
On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard
> wrote:
> >
> > On Mon, 17 Dec 2018, Melvin Davidson wrote:
> >
> > > Yes, you must drop then add the revised constraint. However, from your
> > > statement above, it sounds to me as if you would be better off using A
> > > FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
> >
> > Melvin,
> >
> >I don't follow. Here's the DDL for that column:
> >
> > industry varchar(24) NOT NULL
> > CONSTRAINT invalid_industry
> > CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
> > 'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
> > 'Ports/Marine Services', 'Transportation')),
> >
> > and I want to remove Municipalities for the more general Government.
>
> --not tested
>
> CREATE TABLE industry (
> industry_name text PRIMARY KEY
> );
>
> CREATE TABLE company (
> company_id serial PRIMARY KEY,
> industry_name text REFERENCES industry (industry_name)
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> );
>
> UPDATE industries SET industry_name = 'Government' WHERE industry_name
> = 'Municipalities';
> -- All records in company have changed now too thanks to the ON UPDATE
> CASCADE
>
> To avoid the effective table rewrite use surrogate keys and turn the
> text into a simple label. It should still have a UNIQUE index on it
> though as it is your real key.
>
> David J.
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
Re: Alter table column constraint
So CREATE a table eg:
CREATE TABLE fks_for_tables
( fks_id serial
fks_values varchar(20),
CONSTRAINT fks_pkey PRIMARY KEY (fks_id),
CONSTRAINT fks-unique UNIQUE fks_values
)
Then
INSERT INTO fks_for_tables
(fks_values)
VALUES
( 'Agriculture'),
('Business'),
('other))',
'Chemicals')
...
...
('Transportation');
Then you can
ALTER TABLE your_table
ADD CONSTRAINT FOREIGN KEY (industry)
REFERENCES fks_for_tables(fks_valies);
On Mon, Dec 17, 2018 at 3:20 PM Rich Shepard
wrote:
> On Mon, 17 Dec 2018, Melvin Davidson wrote:
>
> > Yes, you must drop then add the revised constraint. However, from your
> > statement above, it sounds to me as if you would be better off using A
> > FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
>
> Melvin,
>
>I don't follow. Here's the DDL for that column:
>
> industry varchar(24) NOT NULL
> CONSTRAINT invalid_industry
> CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
> 'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
> 'Ports/Marine Services', 'Transportation')),
>
> and I want to remove Municipalities for the more general Government.
>
> Regards,
>
> Rich
>
>
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
Re: Alter table column constraint
On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard wrote:
>
> On Mon, 17 Dec 2018, Melvin Davidson wrote:
>
> > Yes, you must drop then add the revised constraint. However, from your
> > statement above, it sounds to me as if you would be better off using A
> > FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
>
> Melvin,
>
>I don't follow. Here's the DDL for that column:
>
> industry varchar(24) NOT NULL
> CONSTRAINT invalid_industry
> CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
> 'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
> 'Ports/Marine Services', 'Transportation')),
>
> and I want to remove Municipalities for the more general Government.
--not tested
CREATE TABLE industry (
industry_name text PRIMARY KEY
);
CREATE TABLE company (
company_id serial PRIMARY KEY,
industry_name text REFERENCES industry (industry_name)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
UPDATE industries SET industry_name = 'Government' WHERE industry_name
= 'Municipalities';
-- All records in company have changed now too thanks to the ON UPDATE CASCADE
To avoid the effective table rewrite use surrogate keys and turn the
text into a simple label. It should still have a UNIQUE index on it
though as it is your real key.
David J.
Re: Alter table column constraint
On 12/17/2018 02:20 PM, Rich Shepard wrote:
On Mon, 17 Dec 2018, Melvin Davidson wrote:
Yes, you must drop then add the revised constraint. However, from your
statement above, it sounds to me as if you would be better off using A
FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
Melvin,
I don't follow. Here's the DDL for that column:
industry varchar(24) NOT NULL
CONSTRAINT invalid_industry
CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
'Ports/Marine Services', 'Transportation')),
and I want to remove Municipalities for the more general Government.
Melvin is saying to:
1. create a table named valid_industry,
2. populate it with the valid industries,
3. create an FK constraint on your main table's industry column to
valid_industry.industry, and then
4. drop the constraint invalid_industry.
--
Angular momentum makes the world go 'round.
RE: NL Join vs Merge Join - 5 hours vs 2 seconds
Thank you Tom for initial thoughts! I tried query with function dependency statistics but it did not help. I don't see we can create such statistics on table join. Regards, Virendra -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, December 17, 2018 12:02 PM To: Kumar, Virendra Cc: pgsql-general@lists.postgresql.org Subject: Re: NL Join vs Merge Join - 5 hours vs 2 seconds "Kumar, Virendra" writes: > We are using Postgers-XL based on PostgreSQL 10.5 on RHEL7. We have an > extreme case of one query which running in 4 hour 45 mins and 33 seconds with > Nested Loops Join vs 2.5 seconds when we disable enable_nestloop. The query > is given below. I would be happy to share more information if required. It looks like what's burning you is bad estimates at these joins: >-> Nested Loop > (cost=33681.00..71431.33 rows=1 width=348) (actual time=359.006..926.303 > rows=37207 loops=3) > Join Filter: (se.account_id = > a.account_id) > -> Hash Join > (cost=33680.71..71298.94 rows=395 width=300) (actual time=358.953..656.923 > rows=36828 loops=3) >Hash Cond: ((se.account_id > = sh.account_id) AND (se.site_id = sh.site_id)) ... > -> Index Scan using > account_p113_account_id_idx on account_p113 a (cost=0.29..0.32 > rows=1 width=52) (actual time=0.003..0.005 rows=1 loops=110485) >Index Cond: (account_id = > sh.account_id) >Filter: (portfolio_id = > 113) I'm guessing that account_id, site_id, and portfolio_id are all highly correlated, but the planner doesn't know that and thinks the additional conditions will remove way more rows than they actually do. In PG10 and later, you can probably fix that by creating functional-dependency statistics on those pairs of columns. regards, tom lane This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
Re: Alter table column constraint
On Mon, 17 Dec 2018, Melvin Davidson wrote:
Yes, you must drop then add the revised constraint. However, from your
statement above, it sounds to me as if you would be better off using A
FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
Melvin,
I don't follow. Here's the DDL for that column:
industry varchar(24) NOT NULL
CONSTRAINT invalid_industry
CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
'Ports/Marine Services', 'Transportation')),
and I want to remove Municipalities for the more general Government.
Regards,
Rich
Re: Alter table column constraint
>I want to alter a term in a column's constraint to allow only specified > strings as attributes Yes, you must drop then add the revised constraint. However, from your statement above, it sounds to me as if you would be better off using A FOREIGN kEY CONSTRAINT. It makes things a lot simpler. On Mon, Dec 17, 2018 at 3:07 PM Joshua D. Drake wrote: > On 12/17/18 12:01 PM, Rich Shepard wrote: > > On Mon, 17 Dec 2018, Rich Shepard wrote: > > > >> I want to alter a term in a column's constraint to allow only specified > >> strings as attributes and have not found how to do this in the docs > >> (using > >> version 10 docs now). There is an alter table command that allows > >> renaming > >> a constraint but I've not seen how to modify the constraint itself. > > > > Is the procedure to drop the current check constraint then add the > > revised > > one? > > Or the other way around but yes. > > JD > > > > > > Rich > > > > -- > Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc > *** A fault and talent of mine is to tell it exactly how it is. *** > PostgreSQL centered full stack support, consulting and development. > Advocate: @amplifypostgres || Learn: https://postgresconf.org > * Unless otherwise stated, opinions are my own. * > > > -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
Re: Alter table column constraint
On 12/17/18 12:01 PM, Rich Shepard wrote: On Mon, 17 Dec 2018, Rich Shepard wrote: I want to alter a term in a column's constraint to allow only specified strings as attributes and have not found how to do this in the docs (using version 10 docs now). There is an alter table command that allows renaming a constraint but I've not seen how to modify the constraint itself. Is the procedure to drop the current check constraint then add the revised one? Or the other way around but yes. JD Rich -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc *** A fault and talent of mine is to tell it exactly how it is. *** PostgreSQL centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://postgresconf.org * Unless otherwise stated, opinions are my own. *
Re: Alter table column constraint
On Mon, 17 Dec 2018, Rich Shepard wrote: I want to alter a term in a column's constraint to allow only specified strings as attributes and have not found how to do this in the docs (using version 10 docs now). There is an alter table command that allows renaming a constraint but I've not seen how to modify the constraint itself. Is the procedure to drop the current check constraint then add the revised one? Rich
Alter table column constraint
I want to alter a term in a column's constraint to allow only specified strings as attributes and have not found how to do this in the docs (using version 10 docs now). There is an alter table command that allows renaming a constraint but I've not seen how to modify the constraint itself. Pointer to a reference needed. TIA, Rich
Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
Hi Daniel,
Thank you for the comments.
I changed my line 7 to this:
$line = implode("\t",$line). "\n";
Now, I get the following.
Warning: pg_copy_from(): Copy command failed: ERROR: extra data after last
expected column CONTEXT: COPY test_table, line 1: "1 Denny's orange juice
"1 500 yen" """Dear John""" "32"" TV"" in
- Original Message -
> From: Daniel Verite
> To: s4...@yahoo.co.jp
> Cc: pgsql-general@lists.postgresql.org
> Date: 2018/12/18, Tue 00:35
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text
> file that contains comma and double quotes
>
> wrote:
>
>> When I save that Excel as a tab delimited text file, I get this:rec_no
>> item1 item2 item3 item4 item5
>> 1 Denny's orange juice "1,500 yen"
> """Dear John""" "32""
>> TV"(As seen when I opened that file with Notepad)
>
> This looks good. Fields are properly enclosed and double quotes
> in contents are doubled, as expected in CSV.
>
>> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {
> //0 means I can
>> read row whatever its length
>> 6． if($row == 1){ $row++; continue; } //skip header
>> 7． $line = implode(" ",$line). "\n";
>> 8． $twoDarray[] = $line;
>> ...
>> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
>
> It goes wrong at line 7. pg_copy_from() expects lines in the
> COPY "text format" documented at
> https://www.postgresql.org/docs/current/sql-copy.html
>
> It implies that:
> - since your call to pg_copy_from() doesn't specify a delimiter
> it uses tab, not a space, so implode() must be passed a tab,
> not a space.
> - if there are backslashes in the contents they must be quoted
> by doubling them.
> - if there are newline or carriage return characters in the contents
> they must be replaced by \n and \r respectively, so as to
> not be confused with an end of record.
> - if there are tabs in the contents they must be replaced by \t.
>
> These replacements can all be done by a single strtr() call in php.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>
Re: NL Join vs Merge Join - 5 hours vs 2 seconds
"Kumar, Virendra" writes: > We are using Postgers-XL based on PostgreSQL 10.5 on RHEL7. We have an > extreme case of one query which running in 4 hour 45 mins and 33 seconds with > Nested Loops Join vs 2.5 seconds when we disable enable_nestloop. The query > is given below. I would be happy to share more information if required. It looks like what's burning you is bad estimates at these joins: >-> Nested Loop > (cost=33681.00..71431.33 rows=1 width=348) (actual time=359.006..926.303 > rows=37207 loops=3) > Join Filter: (se.account_id = > a.account_id) > -> Hash Join > (cost=33680.71..71298.94 rows=395 width=300) (actual time=358.953..656.923 > rows=36828 loops=3) >Hash Cond: ((se.account_id > = sh.account_id) AND (se.site_id = sh.site_id)) ... > -> Index Scan using > account_p113_account_id_idx on account_p113 a (cost=0.29..0.32 > rows=1 width=52) (actual time=0.003..0.005 rows=1 loops=110485) >Index Cond: (account_id = > sh.account_id) >Filter: (portfolio_id = > 113) I'm guessing that account_id, site_id, and portfolio_id are all highly correlated, but the planner doesn't know that and thinks the additional conditions will remove way more rows than they actually do. In PG10 and later, you can probably fix that by creating functional-dependency statistics on those pairs of columns. regards, tom lane
Re: conditionally terminate psql script
On 12/17/2018 09:01 AM, Jerry Sievers wrote: haman...@t-online.de writes: Hi, many thanks -- too bad I am still using 9.3 Just because your server backend is 9.3 does not rule out using much newer clients, such as psql. While technically true, and is useful, many production servers (especially ones that must be PCI compliant) heavily restrict who can remotely connect to the database, and so superusers are stuck with what's installed, since installing new stuff has enough paperwork and bureaucracy to make Kafka weep. (Of course, if you must be PCI compliant, you should get off 9.3 before the auditors drop the hammer on you.) -- Angular momentum makes the world go 'round.
Re: conditionally terminate psql script
haman...@t-online.de writes: > Hi, > > many thanks -- too bad I am still using 9.3 Just because your server backend is 9.3 does not rule out using much newer clients, such as psql. HTH > > Best regards > Wolfgang > >>> Hi >>> >> po 17. 12. 2018 v 13:14 odesílatel napsal: >>> >> > >>> > >>> > Hi, >>> > >>> > is there a way to stop execution of a psql script if a select returns some >>> > rows (or no rows) >>> > The idea is to add a safety check on data, specifically to select all new >>> > rows that would conflict >>> > on a bulk insert, show them and stop >>> > >>> >> you need psql from PostgreSQL 10 and higher >>> >> there is a \if statement >>> >> Regards >>> >> Pavel >>> >> > >>> > Best regards >>> > Wolfgang >>> > >>> > >>> > >>> > > > > > > -- Jerry Sievers e: jerry.siev...@comcast.net p: 312.241.7800
NL Join vs Merge Join - 5 hours vs 2 seconds
We are using Postgers-XL based on PostgreSQL 10.5 on RHEL7. We have an extreme case of one query which running in 4 hour 45 mins and 33 seconds with Nested Loops Join vs 2.5 seconds when we disable enable_nestloop. The query is given below. I would be happy to share more information if required. -- Plan A - NL enabled: -- Limit (cost=78922.65..87812.39 rows=1 width=584) (actual time=53476.337..17133622.713 rows=353 loops=1) Buffers: shared hit=181422132, temp read=50909275 written=50909273 -> GroupAggregate (cost=78922.65..87812.39 rows=1 width=584) (actual time=53476.334..17133622.035 rows=353 loops=1) Group Key: sh."row", sh.col Buffers: shared hit=181422132, temp read=50909275 written=50909273 -> Nested Loop (cost=78922.65..87812.20 rows=1 width=536) (actual time=51768.638..17132518.985 rows=51562 loops=1) Join Filter: ((se.account_id = pe.account_id) AND (se.peril_id = pe.peril_id)) Rows Removed by Join Filter: 2403022205 Buffers: shared hit=181422132, temp read=50909275 written=50909273 -> GroupAggregate (cost=72432.96..72433.14 rows=1 width=368) (actual time=51513.891..52815.004 rows=51567 loops=1) Group Key: sh."row", sh.col, se.portfolio_id, se.peril_id, se.account_id Buffers: shared hit=22235530, temp read=7878 written=7876 -> Sort (cost=72432.96..72432.96 rows=1 width=324) (actual time=51513.864..51790.397 rows=111621 loops=1) Sort Key: sh."row", sh.col, se.peril_id, se.account_id Sort Method: external merge Disk: 36152kB Buffers: shared hit=22235530, temp read=7878 written=7876 -> Nested Loop Semi Join (cost=34681.42..72432.95 rows=1 width=324) (actual time=238.805..51120.362 rows=111621 loops=1) Join Filter: (a.account_id = se1.account_id) Buffers: shared hit=22235530, temp read=3359 written=3353 -> Gather (cost=34681.00..72431.43 rows=1 width=348) (actual time=238.757..1085.453 rows=111621 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=421611, temp read=3359 written=3353 -> Nested Loop (cost=33681.00..71431.33 rows=1 width=348) (actual time=359.006..926.303 rows=37207 loops=3) Join Filter: (se.account_id = a.account_id) Buffers: shared hit=421611, temp read=3359 written=3353 -> Hash Join (cost=33680.71..71298.94 rows=395 width=300) (actual time=358.953..656.923 rows=36828 loops=3) Hash Cond: ((se.account_id = sh.account_id) AND (se.site_id = sh.site_id)) Buffers: shared hit=89803, temp read=3359 written=3353 -> Parallel Bitmap Heap Scan on site_exposure_p113 se (cost=2447.82..32716.49 rows=15266 width=276) (actual time=4.980..165.908 rows=36639 loops=3) Recheck Cond: (shape && '010320E610010005006EDDDBF40F285FC03D719130D9532340BEE0DBF4BFCE53C03D719130D9532340BEE0DBF4BFCE53C0B2C127D5E26B42406EDDDBF40F285FC0B2C127D5E26B42406EDDDBF40F285FC03D719130D9532340'::geometry) Filter: ((portfolio_id = 113) AND _st_intersects(shape, '010320E610010005006EDDDBF40F285FC03D719130D9532340BEE0DBF4BFCE53C03D719130D9532340BEE0DBF4BFCE53C0B2C127D5E26B42406EDDDBF40F285FC0B2C127D5E26B42406EDDDBF40F285FC03D719130D9532340'::geometry)) Heap Blocks: exact=2704 Buffers: shared hit=16522 -> Bitmap Index Scan on site_exposure_p113_shape_idx (cost=0.00..2438.66 rows=109917 width=0) (actual time=12.154..12.155 rows=109917 loops=1) Index Cond: (shape && '010320E610010005006EDDDBF40F285FC03D719130D9532340BEE0DBF4BFCE53C03D719130D9532340BEE0DBF4BFCE53C0B2C127D5E26B42406EDDDBF40F285FC0B2C127D5E26B42406EDDDBF40F285FC03D719130D9532340'::geometry) Buffers: shared hit=797 -> Hash (cost=28094.08..28094.08 rows=143721 width=28) (actual time=352.223..352.224 rows=144202 loops=3)
Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
wrote:
> When I save that Excel as a tab delimited text file, I get this:rec_no
> item1item2item3item4item5
> 1Denny'sorange juice"1,500 yen""""Dear John""""32""
> TV"(As seen when I opened that file with Notepad)
This looks good. Fields are properly enclosed and double quotes
in contents are doubled, as expected in CSV.
> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) { //0 means I can
> read row whatever its length
> 6．if($row == 1){ $row++; continue; } //skip header
> 7．$line = implode(" ",$line). "\n";
> 8．$twoDarray[] = $line;
> ...
> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
It goes wrong at line 7. pg_copy_from() expects lines in the
COPY "text format" documented at
https://www.postgresql.org/docs/current/sql-copy.html
It implies that:
- since your call to pg_copy_from() doesn't specify a delimiter
it uses tab, not a space, so implode() must be passed a tab,
not a space.
- if there are backslashes in the contents they must be quoted
by doubling them.
- if there are newline or carriage return characters in the contents
they must be replaced by \n and \r respectively, so as to
not be confused with an end of record.
- if there are tabs in the contents they must be replaced by \t.
These replacements can all be done by a single strtr() call in php.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Re: REVOKE to an user that doesn't exist
Il 12/12/2018 16:01, Tom Lane ha scritto: The safest way to clean it up manually would be to set the pg_proc.proacl field for that function to NULL. If there are other grants about the function, you could try removing the bad entry, but it would likely be safer to just re-grant after the upgrade. Setting the column (and several others from other tables such as pg_class) to null made me possible to upgrade the cluster. After the upgrade, I issued the necessary GRANTs and everything is up & running on Postgres 10 now! Thanks again Moreno.-
Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
Hello,
On Mon, 2018-12-17 at 21:34 +0900, s4...@yahoo.co.jp wrote:
> Hello Good People of the Forum!
>
> I am trying to insert some data into a PostgreSQL database using PHP
> and struggling to create an array so that pg_copy_from function will
> accept and process the data.
>
> I can insert data but not the way I want- my data this case contains
> comma, space, double quotes and unpaired double quote.
> I need to use Excel to create the data and save it as tab delimited
> text file, and then convert encoding to UTF-8 (from Excel's ANSI)
> before passing it to the PHP.
>
> Here is my dummy data in Excel:
> rec_noitem1item2item3item4item5
> 1Denny'sorange juice1,500 yen"Dear John"32" TV
>
> Explanation: the first row is header.
> The second row is data for each column.
> I tried to create variation like apostrophe, space between words,
> comma, double quotes and unpaired double quote (32" TV).
>
> When I save that Excel as a tab delimited text file, I get this:
> rec_noitem1item2item3item4item5
> 1Denny'sorange juice"1,500 yen""""Dear John"""
> "32"" TV"
> (As seen when I opened that file with Notepad)
>
> Because my data also contains non-ascii, I saved the file with UTF-8
> encoding using the Notepad.
>
> Then I created a two-dimensional array with PHP:
>
> 1．$file = 'test.txt'; //tab delimited file
> 2．$fileRead = fopen($file, 'r');
>
> 3．$row = 1;
> 4．$twoDarray = array();
> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) { //0 means
> I can read row whatever its length
> 6．if($row == 1){ $row++; continue; } //skip header
> 7．$line = implode(" ",$line). "\n";
> 8．$twoDarray[] = $line;
> 9．}
> 10．fclose($fileRead);
>
> Then I passed that twoDarray to pg_copy_from.
>
> $con=pg_connect("host=$host dbname=$dbname port=5432 user=$user
> password=$password");
>
> 11．if (!$con) {
> 12．die("Couldn't open..\n");
> 13．}
>
> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
> 15．print "OK!";
> 16．}
> 17．else{
> 18．print "Not OK.";
> 19．}
>
> When I run the program, I have this error:
> Warning: pg_copy_from(): Copy command failed:
> ERROR: value too long for type character varying(32) CONTEXT: COPY
> test_table, line 1, column rec_no: "1 Denny's orange juice 1,500 yen
> "Dear John" 32" TV" in testProgram.php line xx.
>
> My table definition is:
> CREATE TABLE test_table (
> rec_no VARCHAR(32) PRIMARY KEY NOT NULL,
> item1 VARCHAR(255),..item2 .. until item5.);
>
> Obviously, my program thinks everything in the data row is for the
> first field.
> No, no.
>
> How to make it think that
> 1 is for the 'rec_no' field,
> Denny's is for the 'item1' field,
> orange juice is for the 'item2' field,
> 1,500 yen is for the 'item3' field,
> "Dear John" is for the 'item4' field and
> 32" TV is for the 'item5' field?
>
> When I tried removing '0' from line 5, that is,
> while (($line = fgetcsv($fileRead, "\t")) !== FALSE) { //without read
> length
>
> I can see data written in the database, but with some extra double
> quotes and a missing comma!
> That is data was saved as
> Denny's, orange juice, "1 500 yen","""Dear John""", and
> "32"" TV"
> into the respective fields.
> I cannot have those extra double quotes, and I cannot have missing
> comma in my data.
fgetscsv returns an array from reading a record from a text file.
So $line = fgetcsv($fileRead, 0, "\t", '"') would use tab as the
delimiter and remove any enclosure character equal to ".
I don't use Excel. With Libreoffice you can set the delimiter to the
pipe character and tell it not to use enclosures. So I don't know how
to massage your Excel file so that 32" TV is rendered correctly.
Secondly, in PHP implode takes an array and turns it into a string with
a specified character used to delimit the values from the array.
I can only suggest that you read the PHP manual, as well as do some
searches for the use of pg_copy_from, although I doubt anything useful
will turn up.
Cheers,
Rob
Re: [External] Re: simple query on why a merge join plan got selected
Thanks a lot Tom, as always :) We generally do not have so many duplicates in production, so maybe this is an edge case but I am happy with the explanation and the code reference for the analysis. I’ll also play with default statistic target to see what changes by increasing the value. On Sun, 16 Dec 2018 at 5:52 AM Tom Lane wrote: > Vijaykumar Jain writes: > > I was just playing with exploring joins and plans i came across this > > create table t1(a int); > > create table t2(a int); > > insert into t1 select (x % 10) from generate_series(1, 10) x; > > insert into t2 select (x % 100) from generate_series(1, 10) x; > > ... > > select * from t1 join t2 using (a); > > Hm. This is a fairly extreme case for mergejoining. In the first place, > because of the disparity in the key ranges (t1.a goes from 0..9, t2.a > from 0..99) the planner can figure out that a merge join can stop after > scanning only 10% of t2. That doesn't help much here, since we still > have to sort all of t2, but nonetheless the planner is going to take > that into account. In the second place, because you have so many > duplicate values, most rows in t1 will require "rescanning" 1000 rows > that were already read and joined to the previous row of t1 (assuming > t1 is on the left of the join; it's worse if t2 is on the left). > > The planner estimates each of those situations properly, but it looks > to me like it is not handling the combination of both effects correctly. > In costsize.c we've got > > /* > * The number of tuple comparisons needed is approximately number of > outer > * rows plus number of inner rows plus number of rescanned tuples (can > we > * refine this?). At each one, we need to evaluate the mergejoin > quals. > */ > startup_cost += merge_qual_cost.startup; > startup_cost += merge_qual_cost.per_tuple * > (outer_skip_rows + inner_skip_rows * rescanratio); > run_cost += merge_qual_cost.per_tuple * > ((outer_rows - outer_skip_rows) + > (inner_rows - inner_skip_rows) * rescanratio); > > where outer_rows and inner_rows are the numbers of rows we're predicting > to actually read from each input, the xxx_skip_rows values are zero for > this example, and rescanratio was previously computed as > > /* We'll inflate various costs this much to account for rescanning */ > rescanratio = 1.0 + (rescannedtuples / inner_path_rows); > > where inner_path_rows is the *total* size of the inner relation, > including rows that we're predicting won't get read because of the > stop-short effect. > > As far as I can tell, that comment's claim about the number of tuple > comparisons needed is on-target ... but the code is computing a number > of tuple comparisons 10x less than that. The reason is that rescanratio > is wrong: it should be > > rescanratio = 1.0 + (rescannedtuples / inner_rows); > > instead, so that it's something that makes sense to multiply inner_rows > by. In the existing uses of rescanratio, one multiplies it by > inner_path_rows and needs to be changed to inner_rows to agree with > this definition, but the other uses are already consistent with this. > > This doesn't make a significant difference if either rescannedtuples > is small, or inner_rows isn't much less than inner_path_rows. But > when neither is true, we can greatly underestimate the number of tuple > comparisons we'll have to do, as well as the number of re-fetches from > the inner plan node. I think in practice it doesn't matter that often, > because in such situations we'd usually not have picked a mergejoin > anyway. But in your example the buggy mergejoin cost estimate is about > 10% less than the hashjoin cost estimate, so we go with mergejoin. > > The attached proposed patch fixes this, raising the mergejoin cost > estimate to about 35% more than the hashjoin estimate, which seems > a lot closer to reality. It doesn't seem to change any results in > the regression tests, which I find unsurprising: there are cases > like this in the tests, but as I just said, they pick hashjoins > already. > > Also interesting is that after this fix, the estimated costs of a > mergejoin for this example are about the same whether t1 or t2 is on > the left. I think that's right: t2-on-the-left has 10x more rescanning > to do per outer tuple, but it stops after scanning only 10% of the > outer relation, canceling that out. > > I'm not sure whether to back-patch this. It's a pretty clear thinko, > but there's the question of whether we'd risk destabilizing plan > choices that are working OK in the real world. > > regards, tom lane > > -- Regards, Vijay
Re: conditionally terminate psql script
Hi, many thanks, I will give it a try tomorrow Best regards Wolfgang >> >> On 17.12.2018 16:07, haman...@t-online.de wrote: >> > Hi, many thanks -- too bad I am still using 9.3 >> >> In this case you can try ON_ERROR_STOP psql variable. >> Something like this: >> >> \set ON_ERROR_STOP on >> >> do $$ >> declare >> total bigint; >> begin >> select count(*) into total from pg_class where 1=1; >> if total = 0 then >> raise exception 'Nothing found.'; >> end if; >> >> raise notice '% records found.', total; >> end; >> $$ language plpgsql; >> >> \echo Continue execution... >> >> - >> Pavel Luzanov >> Postgres Professional: http://www.postgrespro.com >> The Russian Postgres Company >> >> >>
Re: conditionally terminate psql script
On 17.12.2018 16:07, haman...@t-online.de wrote: Hi, many thanks -- too bad I am still using 9.3 In this case you can try ON_ERROR_STOP psql variable. Something like this: \set ON_ERROR_STOP on do $$ declare total bigint; begin select count(*) into total from pg_class where 1=1; if total = 0 then raise exception 'Nothing found.'; end if; raise notice '% records found.', total; end; $$ language plpgsql; \echo Continue execution... - Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: conditionally terminate psql script
Hi, many thanks -- too bad I am still using 9.3 Best regards Wolfgang >> Hi >> >> po 17. 12. 2018 v 13:14 odesílatel napsal: >> >> > >> > >> > Hi, >> > >> > is there a way to stop execution of a psql script if a select returns some >> > rows (or no rows) >> > The idea is to add a safety check on data, specifically to select all new >> > rows that would conflict >> > on a bulk insert, show them and stop >> > >> >> you need psql from PostgreSQL 10 and higher >> >> there is a \if statement >> >> Regards >> >> Pavel >> >> > >> > Best regards >> > Wolfgang >> > >> > >> > >>
Re: conditionally terminate psql script
Hi is there a way to stop execution of a psql script if a select returns some rows (or no rows) The idea is to add a safety check on data, specifically to select all new rows that would conflict on a bulk insert, show them and stop Look at \if command in psql (since v10): select count(*) as total from pg_class where 1 = 1\gset select :total = 0 as notfound\gset \if :notfound \echo Nothing found. \q \endif \echo :total records found. - Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: conditionally terminate psql script
Hi po 17. 12. 2018 v 13:14 odesílatel napsal: > > > Hi, > > is there a way to stop execution of a psql script if a select returns some > rows (or no rows) > The idea is to add a safety check on data, specifically to select all new > rows that would conflict > on a bulk insert, show them and stop > you need psql from PostgreSQL 10 and higher there is a \if statement Regards Pavel > > Best regards > Wolfgang > > >
Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes
Hello Good People of the Forum!
I am trying to insert some data into a PostgreSQL database using PHP and
struggling to create an array so that pg_copy_from function will accept and
process the data.
I can insert data but not the way I want- my data this case contains comma,
space, double quotes and unpaired double quote.I need to use Excel to create
the data and save it as tab delimited text file, and then convert encoding to
UTF-8 (from Excel's ANSI) before passing it to the PHP.
Here is my dummy data in Excel:rec_no item1 item2 item3 item4
item5
1 Denny's orange juice 1,500 yen "Dear John" 32" TV
Explanation: the first row is header.The second row is data for each column.I
tried to create variation like apostrophe, space between words, comma, double
quotes and unpaired double quote (32" TV).
When I save that Excel as a tab delimited text file, I get this:rec_no item1
item2 item3 item4 item5
1 Denny's orange juice "1,500 yen" """Dear John""" "32"" TV"(As
seen when I opened that file with Notepad)
Because my data also contains non-ascii, I saved the file with UTF-8 encoding
using the Notepad.
Then I created a two-dimensional array with PHP:
1．$file = 'test.txt'; //tab delimited file
2．$fileRead = fopen($file, 'r');
3．$row = 1;
4．$twoDarray = array();
5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) { //0 means I can
read row whatever its length
6． if($row == 1){ $row++; continue; } //skip header
7． $line = implode(" ",$line). "\n";
8． $twoDarray[] = $line;
9．}
10．fclose($fileRead);
Then I passed that twoDarray to pg_copy_from.
$con=pg_connect("host=$host dbname=$dbname port=5432 user=$user
password=$password");
11．if (!$con) {
12． die("Couldn't open..\n");
13．}
14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
15． print "OK!";
16．}
17．else{
18． print "Not OK.";
19．}
When I run the program, I have this error:Warning: pg_copy_from(): Copy command
failed:
ERROR: value too long for type character varying(32) CONTEXT: COPY test_table,
line 1, column rec_no: "1 Denny's orange juice 1,500 yen "Dear John" 32" TV" in
testProgram.php line xx.
My table definition is:CREATE TABLE test_table (
rec_no VARCHAR(32) PRIMARY KEY NOT NULL,
item1 VARCHAR(255),..item2 .. until item5.);
Obviously, my program thinks everything in the data row is for the first
field.No, no.
How to make it think that
1 is for the 'rec_no' field,Denny's is for the 'item1' field,
orange juice is for the 'item2' field,
1,500 yen is for the 'item3' field,
"Dear John" is for the 'item4' field and
32" TV is for the 'item5' field?
When I tried removing '0' from line 5, that is,
while (($line = fgetcsv($fileRead, "\t")) !== FALSE) { //without read length
I can see data written in the database, but with some extra double quotes and a
missing comma!That is data was saved asDenny's, orange juice, "1 500 yen",
"""Dear John""", and "32"" TV"into the respective fields.
I cannot have those extra double quotes, and I cannot have missing comma in my
data.
conditionally terminate psql script
Hi, is there a way to stop execution of a psql script if a select returns some rows (or no rows) The idea is to add a safety check on data, specifically to select all new rows that would conflict on a bulk insert, show them and stop Best regards Wolfgang
Re: loading jdbc Driver in servlet
On Mon, 17 Dec 2018 at 02:28, Thomas Kellerer wrote:
> Rob Sargent schrieb am 14.12.2018 um 19:28:
> > Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9
> >
> > It appears to me that I need to make the call
> > "Class.forName("org.postgresql.Driver)" when the entry is in a
> > servlet. Is this expected, within a servlet, or is this just /post
> > hoc ergo propter hoc /at it finest and I changed something else
> > (wittingly or not). Same code outside of servlet does not need the
> > forced loading of the class and the manual claims it's not need after
> > java 1.6
>
> Class.forName() is definitely not needed if the driver's JAR file is
> included in the classloader of the class requesting a connection.
>
> Where exactly did you put the JDBC driver's jar file?
> And what exactly is your main() method doing?
>
> If you look at Tomcat's startup script (catalina.sh or catalina.bat), it's
> obvious that setting up the claspath isn't that straightforward.
> My guess is, that that your main() method does something different
> and does not properly include the driver's jar in the classpath.
>
Servlet classpath issues are legendary. As Thomas points out setting up the
classpath for a servlet engine is not trivial.
Dave Cramer
da...@postgresintl.com
www.postgresintl.com
>
>
