Re: Enforce primary key on every table during dev?

[John McKown]

On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel  wrote:

> We want to enforce a policy, partly just to protect those who might
> forget, for every table in a particular schema to have a primary key.  This
> can't be done with event triggers as far as I can see, because it is quite
> legitimate to do:
>
> BEGIN;
> CREATE TABLE foo (id int);
> ALTER TABLE foo ADD PRIMARY KEY (id);
> COMMIT;
>
> It would be nice to have some kind of "deferrable event trigger" or some
> way to enforce that no transaction commits which added a table without a
> primary key.
>
> Any ideas?
>
> Thanks,
> Jeremy
>


​What stops somebody from doing:

CREATE TABLE foo (filler text primary key default null, realcol1 int,
realcol2 text);

And then just never bother to ever insert anything into the column FILLER?
It fulfills your stated requirement​ of every table having a primary key.
Of course, you could amend the policy to say a "non-NULL primary key".



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: Enforce primary key on every table during dev?

[John McKown]

On Wed, Feb 28, 2018 at 7:57 AM, Adrian Klaver 
wrote:

> On 02/28/2018 05:52 AM, John McKown wrote:
>
>> On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel > <mailto:finz...@gmail.com>>wrote:
>>
>> We want to enforce a policy, partly just to protect those who might
>> forget, for every table in a particular schema to have a primary
>> key.  This can't be done with event triggers as far as I can see,
>> because it is quite legitimate to do:
>>
>> BEGIN;
>> CREATE TABLE foo (id int);
>> ALTER TABLE foo ADD PRIMARY KEY (id);
>> COMMIT;
>>
>> It would be nice to have some kind of "deferrable event trigger" or
>> some way to enforce that no transaction commits which added a table
>> without a primary key.
>>
>> Any ideas?
>>
>> Thanks,
>> Jeremy
>>
>>
>>
>> ​What stops somebody from doing:
>>
>> CREATE TABLE foo (filler text primary key default null, realcol1 int,
>> realcol2 text);
>>
>> And then just never bother to ever insert anything into the column
>> FILLER? It fulfills your stated requirement​ of every table having a
>>
>
> Then you would get this:
>
> test=# CREATE TABLE foo (filler text primary key default null, realcol1
> int, realcol2 text);
> CREATE TABLE
> test=# insert into  foo (realcol1, realcol2) values (1, 'test');
> ERROR:  null value in column "filler" violates not-null constraint
> DETAIL:  Failing row contains (null, 1, test).
>

​Hum, it's been so long, I totally forgot. Which makes me wonder why the
parser doesn't "know" that a default of NULL for a primary key is going to
fail anyway and flag it at CREATE time. Oh, well. Thanks.​



>
> primary key. Of course, you could amend the policy to say a "non-NULL
>> primary key".
>>
>>
>>
>> --
>> I have a theory that it's impossible to prove anything, but I can't prove
>> it.
>>
>> Maranatha! <><
>> John McKown
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

[John McKown]

On Wed, May 2, 2018 at 4:29 PM, Jim Michaels  wrote:

> what do you think about foreign data wrappers getting CSV file table I/O?
> - I had thought that CSVQL db could be implemented completely with
> ​
>

​I don't know what you want to do with this. SQLite already supports it.
SQLite is an embedded SQL database​ software. To a great extent, the SQL
that it understands is similar to what PostgreSQL understands. It is one of
the "standards" that the author uses. It implements "virtual tables" via
extensions. One of these is to process CSV files. ref:
http://sqlite.org/csv.html  The CSV it understands is RFC 4180 format (
https://www.ietf.org/rfc/rfc4180.txt). SQLite is open source and is _PUBLIC
DOMAIN_. That is, it has NO copyright at all. So you can do anything with
it that you want to. You might even be able to use the source to the SQLite
extension (
https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c ) to
write the PostgreSQL foreign data wrapper.

So, if you just need something so that you can read a CSV using SQL, then
you might consider using SQLite instead of, or in addition to, PostgreSQL.
Or, if you need to do subselects, unions, or other things containing both
PostgreSQL data & CSV data, then I guess you're stuck with the foreign data
wrapper.



> ​
>
> --
> ==
> Jim Michaels 
>
>


-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Enhancement to psql command, feedback.

[John McKown]

I just wanted to throw this out to the users before I made a complete fool
of myself by formally requesting it. But I would like what I hope would be
a minor change (enhancement) to the psql command. If you look on this page,
https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT


To me this looks similar to a UNIX shell script. Now, going sideways for a
second, if someone wanted to create a "self contained" awk script. It would
look something like:

#!/bin/awk -f
... awk code ...

When a user executes the above from the command line, the UNIX system runs
the program in the first "magic" line as if the user had entered "/bin/awk
-f ..." where the ... is replaced by the name of the file executed followed
by the rest of the command line parameters.

I think it would be nice if psql would do the same, mainly for
"consistency" with other UNIX scripting languages, such as python, perl, &
gawk.

The example above would then become:

#!/bin/psql -U postgres template1 -f
REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

Does this seem reasonable to others? When I actually try the following as a
"script", I get an error.

=== transcript ===

$ls -l ./x.psql; cat ./x.psql; ./x.psql
-rwxr-xr-x. 1 joarmc joarmc 40 May  9 02:55 ./x.psql
#!/usr/bin/psql -f
select * from table;
psql:./x.psql:2: ERROR:  syntax error at or near "#!/"
LINE 1: #!/usr/bin/psql -f
   ^


​I have not looked at the source yet, but it seems that it would be "easy"
to implement if psql would simply ignore the first line of any file
referenced via the "-f" parameter if it started with "#!" or maybe even
just "#". I'm not suggesting ignoring _every_ line that start with that
"magic", just the first.​


-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 3:05 AM, Pavel Stehule 
wrote:

>
>
> 2018-05-09 9:59 GMT+02:00 John McKown :
>
>> I just wanted to throw this out to the users before I made a complete
>> fool of myself by formally requesting it. But I would like what I hope
>> would be a minor change (enhancement) to the psql command. If you look on
>> this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
>> you will see a number of example which look like:
>>
>> psql -U postgres template1 -f - << EOT
>>
>> REVOKE ALL ON DATABASE template1 FROM public;
>> REVOKE ALL ON SCHEMA public FROM public;
>> GRANT ALL ON SCHEMA public TO postgres;
>> CREATE LANGUAGE plpgsql;
>>
>> EOT
>>
>>
>> To me this looks similar to a UNIX shell script. Now, going sideways for
>> a second, if someone wanted to create a "self contained" awk script. It
>> would look something like:
>>
>> #!/bin/awk -f
>> ... awk code ...
>>
>> When a user executes the above from the command line, the UNIX system
>> runs the program in the first "magic" line as if the user had entered
>> "/bin/awk -f ..." where the ... is replaced by the name of the file
>> executed followed by the rest of the command line parameters.
>>
>> I think it would be nice if psql would do the same, mainly for
>> "consistency" with other UNIX scripting languages, such as python, perl, &
>> gawk.
>>
>
> These languages has defined # as line comment. It is not true for SQL.
>

​Thanks, that looks like a "NO" vote to me. ​



>
> Regards
>
> Pavel
>
>

-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:17 AM, Ron  wrote:

> On 05/09/2018 02:59 AM, John McKown wrote:
>
> I just wanted to throw this out to the users before I made a complete fool
> of myself by formally requesting it. But I would like what I hope would be
> a minor change (enhancement) to the psql command. If you look on this page,
> https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
> you will see a number of example which look like:
>
> psql -U postgres template1 -f - << EOT
>
> REVOKE ALL ON DATABASE template1 FROM public;
> REVOKE ALL ON SCHEMA public FROM public;
> GRANT ALL ON SCHEMA public TO postgres;
> CREATE LANGUAGE plpgsql;
>
> EOT
>
>
> To me this looks similar to a UNIX shell script.
>
>
> Because it *is* a Unix shell script.  The "<< EOT" is part of a heredoc,
> which is designed to keep everything in one place instead of needing a
> second file for the SQL commands.
>
> https://en.wikipedia.org/wiki/Here_document
>
> (The concept is as old as computing.  Anyone who's worked on mainframes or
> proprietary minicomputers from DEC will instantly recognize it.)
>

​Yes, I use HERE docs in my shell scripts. I was just, sort of, wanting to
avoid that by making a "slight" change to the psql program to ignore the
first (and only the first) line of any file referenced​ via a "-f". This is
NOT any kind of critical necessity. I just think it would be "nice" simply
because _I_ have a habit of use the "magic" #! at the start of the first
like in order to have other "languages" (such as python, perl, gawk), be
invoked with the script file name as a parameter. One reason to avoid a
HERE doc is from what I've learned about how BASH at least implements them.
The BASH shell sees the HERE document and copies it into a "temporary" disk
file. It then opens this file and supplies that file descriptor to whatever
is being fed the HERE document as input. So, in effect, using a HERE
document, at least in BASH, does a lot more I/O to the disk system.

Again, this is just a discussion point. And I'm quite willing to admit
defeat if most people don't think that it is worth the effort.



>
>
> --
> Angular momentum makes the world go 'round.
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:56 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, May 9, 2018 at 6:44 AM, John McKown 
> wrote:
>
>> Again, this is just a discussion point. And I'm quite willing to admit
>> defeat if most people don't think that it is worth the effort.
>>
>
> ​-1, at least per the example.  I would not want "-U postgres" inside the
> file.  I tend to rely on service entries, not environment variables, and
> wouldn't want to hard-code them either.  While psql has grown more
> flow-control capabilities recently it is, in most cases, a support language
> for me, not a main entry point.  Shell scripts merge the per-instance
> run-time environment I need with the behavior the script provides - merging
> that I find I need more often than not and don't miss the added overhead in
> the few cases where it is unnecessary.
>
> David J.
>
>
​I agree. I wouldn't want the -U inside a "regular" shell script either. As
a minor example, consider the following _almost_ equivalent scripts.


$ cat psql-script.sh
#!/bin/sh
psql "$@" -f - <${file}
psql -U postgres -d somedb -h remote-host.com -f ${file}

It just that the HERE document doesn't actually create the ${file}
variable. I have NO idea how other shell implement HERE documents.

However, in the second case, the "magic" first line causes psql, at
present, to report an error and abort. This is why I'd like to modify how
the file referenced via the -f argument is processed. That is, the first
line of any file referenced & executed via the -f argument will be ignored
if and only if it starts with a shebang (#!). If the first line of the file
does not start with a shebang, it is processed normally as are all
subsequent lines.

If I get the energy & time, I'll give a look at the actual source. If it is
within my, admitted limited, ability to generate a patch to implement what
I'm thinking of, I'll post it over on the development forum.


-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:52 AM, Stephen Frost  wrote:

> Greetings,
>
> * John McKown (john.archie.mck...@gmail.com) wrote:
> > Again, this is just a discussion point. And I'm quite willing to admit
> > defeat if most people don't think that it is worth the effort.
>
> For my 2c, at least, I do think it'd be kind of neat to have, but we'd
> need a fool-proof way to realize that's how we're being called and,
> ideally, that would be something we could detect without having to have
> special flags for psql which anyone writing such a script would have to
> be aware of.
>

​I probably should have taken a good look at how the psql code actually
handles the "-f" argument. Unfortunately, I've been very "time poor​"
recently due to some medical work which, along with "real" work, keeps me
away from the house for about 15 hrs a day, except for weekends which I
used to try to recover.



>
> Do you know if there's a way to detect that we're being called this
> way..?
>
> Thanks!
>
> Stephen
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Control PhoneNumber Via SQL

[John McKown]

On Tue, May 15, 2018 at 4:10 AM, tango ward  wrote:

>
> 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
> number'), blank=True)
>
>
> The data for phone number that I am migrating doesn't have country code. I
> want to determine first if the number has country code in it, if it doesn't
> then I will add the country code on the number before INSERTING it to the
> destination database.
>
> Any suggestion will be highly appreciated.
>
>
> Thanks,
> J
>
​
I don't have any code for you, if that is what you are soliciting. I did
find a couple of informative web sites which help explain how international
phone numbers are formatted. These are known as E.164 numbers.


https://support.twilio.com/hc/en-us/articles/223183008-Formatting-International-Phone-Numbers
​
https://en.wikipedia.org/wiki/List_of_country_calling_codes#Alphabetical_listing_by_country_or_region

Note that the above mainly talk about how a number is formatted, not on how
to validate that it is an actual phone number. You must trust the end user.
Which is another can of worms. Case in point -- Yesterday I got 6 automated
phone calls from the local cable company to verify some installation. The
problem is, I am not installing anything. The person either gave them a bad
number or mistyped it into a web page or the customer service rep mistyped
it.​

-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Microsoft buys GitHub, is this a threat to open-source

[John McKown]

On Mon, Jun 4, 2018 at 12:10 PM Michael Nolan  wrote:

> Microsoft has bought GitHub for $7.5 billion, is this a threat to the open
> source community?
>

​I don't know, but my intent is to abandon GitHub entirely. Too bad,
because I like to use it more to post "Gists" and send the URL to someone,
or a list, to show long code sequences (which won't fit easily in email).​



> --
> Mike Nolan
>


-- 
Once a government places vague notions of public safety and security above
the preservation of freedom, a general loss of liberty is sure to follow.

GCS Griffin -- Pelaran Alliance -- TFS Guardian (book)


Maranatha! <><
John McKown

Re: manipulating NUMERIC values in C extension

[John McKown]

On Fri, Jun 8, 2018 at 7:27 AM Geoff Winkless  wrote:

> Hi
>
> I'd like to be able to perform some manipulation on NUMERIC values in
> a C function; however the exposed functionality in numeric.h is pretty
> restrictive.
>
> I can see numeric_normalize will return a pointer to a string
> representation, which is workable, and if there were an equivalent
> string-to-numeric function that would be enough (although not the most
> efficient) but I can't see a way to get a string back in to a numeric
> value to return.
>
> numeric_in looks like it might do what I want but to do that I would
> have to build a FunctionCallInfo struct to do that, and I'm not 100%
> clear how to do that either :(
>
> I _could_ return the result as a varchar and cast it back to numeric
> in the SQL, but that's not very clean.
>
> Accessing the numeric structure directly would work too but I'm
> assuming that's not recommended since it's not exposed in numeric.h.
>
> Any thoughts would be appreciated,
>

Have you considered the standard C library functions: "atoi()", "atof()",
"atol()", and "atoll()" ?



>
> Geoff
>
>

-- 
Rap music is performed by those that can not sing so others can not think.

Maranatha! <><
John McKown

Re: unorthodox use of PG for a customer

[John McKown]

On Fri, Aug 24, 2018 at 1:19 PM David Gauthier 
wrote:

> Hi Everyone:
>
> I'm going to throw this internal customer request out for ideas, even
> though I think it's a bit crazy.  I'm on the brink of telling him it's
> impractical and/or inadvisable.  But maybe someone has a solution.
>

Reading below, I think you're right about it being inadvisable. It seems to
me that your user, like most, just has "needs" and doesn't really want to
worry about "details" or much of anything else. We have programmers where I
would with this attitude: We list what we want -- you supply it and
maintain it. Because we only code (we don't even design much because we're
AGILE! ), we don't worry about those little details (availability,
reliability, security -- not our concern!). One thing missing from your
post is the OS involved. Linux? Windows? Other? Multiple different ones?
E.g. some users are Linux while others are Windows. {ouch}.



>
> He's writing a script/program that runs on a workstation and needs to
> write data to a DB.  This process also sends work to a batch system on a
> server farm external to the workstation that will create multiple, parallel
> jobs/processes that also have to write to the DB as well. The workstation
> may have many of these jobs running at the same time.  And there are 58
> workstation which all have/use locally mounted disks for this work.
>

First question. You say DB. Do you need an SQL based data base. Or do you
just need a shared data store which is easy to use in a script. Have you
considered any NOSQL type data stores such as CouchDB, MongoDB

Second question, which scripting language and what programming language?
Examples might be: Windows Powershell, Linux BASH, Windows BASH, Python,
Perl, surely not some AWK variant, R, Google GO, Rush, C/C++, and so on.



>
> At first blush, this is easy.  Just create a DB on a server and have all
> those clients work with it.  But he's also adamant about having the DB on
> the same server(s) that ran the script AND on the locally mounted disk.  He
> said he doesn't want the overhead, dependencies and worries of anything
> like an external DB with a DBA, etc... . He also wants this to be fast.
>

So, if I understand, he wants a single shared data store for 58 clients. He
also wants the disk holding the data to be "locally mounted" to every
workstation. Depending on what "locally mounted" means to the user, I only
way that I know of to do something like this is to have the actual disk /
filesystem directly attached to a single server. All the workstations would
need to use a communication protocol (IP?) to communicate their I/O to the
"shared data" to this server to do the physical I/O. So we're talking some
protocol like NFS or CIFS (Windows "share"). The easiest way that I know to
do this sharing is to have a NAS box, such as NetApp, which really is a
server+disk "in a box" and which implements these protocols in an easy to
manage manner.

Or can each workstation have a separate, unrelated data store for its
processing. The thought then would be some way to have the application
write locally into its data store. Something else, perhaps "in line", would
replicate the data to a central store on a server. That server would then
distribute the changes back out to all 58 workstations and ??? servers so
that each will have, eventually, an identical copy of the current data; but
the stores might have inconsistencies until they synchronise.



>
> My first thought was SQLite.  Apparently, they now have some sort of
> multiple, concurrent write ability.  But there's no way those batch jobs on
> remote machines are going to be able to get at the locally mounted disk on
> the workstation. So I dismissed that idea.  Then I thought about having 58
> PG installs, one per workstation, each serving all the jobs pertaining to
> that workstation.  That could work.  But 58 DB instances ?  If he didn't
> like the ideal of one DBA, 58 can't be good.  Still, the DB would be on the
> workstation which seems to be what he wants.
>
> I can't think of anything better.  Does anyone have any ideas?
>

Yes, but the civil authorities would prosecute you if they found the body.



>
> Thanks in Advance !
>
>

-- 
Between infinite and short there is a big difference. -- G.H. Gonnet

Maranatha! <><
John McKown

Re: CRecordset::Open postgresql procedure call don't work

[John McKown]

On Mon, Jul 15, 2019 at 6:40 AM jeanclaude marzin 
wrote:

> ‌Hi
> I migrate from MSSQL to postgresql 11. I translate MSSQL stored procedure
> to Postgresql one :
>
> CREATE PROCEDURE procacp ()
> LANGUAGE SQL
> AS $$
> SELECT tabjdbexploit.jdbeid, tabjdbexploit.jdbeproc,
> tabjdbexploit.jdbedate, tabjdbexploit.jdbetypemsg,
> tabjdbexploit.jdbeurg, tabjdbexploit.jdbeconfid,
> tabjdbexploit.jdbeetat, tabmsgacp.acpid,
> tabmsgacp.acpnumserie,
> tabmsgacp.acpsignserv, tabmsgacp.acpnumligne,
> tabmsgacp.acpiaorigine, tabmsgacp.acpgdh,
> tabmsgacp.acperv,  tabmsgacp.acpcdu,
> tabmsgacp.acpdir, tabmsgacp.acppere,
> tabmsgacp.acpcomplement, tabmsgacp.acpsection
>
> FROM tabjdbexploit INNER JOIN
> tabmsgacp ON tabjdbexploit.jdbeid = tabmsgacp.acpid
>
> ORDER BY tabjdbexploit.jdbedate ASC
> $$;
>
>
> All seems OK in PgAdmin 4, procedure is created
>
> I use ODBC and Crecorset in C++
>
> When i use the call strSQL = "{CALL procacp()}". It don't work :
>
> Message error : ERROR: procacp() is a procedure
>
> I don't know what to do
>
> Thanks in advance for any assistance
>
> Jean-Claude


I am fairly sure that you need to make an FUNCTION and not a PROCEDURE.
Functions return values. Procedures do not. Why not try replacing the word
PROCEDURE with FUNCTION and give it another try?

ref: https://www.postgresql.org/docs/11/xfunc.html


-- 
We all agree on the necessity of compromise. We just can't agree on when
it's necessary to compromise. -- Larry Wall

Maranatha! <><
John McKown

Re: copy command - something not found

[John McKown]

Can't really tell. You might want to post the output of the "printenv"
command to show us what the shell variables  you are using have in them.
Oh, does ${CSVPATH} end in a slash? If it is something like "~/mycsvs" then
${CSVPATH}copycmd.z will expand to "~/mycsvscopycmd.z". Most  "PATH"
environment variables  don't end in a /, perhaps you need
"${CSVPATH}/copycmd.z" ? Also, as an aside. most UNIX files which end in .z
are compressed, IIRC.  Make sure the contents of the file  are plain text.

On Tue, Dec 29, 2020 at 1:12 PM Susan Hurst 
wrote:

> I am trying to use the copy command from a csv files using a UNIX shell
> script but something is 'not found'...I can't figure out what is 'not
> found'. Below is my command from the shell script, the executed command,
> the content of the csv file and the output from trying to execute the
> command.
>
> Clearly, the file is being read but I can't figure out what is not
> found. BTW, the column names in the stg.bar_active table match the names
> and order in the first row of the csv file. What should I be looking
> for?
>
> Thanks for your help!
>
> Sue
>
> ##-- shell script command
> psql -d ${DBNAME} -U ${DBNAME} -h ${HOSTNAME} -c < ${CSVPATH}copycmd.z
>
> ##-- executed command
> "copy stg.bar_active from
> '/home/dbzone/stp/rfc_db/RFC-1/RFC-1_copy_bar_active.csv'
> delimiter ',' CSV HEADER;"
>
> ##-- content of .csv file
> schema_name,table_name,table_alias...(this is the header
> row)
> chief,source_systems,ssys
> chief,lookup_categories,lcat
> chief,lookup_data,ldat
>
> ##-- output from terminal window
> /home/dbzone/stp/rfc_db/RFC-1/RFC-1_copy_bar_active.csv:
> schema_name,table_name,table_alias: not found
> /home/dbzone/stp/rfc_db/RFC-1/RFC-1_copy_bar_active.csv:
> chief,source_systems,ssys: not found
> /home/dbzone/stp/rfc_db/RFC-1/RFC-1_copy_bar_active.csv:
> chief,lookup_categories,lcat: not found
> /home/dbzone/stp/rfc_db/RFC-1/RFC-1_copy_bar_active.csv:
> chief,lookup_data,ldat: not found
>
> ##-- select version();
> PostgreSQL 11.9 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang
> version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1),
> 64-bit
>
> --
> 
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hu...@brookhurstdata.com
> Mobile: 314-486-3261
>
>
>

Re: Error updating column of type text as boolean type

[John McKown]

On Wed, Jul 3, 2019 at 10:09 AM Marllius  wrote:

> Hi guys, I was looking datatype bugs for postgresql 10.6 but i not found
> anything.
>
> I am trying to update the column of type text column but i get an error
> for boolean type. In other tables the behavior of the text type column is
> normal.
>
> Has anyone had this problem before?
>
> accounting@accounting=> \dS+ stock_asset_document
>Table
> "public.stock_asset_document"
>
>
>
>
>
>
>
>
>
>
>
>
> * Column |   Type   | Collation | Nullable |
>  Default| Storage  | Stats target |
> Description
> +--+---+--+---+--+--+-
>  id
> | bigint   |   | not null |
> nextval('seq_stock_asset_document'::regclass) | plain|  |
>  tenant_id  | bigint   |   | not null |
>   | plain|  |
>  registry_id| uuid |   | not null |
>   | plain|  |
>  revision_id| uuid |   | not null |
>   | plain|  |
>  negotiation_id | bigint   |   | not null |
>   | plain|  |
>  competence_at  | date |   | not null |
>   | plain|  |
>  is_deleted | boolean  |   | not null |
>   | plain|  |
>  created_at | timestamp with time zone |   | not null | now()
>   | plain|  |
>  updated_at | timestamp with time zone |   |  |
>   | plain|  |  number
>   | bigint   |   |  |
> | plain|  |  serial |
> text |   |  |
> | extended |  | *
> Indexes:
> "pk_stock_asset_document" PRIMARY KEY, btree (id)
> "uk_stock_asset_document_registry_revision" UNIQUE CONSTRAINT, btree
> (registry_id, revision_id)
> "ix_stock_asset_document_tenant_deleted" btree (tenant_id, is_deleted)
> "ix_stock_asset_document_tenant_registry_revision_deleted" btree
> (tenant_id, registry_id, revision_id, is_deleted)
> Referenced by:
> TABLE "stock_asset" CONSTRAINT
> "fk_stock_asset_entry_stock_asset_document" FOREIGN KEY (entry_document_id)
> REFERENCES stock_asset_document(id) ON UPDATE RESTRICT ON DELETE RESTRICT
> TABLE "stock_asset" CONSTRAINT
> "fk_stock_asset_output_stock_asset_document" FOREIGN KEY
> (output_document_id) REFERENCES stock_asset_document(id) ON UPDATE RESTRICT
> ON DELETE RESTRICT
>
> accounting@accounting=> UPDATE stock_asset_document SET serial = '3' AND
> number = 36245 WHERE negotiation_id = 15948333;
>

Don't use AND. Use a comma:

UPDATE stock_asset_document SET serial = '3', number = 36245 WHERE
negotiation_id = 15948333;

ref: https://www.postgresql.org/docs/11/sql-update.html




> ERROR:  22P02: invalid input syntax for type boolean: "3"
> LINE 1: UPDATE stock_asset_document SET serial = '3' AND number = 36...
>  ^
> LOCATION:  boolin, bool.c:154
> Time: 16.427 ms
> accounting@accounting=> SELECT version();
>  version
>
>
> -
>  PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
> (1 row)
>
> Time: 15.989 ms
>
> Atenciosamente,
>
> Márllius de Carvalho Ribeiro
> [image: EDB Certified Associate - PostgreSQL 10]
> <https://www.youracclaim.com/badges/c8dd0919-86ca-4cec-9f58-5cd6fb558a91/public_url>
>
>

-- 
Money is the root of all evil.
Evil is the root of all money.
With that in mind, money is made by the government ...


Maranatha! <><
John McKown

Re: Why can't lseek the STDIN_FILENO?

[John McKown]

My best advice would be to ask a C language question on a C language forum.
This forum is really only for questions about the SQL language for the
PostgreSQL database. I.e. no MariaDB, MySQL, MS SQL questions.

First, you didn't say what OS and she'll you're using. I an guessing BASH
and Linux.

Second, you did NO error checking. I would purely guess that the lseek() is
getting a return value of -1, probably with an error of ESPIPE.

This is probably a better explanation:
https://unix.stackexchange.com/questions/502518/problems-when-test-whether-standard-input-is-capable-of-seeking

The bottom line from the above post is that STDIN is not seekable when it
is a terminal.

On Fri, Jun 23, 2023, 21:17 Wen Yi <896634...@qq.com> wrote:

> Hi community,
> I am testing the lseek & write & read, and I write the code like this:
>
> /*
> lseek_test.c
> Test the lseek
> Wen Yi
> */
> #include 
> #include 
> int main()
> {
> int fd = 0;
> char buffer[16] = {};
> write(STDIN_FILENO, "Hello world\n", sizeof("Hello world\n"));
> lseek(STDIN_FILENO, 0, SEEK_SET);
> read(STDIN_FILENO, buffer, sizeof(buffer));
> write(STDIN_FILENO, buffer, sizeof(buffer));
> return 0;
> }
>
> And I run the program ("Something Input" is my input content)
>
> [beginnerc@bogon 学习 C语言]$ gcc lseek_test.c
> [beginnerc@bogon 学习 C语言]$ ./a.out
> Hello world
> Something Input
> Something Input
> [beginnerc@bogon 学习 C语言]$
>
> I really don't know, why the buffer's content not be "Hello world\n"? (I
> use the lseek to move the cursor to the beginning region)
>
> Can someone give me some advice?
> Thanks in advance!
>
> Yours,
> Wen Yi
>

Re: Fwd: Receipt for PostgreSQL US Invoice #1840

[John McKown]

I filed it under SPAM. As I do most things with a PDF or other attachment
that looks even the slightest iffy. If I had my way, such people would be
impaled.

On Tue, Mar 12, 2024, 21:27 Adrian Klaver  wrote:

> On 3/12/24 19:14, Christophe Pettus wrote:
>
> Oops?
>
> >
> >
> >> Begin forwarded message:
> >>
> >> *From: *Sadie Bella  >> >
> >> *Subject: **Fwd: Receipt for PostgreSQL US Invoice #1840*
> >> *Date: *March 12, 2024 at 19:13:40 PDT
> >> *To: *Christophe mailto:x...@dvvent.com>>
> >>
> >>
> >> -- Forwarded message -
> >> From: mailto:treasu...@postgresql.us>>
> >> Date: Tue, Mar 12, 2024, 7:07 PM
> >> Subject: Receipt for PostgreSQL US Invoice #1840
> >> To: mailto:bellasadie@gmail.com>>
> >>
> >>
> >> Hello!
> >>
> >> We have received your payment for invoice #1840:
> >> PostgreSQL US membership for bellasadie@gmail.com
> >> 
> >>
> >> You will find your receipt for this payment in the attached file.
> >>
> >> Thank you!
> >>
> >> PostgreSQL US
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
>

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[John McKown]

On Tue, Dec 12, 2017 at 2:17 AM, Tsunakawa, Takayuki <
tsunakawa.ta...@jp.fujitsu.com> wrote:

> Hi Laurenz, Tom, Peter,
>
> Thanks for your suggestions.  The practical solution seems to be to
> override comparison operators of char, varchar and text data types with
> UDFs that behave as Tom mentioned.
>
> From: Peter Geoghegan [mailto:p...@bowt.ie]
> > That said, the idea of an "EBCDIC collation" seems limiting. Why
> > should a system like DB2 for the mainframe (that happens to use EBCDIC
> > as its encoding) not have a more natural, human-orientated collation
> > even while using EBCDIC? ISTM that the point of using the "C" locale
> > (with EBDIC or with UTF-8 or with any other encoding) is to get a
> > performance benefit where the actual collation's behavior doesn't
> > matter much to users. Are you sure it's really important to be
> > *exactly* compatible with EBCDIC order? As long as you're paying for a
> > custom collation, why not just use a collation that is helpful to
> > humans?
>
> You are right.  I'd like to ask the customer whether and why they need
> EBCDIC ordering.
>

​This is a guess on my part, based on many years on an EBCDIC system. But
I'll bet that they are doing a conversion off of the EBCDIC system (maybe
Db2 on z/OS) to an ASCII system (Linux or Windows) running PostgreSQL. They
want to be able to compare the output from the existing system to the
output on the new system. EBCDIC orders "lower case", "upper case", then
"digits". The default C locale on Linux (I don't know Windows) will sort
"digits", then alphabetic with the lower then upper case of each letter in
order like: "aAbB...zZ". Comparing identical data which is not presented in
exactly the same order would be very difficult. ​



>
> Regards
> Takayuki Tsunakawa
>
>


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[John McKown]

On Tue, Dec 12, 2017 at 9:11 AM, James Keener  wrote:

> The default C locale on Linux (I don't know Windows) will sort "digits",
>> then alphabetic with the lower then upper case of each letter in order
>> like: "aAbB...zZ"
>>
>
> That's no true at all! The C locales are 0-9A-Za-z
>

​Thanks for the correction. Turns out that I forgot that my default locale
on Linux was en_US.utf8.​


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[John McKown]

On Tue, Dec 12, 2017 at 9:43 AM, James Keener  wrote:

> Sorry for spamming the list. It appears that I'm an idiot. Sorry :(
>

​I guess we're even now. We both made a similar mistake.​

​But, despite my error, I still think the OP's need for an EBCDIC order is
to compare output from parallel runs of an application from both an EBCDIC
host and and ASCII host, to make sure they are "the same".

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: Getting started with first user.

[John McKown]

On Tue, Jan 9, 2018 at 3:48 AM, Agnar Renolen 
wrote:

> I have just installed PostGIS (Postgres9.6) on a Debian server using
> apt-get.
>
> But I have problems doing anything:
>
> I installed as root, but trying doing things as my local user "me"
>
> me> createuser me
> createuser: could not connect to database postgres: FATAL: role "me" does
> not exist
>
> Then, trying the same as root, but gettinge the same result.
>
> root> createuser me
> createuser: could not connect to database postgres: FATAL: role "root"
> does not exist
>
> Then trying with the -U postgres option.
>
> root> createuser -U postgres me
> createuser: could not connect to database postgres: FATAL: Peer
> authentication failed for user "postgres"
>
> How do I get started?
>

​It's been a long time for me, but as I recall, I did something like:

me$ sudo su - # change to root
root# su - postgres # change to postgres user
postgres$​ createuser -s me #create "me"
postgres$ exit # go back to root
root# exit # go back to me
me$ createdb me # create my default database

In /var/lib/pgsql/data/pg_hba.conf , I have lines like:

# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
# IPv6 local connections:
hostall all     ::1/128 trust






-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: Notify client when a table was full

[John McKown]

On Mon, Jan 22, 2018 at 2:07 AM, Steve Atkins  wrote:

>
> > On Jan 21, 2018, at 6:44 PM, Vincenzo Romano <
> vincenzo.rom...@notorand.it> wrote:
> >
> > 2018-01-21 19:31 GMT+01:00 Francisco Olarte :
> >> On Sun, Jan 21, 2018 at 1:27 PM, Michael Paquier
> >>  wrote:
> >>> On Fri, Jan 19, 2018 at 03:40:01PM +, Raymond O'Donnell wrote:
> >> ...
> >>>> How do you define "full"?
> >
> > The only possible and meaningful case, IMHO, as stated by David
> > earlier, is "file system full".
>
> If your filesystem is full you're pretty much off the air. It's something
> that should never happen on a production system. So ... any automation
> around "the filesystem is full" is going to be much the same as "the server
> is dead". You're unlikely to be able to do anything useful from the
> app at that point, let alone from a trigger function.
>

​Well, I'll agree that PostgreSQL is likely "off the air". On my system
(Linux/Intel), I use quotas to restrict​ a user's use of disk space. Yes,
even the PostgreSQL user. I also, at times, use a separate filesystem for a
database and use a TABLESPACE for the tables within it. This is easy to do
with Linux because I can create a filesystem in a regular disk file. It
doesn't perform as well as "native", but my system is not a highly used,
performance oriented, system. Use of a separate filesystem and tablespaces
is, I think, a decent way to control disk usage for "something" so that if
"something" goes "insane", it can't really impact "others" very much. Of
course, others may reasonably disagree with me on this. Each cat, his own
rat.



>
> If the answer involves handling the case where the file system is full
> we're
> not answering a useful question, and the original poster probably needs to
> clarify.
>
> > Which is communicated by Postgres with the "Class 53 — Insufficient
> > Resources" error codes.
> > Please refer to official documentation like:
> >
> > https://www.postgresql.org/docs/10/static/errcodes-appendix.html
> >
> > For specific programming languages more details need to be checked.
> >
>
> Cheers,
>   Steve
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: License question regarding distribution of binaries

[John McKown]

On Sun, Jan 21, 2018 at 7:40 AM, Rafał Zabrowarny <
rafal.zabrowa...@skyrise.tech> wrote:

> Hi,
>
>
>
> My name is Rafał and I would like prepare lib to setup and run Pg within
> integration tests.
>
> To do it I would like to  prepare on Windows nuget package containing
> necessary Pg’s binaries. I would like to keep it in separate folder with
> enclosed Pg’s license. It would be distributed in 1 package with rest of
> the lib.
>
>
>
> I would like to know if this is allowed in terms of law enforcement ? Is
> distributing Pg’s binaries in such a form doesn’t violated license ?
>
> Next quest I have is it possible to remove unneeded Pg’s binaries and only
> distribute part of it (for instance pg_ctl, includes folder and so for) ? I
> Would like to distribute minimal set of binaries that are needed to run Pg.
>
>
>
>
>
> TIA for respond
>

​First and foremost: If you want a _legal_ opinion, ask a lawyer. I'm not a
lawyer!

Now, in the spirit of trying to be helpful, I think the following should
answer your question:

https://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F



What is the license of PostgreSQL?

PostgreSQL is distributed under a license similar to BSD and MIT.
Basically, it allows users to do anything they want with the code,
including reselling binaries without the source code. The only restriction
is that you not hold us legally liable for problems with the software.
There is also the requirement that this copyright appear in all copies of
the software. Here is the license we use:

...

​


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown