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

[Rich Shepard]

On Mon, 4 Jun 2018, Joshua D. Drake wrote:


No but it does show why using non open source platforms for open source
projects is an inherently bad idea.


Joshua,

  Sourceforge seems to be out of favor, too,  so are there any open source
platforms that provide services that sourceforge and github do?

Rich

Re: Open Source tool to deploy/promote PostgreSQL DDL

[Rich Shepard]

On Tue, 10 Jul 2018, Hustler DBA wrote:


A client of mine is looking for an open source tool to deploy and promote
PostgreSQL DDL changes through database environments as part of SDLC. What
tools (open source) does the community members use? I normally use
scripts, but they want something open source.


Neil,

  I'm far from a professional DBA, but scripts are certainly open source
because they're text files.

  To track changes for almoste everything I highly recommend Git for version
control. It's distributed and can handle most types of files. I use it for
tracking coding projects and well as report and other text documents that
are edited and revised prior to release.

Rich

Re: Recomended front ends?

[Rich Shepard]

On Wed, 7 Aug 2019, Igor Korot wrote:


On top of what already been said - make sure that the product you are
about to start working on will have its requirements clear and concise.


This is a critical process that needs to be developed in depth. One
criterion that will guide your choice of UI is whether the database will be
accessed only on the LAN or also remotely. For the former, consider using
Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based
application using Django.

HTH,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Stuart McGraw wrote:


I would be a little cautious about Django.



Specifically IIRC it insists that tables have a single-column primary
keys.


Stuart,

I looked seriously at Django and did not encounter that limitation. However,
I did learn that I'm not a web application developer nor do I want to be.
The applications I develop, primarily for my own business needs. use
SQLAlchemy and that allows multi-column primary keys. That's a necessity for
many-to-many tables (or SA classes).

I suspect that Django also allows multi-column primary keys but the syntax
might not be obvious.

Regards,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Adrian Klaver wrote:


Unfortunately it does not:
https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys

Given that the issue:
https://code.djangoproject.com/ticket/373
is 14 years old does not inspire confidence that it will change anytime soon.


Adrian,

That's really interesting. I don't see how a framework cannot implement
multi-column PKs.

Many databases I have include tables for samples (geochemical, biological,
physical) where the PK for each row is location, date, parameter. Good thing
I don't like browser user interfaces, eh? :-)

Thanks for the information,

Rich

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Tim Clarke wrote:


We tried Django without any pleasant results.


Tim,

That's unexpected and too bad.


I'd also caution using MS Access, we're desperate to get away from it.
Sharing code has challenges and it is horribly aggressive with caching
unless you use un-bound forms and write all the CRUD interface code
yourself.


Slightly off-topic, but I've not seen anything good about Access. My
understanding is it's a flat-file database intended as a user front end to
Microsoft's relational database product. My experiences with those who use
it have been painful.

Just yesterday I downloaded a very large database of fisheries data from a
federal agency and have started translating it to postgres using the
mdbtools. There's no schema provided, only 32 pages of table columns and
types without descriptions of the column names. No primary keys, no foreign
keys, and only 66 tables were found in the .mdb file while all table names
starting with s through z were not available. There are also many tables
that hold redundant data which should not exist as the contents are easily
generated by SQL queries. It will take me a while to make it a working
relational database.

Rich

Converting Access .mdb to postgres

[Rich Shepard]

I have the need to convert a flat-file Access database to postgres. I've
exported the .mdb tables using mdbtools. There's an accompanying metadata
PDF with column names and data types for each of the 84 tables, but no
description of the tables or column headings. I've asked the agency to
provide that information ... if they have it. No table has primary or
referential keys and, of course, there's no E-R diagram of the schema.

If anyone's done this I'd appreciate learning from your experiences. And I
assume there are no tools to automate all or part of the process so it must
be done manually.

Among the tables are many lookup tables. I don't know whether to leave them
as tables or apply a different structure to them.

Advice, suggestions, and recommendations are all welcome.

TIA,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


So you have the tables in Postgres, correct?


Adrian,

Not yet. I have the schema extracted using mdb-schema.


I did something similar with the USDA Nutrient database(with notion of
making it a test dataset): https://ndb.nal.usda.gov/ndb/doc/index


This is from streamnet.org. They're part of the Pacific States Marine
Fisheries Commission and it's a database of all fish-related information
within the Columbia River basin.


Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information I
guessing you will have to create your own method of dealing with it. Don't
count on the data being consistent.


At the federal level cooperation is spotty. Several years ago I downloaded
the International Taxonomic Identification System (ITIS) database (the
internationally accepted list of names -- scientific and common -- for all
animals (perhaps plants, too) in mysql format (I think that was the format)
and my contact there asked for the postgres database when done. I sent him
the dump_all output and he was happy. State level is more open.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Roger De Four wrote:


The easy way - just send it to me.

The more challenging way requires using several tools like excel or Open
Office.


Roger,

I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.

When I ran mdb-export I seleted postgres output. The tables are all insert
into statements of the data. I don't know that LibreOffice would handle
files of 112M (the size of one table as downloaded from their web site), but
emacs had no issues with it. 'Excel' or any other proprietary application is
not an option for me. I defenestrated early in 1997 and run only linux.

Will post the URL for the tarball Real Soon Now.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc


Adrian,

Not yet, but I will. Didn't see it when I went to the data pages.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.



Will post the URL for the tarball Real Soon Now.


Here it is for anyone interested: <https://tinyurl.com/yyzuhrcg>.

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc


Thanks, Adrian. This looks like it has all the information I need.

Under which menu did you find this? I had looked in the Data and Resources
menus and searched for 'data exchange' without finding it.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Michael Nolan wrote:


It seemed like for every rule I tried there were a handful of exceptions.
We wound up just rewriting the app and not trying to export the data from
the previous one.


Mike,

This is not surprising. My interest is in the data, not the application.
Much of my consulting practice involves fish distributions, abundances, and
habits. The streamnet database has always been a comprehensive resource for
this information within the Columbia River basin.

Regards,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Roger De Four wrote:


Rich - This is the ERD for the Access db you posted


Thanks, Roger. The exchange format document Adrian found also has an E-R
diagram.

Much appreciated,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Adrian Klaver wrote:


Here:
https://www.streamnet.org/data/downloadable-data/
Link: Data Exchange Standard (DES).


Adrian,

Then I missed it when I was in that subdirectory.

Thanks,

Rich

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


Thanks, Adrian. This looks like it has all the information I need.


Adrian,

Off the mail list.

I'm reading that metadata document and it seems to me that it's not well
constructed. Perhaps this is an Access thing[1]; perhaps the DBA's have
limited knowledge. For example there are a lot of entity1_X_entity2 tables
which I think don't need to exist as one can get the same information from a
query. And, there are a bunch of lookup tables I think could be better
handled the postgres way.

Would you help me with this migration off the mail list?

Regards,

Rich

[1] I tried searching the database tables on their web site but could not
find the information I need. As far as I know Access is a flat-file
database, not relational.

Re: Converting Access .mdb to postgres

[Rich Shepard]

On Wed, 14 Aug 2019, Rich Shepard wrote:


Off the mail list.


Sorry all. I thought it went to only Adrian.

Rich

Re: Variable constants ?

[Rich Shepard]

On Thu, 15 Aug 2019, stan wrote:


I need to put a few bossiness constants, such as a labor rate multiplier
in an application. I am adverse to hard coding these things. The best plan
i have come up with so far is to store them in a table, which would have
only 1 row, and a column for each needed constant.

Anyone have a better way to do this?

Failing a better way is there some way I can limit this table to only
allow one row to exist?


Stan,

I've resolved similar issues with changing regulatory agency staff. For your
application(s) I suggest a table like this:

create table labor_rate_mult (
  rate  real primary_key,
  start_datedate not null,
  end_date  date
)

This provides both a history of labor rate multipliers and the ability to
select either the most current one or a previous one.

If other factors affect the rate, add attribute columns for them.

Regards,

Rich

Re: Variable constants ?

[Rich Shepard]

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:

Another way to keep a history is using a daterange instead of two columns for 
start and end date. Something like


create table labor_rate_mult (
 rate   real primary_key,
 validity   daterange not null
)


Charles,

Just out of curiosity, what is the range for a rate that is still current?
Does it change every day?

Regards,

Rich

Re: Variable constants ?

[Rich Shepard]

On Fri, 16 Aug 2019, Charles Clavadetscher wrote:


That would be a range with an empty upper bound. Let's say that the rate
is valid since 2019-08-14 then the range would look like
[2019-08-14,)
A query to find the current rate would look like:

SELECT rate
FROM labor_rate_mult
WHERE validity @> CURRENT_DATE;

Here you can find documentation on the range types (cool stuff I believe):
https://www.postgresql.org/docs/11/rangetypes.html


Charles,

Interesting. Certainly worth considering the next time an application needs
a range of dates.

Regards,

Rich

Selecting rows having substring in a column

[Rich Shepard]

Using postgres-11.1 here. My SQL knowledge needs expanding and my web
searches have not found a satisfactory answer. I hope to learn the correct
approach here.

A table (Fishes) has an attribute column stream_trib with values such as
Small Creek trib to Winding River
Roaring River trib to Winding River
and I want to find all rows containing Winding River in that column.

The postgres substring function takes as arguments the substring, starting
position, and length. In my table the staring position varies although the
length remains constant.

I need to learn how to construct a SELECT statement that returns the set of
rows containing the substring 'Winding River'. A pointer to references would
be great; so would a detailed lesson in handling this and similar queries.

Regards,

Rich

Re: Selecting rows having substring in a column [RESOLVED]

[Rich Shepard]

On Thu, 29 Aug 2019, Adrian Klaver wrote:


test=# select * from like_test where  fld_1 ilike '%Winding River%';
   fld_1


Adrian,

Aha! I thought of 'like' but forgot about ilike. That's exactly what I need.

Thanks very much,

Rich

Re: Selecting rows having substring in a column

[Rich Shepard]

On Thu, 29 Aug 2019, Gary Cowell wrote:


Look at the 'LIKE' function


Gary,

Yes, I thought of like but didn't think to look for it in the postgres
manual.

Thank you very much,

Rich

Re: Selecting rows having substring in a column [RESOLVED]

[Rich Shepard]

On Thu, 29 Aug 2019, Rich Shepard wrote:


Aha! I thought of 'like' but forgot about ilike. That's exactly what I
need.


'thought' is the wrong word. I should have written that I once knew of like
and had forgotten it.

Rich

Query using 'LIKE' returns empty set

[Rich Shepard]

Next problem is one I've not before encountered.

The .sql file used to import data to the fish_counts table has rows such as
this one:

('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to North
Fork Nehalem River','0-3.25','161980','Unknown','Jack or subadult','Peak
live & dead fish','Spawner Counts','ODFW','2012-01-06','1950-1974',25,
'1950-10-01','1951-01-31','Ground','Actual Physical Counts',0), 
[Lines wrapped in the message only.]


When I submit this query I get no rows returned:

select * from fish_counts where stream_tribs ilike 'Nehalem';
 count_id | loc_id | downstream | upstream | stream_name | stream_tribs | r
iver_miles | itis_tsn | production | life_stage | count_type | data_categor
y | compiled_by | updated | years | nbr_observations | begin_date | end_dat
e | sample_method | calc_method | count_value 
--+++--+-+--+--

---+--++++-
--+-+-+---+--++
--+---+-+-
(0 rows)

What is equally puzzling is when I search the input file using
grep -c -e "Nehalem" fish_counts
0 is returned, the same as the postgres query.

I want to understand what I've done incorrectly.

TIA,

Rich

Re: Query using 'LIKE' returns empty set [FIXED]

[Rich Shepard]

On Thu, 29 Aug 2019, Rob Sargent wrote:


Are you sure that particular file has the search string?


Rob,

I'm suitably embarrased: that's the wrong file name. I must be seriously
under cafinated. The proper file is fish_data.sql so grep and postgres
return 1409 instances.

My apologies to all,

Rich

Escape parentheses in aggregate function

[Rich Shepard]

I want to copy query results to a text file and there's an aggregate
function in the SELECT expression. One of the aggregate function's
parentheses seems to end the \copy() function and I don't know how best to
write the statement. A minimal example:

\copy(select count_value, sum(count_value)
from table_name) to 'output_file.txt';

Do I write sumE'('count_valueE')', sum\(count_value\), or something else?

TIA,

Rich

Re: Escape parentheses in aggregate function

[Rich Shepard]

On Fri, 30 Aug 2019, Tom Lane wrote:


I think your problem is the line break, not the parentheses. psql knows
how to count parens, but it has no concept of letting backslash commands
continue across lines.


Tom,

Interesting. I've adopted separating select statements by their expressions
as it makes it easier for me to get it right, or fix it. However, I can put
it all on a single line once I know the syntax is correct and I add the
\copy() function.

Thanks,

Rich

Re: Escape parentheses in aggregate function

[Rich Shepard]

On Fri, 30 Aug 2019, Tom Lane wrote:


FWIW, if you're running a current release then there's a reasonable
alternative for writing multi-line COPY-from-query commands; see commit
log below.


Thanks, Tom. I'm running 11.1 and plan to upgrade to 11.5 this weekend.

Regards,

Rich

Aggregate functions on groups

[Rich Shepard]

Tables hold data on fish counts by stream name, species, and (unreported)
collection dates. I'm trying to write a query that returns the total number
of each species in each stream.

The latest attempt is (lines wrapped by alpine; submitted as one line):

\copy (select f.stream_tribs, f.count_value, sum(f.count_value),
i.common_name, i.sci_name  from fish_counts as f, itis as i where
f.stream_tribs like '%Nehalem River%' group by f.stream_tribs,
i.common_name, i.sci_name, f.count_value  order by f.stream_tribs,
i.common_name, i.sci_name, f.count_value) to
'/home/rshepard/projects/oregon/mohler_sand/data/fish/fishes.dat';

The returned set starts this way:

Anderson Creek trib to Nehalem River0   0   Black crappie   Pomoxis 
nigromaculatus
Anderson Creek trib to Nehalem River3   3   Black crappie   Pomoxis 
nigromaculatus
Anderson Creek trib to Nehalem River0   0   BluegillLepomis 
macrochirus
Anderson Creek trib to Nehalem River3   3   BluegillLepomis 
macrochirus
Anderson Creek trib to Nehalem River0   0   Brook trout 
Salvelinus fontinalis
Anderson Creek trib to Nehalem River3   3   Brook trout 
Salvelinus fontinalis
Anderson Creek trib to Nehalem River0   0   Brown bullhead  
Ameiurus nebulosus
Anderson Creek trib to Nehalem River3   3   Brown bullhead  
Ameiurus nebulosus

What I want returned would look like this:

Anderson Creek trib to Nehalem River  Black crappie  Pomoxis nigromaculatus 3
Anderson Creek trib to Nehalem River  Bluegill   Lepomis macrochirus3
Anderson Creek trib to Nehalem River  Brook troutSalvelinus fontinalis  3
Anderson Creek trib to Nehalem River  Brown bullhead Ameiurus nebulosus 3

I've read the manual yet must have not seen the section explaining how to
apply aggregate functions to groups.

Thanks in advance,

Rich

Re: Aggregate functions on groups [RESOLVED]

[Rich Shepard]

On Fri, 30 Aug 2019, John W Higgins wrote:


You are grouping by count_value which means that you are asking the system
to return a row for each different count_value.


John,

I didn't realize this.


So if you remove the f.count_value from the select statement (not the
sum(f.count_value)) - and you remove f.count_value from the group_by and
order_by statements - you should get what you want


Aha. I thought I had to select f.count_value in order to obtain
sum(f.count_value); it's been a long time since I needed to do something
like this.


Something like

\copy (select f.stream_tribs, sum(f.count_value),
i.common_name, i.sci_name  from fish_counts as f, itis as i where
f.stream_tribs like '%Nehalem River%' group by f.stream_tribs,
i.common_name, i.sci_name  order by f.stream_tribs,
i.common_name, i.sci_name) to
'/home/rshepard/projects/oregon/mohler_sand/data/fish/fishes.dat';


Thanks very much!

Rich

Re: Aggregate functions on groups

[Rich Shepard]

On Sat, 31 Aug 2019, Morris de Oryx wrote:


Your tributaries and fish master tables make sense. If I read your code
right, you're grouping by too many columns. I flattened the data into a
survey table for this simple example:


Morris,

I'm still learning about postgres groups. My approach is to group on the
column of interest, then add more when psql tells me to do so.


select tributary,
  common_name,
  scientific_name,
  sum(count_value) as fish_seen,
  count(count_value) as observations_made

  from survey

  group by 1,2,3 -- The GROUP BY clause can use positions on the select
list, if you feel like typing less.


I will look more at this approach; at first glance it appears to address one
query but not all those needed.


But this is not why I'm answering. I'm responding as I wanted to make sure
that you're aware of the pg-similarity extension:
https://salsa.debian.org/postgresql/pg-similarity


Thanks for the URL. I'll definintely read about similarity.

Regards,

Rich

Updating data: confirmation and question

[Rich Shepard]

Before my old server died I ran pg_dumpall on the database there and copied
that to my new server. The old database was postgresql-10 and the new one is
postgresql-11.5.

Am I correct that I can run 'pgsql -f .sql' and overwrite the
existing databases with the newer data?

Checking the database names in both I see that I mistakenly used hyphens
rather than underscores in one database name. I can change that in the .sql
file but have not before learned how/whether I can change a database name
using psql. What's the best approach to changing the existing hypephenated
name?

TIA,

Rich

Re: Updating data: confirmation and question

[Rich Shepard]

On Sat, 26 Oct 2019, Adrian Klaver wrote:


That depends on how you ran pg_dumpall. For instance did you use -c?:


Adrian,

Yes. Always.


https://www.postgresql.org/docs/11/sql-alterdatabase.html
ALTER DATABASE name RENAME TO new_name


Ah, I should have looked. I apologize.

Rich

Re: Updating data: confirmation and question

[Rich Shepard]

On Sat, 26 Oct 2019, Adrian Klaver wrote:


Then the question is, do you really want to overwrite the new database?


Adrian,

I want to overwrite the old databases with the new .sql file. I _think_
there's only one database that's changed, but overwriting all won't hurt.

Regards,

Rich

Re: Updating data: confirmation and question

[Rich Shepard]

On Sat, 26 Oct 2019, Adrian Klaver wrote:


Just me, but to keep this from being a 'famous last words' moment I would
backup up the new(11.5) instance before proceeding.


Adrian,

Okay. That makes good sense. Will do a pg_dumpall on the 11.5 cluster.

When I'm actively working on a database I do a nightly dump (datestamped).

Thanks,

Rich

RE: Upgrade procedure

[Rich Shepard]

On Tue, 5 Nov 2019, Kevin Brannen wrote:


The manual or others here are more knowledgeable than I, but I believe that
for a "minor" upgrade, you can just swap out the code and restart PG. For
major upgrades, the PG server is going to have to come down as the underlying
files might be changed/transformed during the upgrade, ...


Files might change even with a minor upgrade. The few seconds it takes to
stop, upgrade, and restart the system prevents all errors due to
someone/something accessing the database while it's being upgraded.

Rich

Logging

[Rich Shepard]

Running Slackware-14.2/x86_64 and postgresql-11.5.

In /var/log/ are these files:

-rw-r- 1 postgres wheel   0 Nov 23 04:40 postgresql-11
-rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1
-rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz
-rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz
-rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz
-rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz
-rw-r- 1 postgres wheel 325 Nov  6 04:40 postgresql-11.6.gz
-rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz

I assume that they're an automatic backup that runs every 3-4 days. What's
backed up and where is this controlled?

I ask because I have a cron job that does a pg_dumpall each night at 11:30
pm. (It's a small installation for my business use so the files are not
excessive and I keep them for only short periods.)

Regards,

Rich

Re: Logging [RESOLVED]

[Rich Shepard]

On Wed, 4 Dec 2019, Stephen Eilert wrote:


Usually, this is done by logrotate or a similar mechanism in your system.
You’ll likely find that other logs in your system follow a similar
pattern, not just Postgresql.


Stephen,

Other logs, controlled by logrotate, rotate daily for a maximum of 4
backups.

I just checked /etc/logrotate.d/postgres and it was set at daily with rotate
7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential.

Thanks,

Rich

Re: Logging

[Rich Shepard]

On Wed, 4 Dec 2019, Adrian Klaver wrote:

Take a look at the logging section of postgresql.conf to see if Postgres is 
handing off to the system and logrotate


Adrian,

That conf file is in several places (different flavors). I'll check them
all.

Thanks,

Rich

Encoding/collation question

[Rich Shepard]

My older databases have LATIN1 encoding and C collation; the newer ones have
UTF8 encoding and en_US.UTF-8 collation. A web search taught me that I can
change each old database by dumping it and restoring it with the desired
encoding and collation types. My question is whether the older types make
any difference in a single-user environment.

Regards,

Rich

Re: Encoding/collation question

[Rich Shepard]

On Wed, 11 Dec 2019, Tom Lane wrote:


String comparisons in non-C collations tend to be a lot slower than they
are in C collation. Whether this makes a noticeable difference to you
depends on your workload, but certainly we've seen performance gripes that
trace to that.


Tom,

How interesting.


If your data doesn't require the larger character set of UTF8, then using
LATIN-any is going to offer some space savings (for non-ASCII characters)
plus minor performance benefits due to the lack of variable-width
characters. This is less significant than the collation issue, though, for
most people.


I doubt that my use will notice meaningful differences. Since there are only
two or three databases in UTF8 and its collation perhaps I'll convert those
to LATIN1 and C.

Thanks for the insights.

Regards,

Rich

Counting number of sites with same number of sampling dates

[Rich Shepard]

A sampling location table has 28 distinct sites, each site being sampled
from 1 to 67 times. I'm trying to obtain the number of sites having 1
sample, 2 samples, ... 67 samples and am not seeing the solution despite
several alternative queries.

The query,

select site_nbr, count(distinct sampdate) from wrb_hg_cong group by site_nbr 
order by site_nbr;

returns the number of times each site has been sampled, for example:

site_nbr | count 
--+---

 10332|11
 10335| 1
 10339| 2
 10340| 1
 10342| 4
 10344|18
 10347| 2
 10348| 2
 10350| 2
 10351| 1
 10355|14
etc.

I want the number of sites for each number of samples (e.g., how many sites
with one sampdate, 2 sampdates, etc.). I cannot group by an aggregate such
as count(distinct sampdate) yet I'm confident the syntax is simple and I'm
not seeing how to get the number in each group.

What is an appropriate select statement for this?

TIA,

Rich

Re: Counting number of sites with same number of sampling dates

[Rich Shepard]

On Wed, 11 Dec 2019, Michael Lewis wrote:


Put what you have in a subquery and group/aggregate again.

select sample_count, count( site_nbr ) FROM (
select site_nbr, count(distinct sampdate) AS sample_count from wrb_hg_cong
group by site_nbr order by site_nbr
) sub
group by sample_count;


Michael,

Well, darn! I totally speced using a subquery. Thank you very much for an
important lesson.

Best regards,

Rich

Re: Counting number of sites with same number of sampling dates

[Rich Shepard]

On Wed, 11 Dec 2019, Ron wrote:


The SUM() function?


Ron,

Interesting. I'll look at this, too.

Regards,

Rich

Re: Encoding/collation question

[Rich Shepard]

On Thu, 12 Dec 2019, Andrew Gierth wrote:


Note that it's perfectly fine to use UTF8 encoding and C collation (this
has the effect of sorting strings in Unicode codepoint order); this is as
fast for comparisons as LATIN1/C is.


Andrew,

This is really useful insight. I've not thought of the relationship of
encoding to collation (which I now know there isn't.)


For those cases where you need data to be sorted in a
culturally-meaningful order rather than in codepoint order, you can set
collations on specific columns or in individual queries.


Not an issue for my work. :-)

Thanks very much,

Rich

Changing default ../data/ directory

[Rich Shepard]

I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data
directory has always been located in /var/lib/pgsql//data. This
data directory is located in the / partition on a 240G SSD.

There's another 2T HDD with a /data partition and I want to both move the
current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future
databases use that location.

The PGDATA environment variable used to be used for this (but I never did
get it satisfactorily working). Web searches show changing initdb or,
perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but
no postgresql.conf).

As this is all new to me I want to learn how to:

1) Define a postgresl.conf and learn where it should be located.

2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.

3) Create all new databases in /data/pgsql/11 by default.

I'm sure it's in the manual yet I'm unsure just where to start reading.

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, nikhil raj wrote:


Step 1-: Stop the postgres services and change the path of the
data directory in postgres.conf file.


The postgresql.conf.sample notes that the default value of data_directory is
taken from -D in the startup command or the PGDATA environment variable. I
suppose that I can define PGDATA in ~/.bash_profile as I'm the only user.

Thanks,

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Adrian Klaver wrote:


Not following above:
1) Are you looking for your current postgresql.conf?


Yes.


More below.
https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY


Thanks, Adrian.

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Jeff Janes wrote:


PGDATA should work fine if you always start the server directly. But if
you sudo or su to another user, they likely won't inherit your environment
variables. And if you use some kind of start-up script, they will likely
override it. How do you start and stop PostgreSQL?


Jeff,

Slackware's /etc/rc.d/rc.postgresql start command. I can modify that script
to change the data directory path.

Thanks,

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, George Neuner wrote:


Since the new drive is local you can just move the data directory to its
new location and link to it from the default (/var) location. No
configuration changes needed.


George,

Huh! It didn't occur to me to make a softlink to the new directory from the
old one. That looks like the most parsimonious solution.

Thanks,

Rich

Re: Changing default ../data/ directory

[Rich Shepard]

On Sat, 4 Jan 2020, Adrian Klaver wrote:


I don't see anything here:
https://slackbuilds.org/slackbuilds/14.2/system/postgresql/rc.postgresql.new
that changes the conf location, so postgresql.conf should be in the DATADIR:
/var/lib/pgsql/$PG_VERSION/data


Adrian,

How interesting. When I used 'locate postgresql.conf' it did not find it,
only /usr/share/postgresql-11/postgresql.conf.sample. That's because users
are not allowed to see what's in /var/lib/. I don't recall running into this
issue before.

Thanks,

Rich

Undeliverable: Re: Changing default ../data/ directory (fwd)

[Rich Shepard]

I could not find an address for the pgsql-general list owner. Please excuse
my posting to the entire list.

Today all my messages generate this response:

-- Forwarded message --
Date: Sat, 4 Jan 2020 18:21:10 +
From: postmas...@outlook.com
To: rshep...@appl-ecosys.com
Subject: Undeliverable: Re: Changing default ../data/ directory

p3plibsmtp01-13.prod.phx3.secureserver.net rejected your message to the 
following email addresses:

p...@paulweiss.info
The address you sent your message to wasn't found at the destination domain.
It might be misspelled or it might not exist. Try to fix the problem by
doing one or more of the following:

p3plibsmtp01-13.prod.phx3.secureserver.net gave this error:
104.47.55.172 is not allowed to send from  per its SPF Record. 
Please inspect your SPF settings, and try again. IB508 

Diagnostic information for administrators:

Generating server: BN8NAM12HT135.mail.protection.outlook.com

--

Please remove this address from the subscribed list.

Thanks in advance,

Rich

Pre-version pg_upgrade syntax check

[Rich Shepard]

Running Slackware-14.2/x86_64. Current installation is postgres-11.5 and I'm
upgrading to postgresql-12.1. Both versions are installed and stopped.

If I have correctly read the pg_upgrade manual page this is the command to
use (as user postgres) with lines split to fit the message; the command will
be on a single line:

pg_upgrade \
-b /usr/lib64/postgresql/11/bin/ \
-B /usr/lib64/postgesql/12/bin/ \
-d /var/lib/pgsql/11/data/ \
-D /var/lib/pgsql/12/data/ \
-p 5432 \
-P 5432

I don't know that the port numbers are required.

Have I missed an option?

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Rich Shepard wrote:


-B /usr/lib64/postgesql/12/bin/ \


Typo: it should be /usr/lib64/postgresql/12/bin/

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Adrian Klaver wrote:


I don't know that the port numbers are required.

They are not: https://www.postgresql.org/docs/12/pgupgrade.html
"Obviously, no one should be accessing the clusters during the upgrade.
pg_upgrade defaults to running servers on port 50432 to avoid unintended
client connections. You can use the same port number for both clusters
when doing an upgrade because the old and new clusters will not be running
at the same time. However, when checking an old running server, the old
and new port numbers must be different."


Adrian,

As I'm the only user of the databases, and both are shut down, I assumed
specifying port numbers was not needed.


Have I missed an option?

Not a required one. Best way to find out is to run --check.


Good advice; I'll do that.

Thanks,

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Rich Shepard wrote:


Good advice; I'll do that.


Huh! Not what I expected:

postgres@salmo:~$ pg_upgrade -c -b /usr/lib64/postgresql/11/bin/ -B
/usr/lib64/postgresql/12/bin/ -d /var/lib/pgsql/11/data -D
/var/lib/pgsql/12/data/

(All on one line; wrapped by alpine.)

Performing Consistency Checks
-
Checking cluster versions 
Old cluster data and binary directories are from different major versions.

Failure, exiting

Well, of course they're from different major versions, that's why I'm
running pg_upgrade. What did I do incorrectly?

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Adrian Klaver wrote:


What it is saying is -b and -d are pointing at binary and data directories
that are incompatible. You need to make sure that:

/usr/lib64/postgresql/11/bin/
and
/var/lib/pgsql/11/data

are actually pointing at 11 instances of binaries and data respectively.


Adrian,

I just copied the contents of those directories from last Thursday's
backups. Still fails.

I have the pg_dumpall .sql file from last Thursday. I'll fire up
postgresql-12.1 and us pgsql to read in that file, unless you have a
recommendation for me to apply.

Thanks,

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Adrian Klaver wrote:


So you already have 11 and 12 instances of Postgres running?


Adrian,

No. Both 11 and 12 are installed; neither is running. I have a cron job that
runs pg_dumpall every weekday night.


If so why use pg_upgrade?


Because I wanted to try it rather than use 'psql -f .sql'


To verify what is going on do:
/usr/lib64/postgresql/11/bin/psql --version


The pg_upgrade page says to not have either the old or new versions running.


vi /var/lib/pgsql/11/data/PG_VERSION


/var/lib/pgsql/11/data/PG_VERSION is 11; /var/lib/pgsql/12/data/PG_VERSION
is 12.

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Tue, 11 Feb 2020, Rich Shepard wrote:


So you already have 11 and 12 instances of Postgres running?


Adrian,

I just started 12.1 and, as user postgres, read last Friday's backup file.
All's well.

A final question: which conf file do I edit so when I enter 'psql -l' (or
open a specific database) I don't need to enter my password? I don't recall
having to reset this permission with prior upgrades and want to do so now.

Regards,

Rich

Re: Pre-version pg_upgrade syntax check [FIXED]

[Rich Shepard]

On Tue, 11 Feb 2020, Rich Shepard wrote:


A final question: which conf file do I edit so when I enter 'psql -l' (or
open a specific database) I don't need to enter my password? I don't
recall having to reset this permission with prior upgrades and want to do
so now.


Never mind: I found it: /var/lib/pgsql/12/data/pg_hba.conf.

Thanks for all you help.

Rich

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Tue, 11 Feb 2020, Adrian Klaver wrote:


The above runs the psql client not the server. It is a way of determining
what version binaries /usr/lib64/postgresql/11/bin/ actually contains.


Adrian,

Aha! Running the command taught me a couple of valuable lessons because both
11 and 12 show they're running 12.1. That's because the SlackBuilds.org
build script apparently upgraded 11.5 to 12.1 in the same
/usr/lib64/postgresql/11/ directory. Before the next major version upgrade
I'll ensure the new version is installed in the proper subdirectory.

Since 12.1 is running I'll remove the version 11 directories.

Thanks very much,

Rich

Re: Enforcing uniqueness on [real estate/postal] addresses

[Rich Shepard]

On Tue, 12 May 2020, Peter Devoy wrote:


Is is possible to have two entries which have the same
address_identifier_general, street and postcode, but different
descriptions?


Unfortunately, yes. The data comes from gov't systems to regulate the
development/alteration of arbitrary pieces of property and those pieces do
not always have a postal address. E.g. a farmer may one year apply to
erect a wind turbine in "field north of Foo Cottage" and the next year
apply to demolish "barnhouse west of Foo Cottage".


I'm in the US but recently read (in The Economist, I think) that house
numbers in the UK are sometimes denied by the local government and the owner
told to select a name for the property.

Can you have a primary key for the property descrption, e.g., "Foo Cottage"
and another (related) column for objects associated with it; e.g., "North
field" and "West barnhouse?"

Similar to an apartment (flat) number in a multifamily dwelling.

Rich

RE: Enforcing uniqueness on [real estate/postal] addresses

[Rich Shepard]

On Wed, 13 May 2020, Basques, Bob (CI-StPaul) wrote:


Was wondering if you ever thought about binding the textual address to a
USNG location. https://usngcenter.org/


Bobb, et al.:

Why not have a 'parent' table with entries such as 'Foo Farm' and a 'child'
table with rows for sub-parts of the parent; e.g., 'Barn', 'Field'?

Rich

Check for duplicates before inserting new rows

[Rich Shepard]

This is a new issue for me: I've received data from a different source and
need to add non-duplicates to two tables in the database. Each row in the
new data has a station ID and associated measurements.

The existing database includes a table for station information and another
for measurements made there.

I want to learn which stations and which measurements in the new data set
are not included in the existing tables. And, I don't want to try inserting
the new data and have postgres tell me when it's found duplicates,
especially since there are two tables involved.

My research into how to do this has not found a solution so I ask for
pointers to resources that will teach me how to add these new data to both
tables.

Regards,

Rich

Re: Check for duplicates before inserting new rows

[Rich Shepard]

On Fri, 4 Sep 2020, George Woodring wrote:


I would suggest creating a temp table based on the original table and
loading the data into it first. You can then purge the duplicates.


George,

I hadn't thought of this. Using a duplicate table without a PK would work
well if there's only one attribute that needs checking.

In my case there are two tables involved: locations and measurements. So, I
can create a temporary table from the new data which holds only the
locations. I can then isolate the new location rows that do not already
exist in that table, then insert the new data to eliminate the duplicated
measurements.

Thanks,

Rich

Re: Check for duplicates before inserting new rows

[Rich Shepard]

On Fri, 4 Sep 2020, Olivier Gautherot wrote:


First of all, what version of PostgreSQL are you using?


Olivier,

12.2.


One way would be to add a UNIQUE constraint and perform for each row of the
source table a INSERT ... ON CONFLICT DO NOTHING (see
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT)

If it is a 2-way merge, I would encapsulate the process in a function
(which will create a transaction to protect your process) and add a column
to trace the rows that have been merged. For this purpose, you can use the
ON CONFLICT DO UPDATE ...


Lots for me to ponder.

The new data has attributes stored in two database tables: location and
measurements. I'll need to think about how both could be handled in a single
function. For example, the new data contains a row with a location not
already in the existing location table. That location needs to be added to
the location table and its associated measurement attributes then added to
the existing measurements table.

Thanks for the suggestion,

Rich

Re: Check for duplicates before inserting new rows

[Rich Shepard]

On Fri, 4 Sep 2020, Chris Sterritt wrote:

Assuming some simple table structures (I've not included PK or FK definitions 
for simplicity):


Chris,

Thanks very much.

Stay well,

Rich

Modifying database schema without losing data

[Rich Shepard]

I've been developing a business tracking application for my own use and it's
worked well up to now. But, I need to modify it by adding a table with
attributes from two other tables. I've not drawn a E-R diagram so I show the
two existing tables here:

CREATE TABLE Organizations (
  org_id serial PRIMARY KEY,
  org_name varchar(64) DEFAULT '??' NOT NULL,
  org_addr1 varchar(64),
  org_addr2 varchar(64),
  org_city varchar(16),
  state_code char(2),
  org_postcode varchar(10),
  org_country char(2) DEFAULT 'US' NOT NULL,
  main_phone varchar(16),
  org_fax varchar(12),
  org_url varchar(64),
  industry varchar(24) DEFAULT 'Other' NOT NULL
   REFERENCES industries(ind_name)
   ON UPDATE CASCADE
   ON DELETE RESTRICT,
  status varchar(20) DEFAULT 'Opportunity' NOT NULL
 REFERENCES statusTypes(stat_name)
 ON UPDATE CASCADE
 ON DELETE RESTRICT,
  comment text 
);


CREATE TABLE People (
  person_id serial PRIMARY KEY,
  lname varchar(15) NOT NULL,
  fname varchar(15) NOT NULL,
  job_title varchar(32),
  org_id int DEFAULT '0' NOT NULL
  REFERENCES Organizations(org_id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT,
  site_name varchar(64),
  site_addr varchar(32),
  site_city varchar(16),
  state_code char(2),
  site_postcode varchar(10),
  site_country char(2) DEFAULT 'US' NOT NULL,
  direct_phone varchar(15),
  direct_fax varchar(15),
  cell_phone varchar(15),
  site_phone varchar(15),
  ext varchar(6),
  email varchar(64),
  active boolean DEFAULT TRUE NOT NULL,
  comment text
);

What I should have noticed when I designed this tool is that addresses and
phone/e-mail addresses can be duplicated when there's only a single
location. Now I have some prospective clients with multiple locations but I
have no names of individuals. So, I want to add a Location table with
addresses and contact information. Each row in that table will have a serial PK
and will use a FK to reference the Organization table. People will now
reference the Locations table rather than the Organization table.

There are data in each of these tables and my research in my books and on
the web have not provided any insights on how to modify the existing schema
and get date into their new appropriate table homes.

I think the long way is to dump the database and manually move rows (using
emacs) from their current table to the new one, as appropriate, but there're
probably much better ways to do this and I'm eager to learn.

Regards,

Rich

Re: Modifying database schema without losing data

[Rich Shepard]

On Mon, 28 Sep 2020, Adrian Klaver wrote:


You could use INSERT INTO location(new_fields,) SELECT the_fields FROM
the_table(s).


Well, duh! I could have thought of that. That's exactly what I'll do:

Create the new table, move data from the old table into it, then drop
columns in the old table ... after checking all data's there.

Thanks, Adrian.

Stay well,

Rich

Re: Modifying database schema without losing data

[Rich Shepard]

On Mon, 28 Sep 2020, Adam Scott wrote:


What if a person is a member of more than one Org?  Consider a person_org
table.


Adam,

Not applicable. An individual is employed by a single organization.


I see mention of a site in the person table.  It may also be the case that
you need a site table.


Yep. That's what I need to add.


Often, you want a table for the Person and a Contact (or Address)  table
separately. This allows for having more than one contact for a Person.


Possible, but I've not encountered more than a couple of phone numbers per
person (work and mobile). I don't need home addresses or phones.


This way a person can be a member of more than one org, at one or  more
sites, and have one or more contacts.


Doesn't apply to businesses in my market areas.

Thanks for the thoughts,

Rich

Re: Modifying database schema without losing data

[Rich Shepard]

On Mon, 28 Sep 2020, Tom Lane wrote:


No part-timers in your universe? (My friends in the restaurant business
would surely find the above pretty laughable.)


Tom,

Not in the markets I serve; at least, not at the environmental manager
level. I don't work for retail businesses; primarily natural resource
industries and others that require storm water discharge permits (no process
waters are allowed off-site when untreated in any state in the western US).

The only part-timers are external consultants hired for a specific purpose.

Stay well,

Rich

Multi-row insert: error at terminal row.

[Rich Shepard]

I'm loading data into tables with the 'insert' statement. There are many
rows containing values and each is bracketed by parentheses except for the
last row. That's terminated with a semicolon, but psql reports an error
there:

psql:organizations.sql:1926: ERROR:  syntax error at or near ";"
LINE 1925: ...m',null,'Port','Opportunity',null);
^
I'm not seeing why that's an error. All previous rows terminate with a comma
and I don't know where else to look for the reason. What am I missing
seeing?

TIA,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, Rob Sargent wrote:


Can we see the last two line of the file (1924, 1925)?


Rob,

(2697,'Port of 
Newport','http://www.portofnewport.com',null,'Port','Opportunity',null),
(2698,'Port of 
Portland','http://www.portofportland.com',null,'Port','Opportunity',null);

Each line is enclosed in parentheses and is terminated with a comma.

Regards,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, Paul Förster wrote:


(2698,'Port of 
Portland','http://www.portofportland.com',null,'Port','Opportunity',null);

the last line has a closing parenthesis missing.


Paul,

I see a closing parenthesis immediately in front of the semi-colon and emacs
shows it matches the opening parenthesis.

Rich

Re: Multi-row insert: error at terminal row. [RESOLVED]

[Rich Shepard]

On Thu, 29 Oct 2020, Adrian Klaver wrote:

Pretty sure the thinking is that the opening parenthesis is further upstream, 
say around VALUES?


Well, duh! Of course. I forgot to enclose all value rows. Mea culpa!

Thanks,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, Paul Förster wrote:


insert ...
(
   (v1, v2, v3),
   (v4, v5, v6),
   (v7, v8, v9)<= this is the bracket pair that Emacs shows as matching.
); <= this is the missing bracket.


Paul/Adrian/Tom:

First thing I did was look at the postgres 12 manual. On page 155 I see:

You can insert multiple rows in a single command:
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);

Which is what I have. There are no extra parentheses enclosing multiple rows
of VALUES. But, adding them makes no difference: same error reported.

Thanks,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, David G. Johnston wrote:


That said seeing the first few rows, in addition to the last few, would
help.


David,

insert into organizations (org_nbr,org_name,org_url,org_email,industry,status,comment) values 
(1,'Tidewater Contractors Inc',null,null,'Mining','Opportunity','GEN12A'),

(2,'All Rock LLC','www.allrockllc.com',null,'Mining','Opportunity','GEN12A'),
...

Thanks,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, David G. Johnston wrote:


As your general syntax seems correct I would suspect an issue in the data
values - like having a single quote in an organization name that isn't
properly escaped (doubled). I'd first make sure insert one record works
then begin bisecting your values, only inserting subsets at a time, until
you narrow down the offending record.


David,

I checked and repaired all single apostrophe's with doubled apostrophies.
Postgres found other syntax errors (period rather than comma; missing comma)
but didn't reject single quotes as apostrophes within a string. Also, there
are no strings in this table that would have apostrophes.

Thanks,

Rich

Re: Multi-row insert: error at terminal row.

[Rich Shepard]

On Thu, 29 Oct 2020, Adrian Klaver wrote:


INSERT INTO products (product_no, name, price) VALUES
   (
   (1, 'Cheese', 9.99),
   (2, 'Bread', 1.99),
   (3, 'Milk', 2.99)
);

then you should have gotten a different error. Something like:

ERROR:  column "product_no" is of type integer but expression is of type 
record


Adrian,

Tried that but the error was the same: the closing semi-colon.


I'm going to say the issue is more like what David posted, an escaping
problem in the data. Not sure how many rows you are dealing with, but it
might be helpful to break them down into smaller batches to isolate the
problem.


Yep, that's what I'll do.

Thanks,

Rich

Re: Multi-row insert: error at terminal row. [RESOLVED]

[Rich Shepard]

On Thu, 29 Oct 2020, Rich Shepard wrote:


psql:organizations.sql:1926: ERROR:  syntax error at or near ";"
LINE 1925: ...m',null,'Port','Opportunity',null);

^

I'm not seeing why that's an error. All previous rows terminate with a comma
and I don't know where else to look for the reason. What am I missing
seeing?


Rob/Paul/David/Tom/Adrian:

Found the problem in line 26 of 1925 rows: a non-null column had 'null'
entered rather than one of the allowed values. That row was in the original
database and I've no idea when I might have changed that.

Why postgres didn't highlight that until I had only a 50-line .sql file I
don't know. But, when bifircating the original file into smaller pieces and
I got down to 50 lines postgres showed me exactly what the error was:

psql:orgs-1.sql:50: ERROR:  null value in column "industry" violates
not-null constraint.

Does this happen in newer versions than the 12.2 installed here?

Whew!

Stay well all ... and VOTE!

Rich

Re: Multi-row insert: error at terminal row. [RESOLVED]

[Rich Shepard]

On Thu, 29 Oct 2020, Adrian Klaver wrote:


Is orgs-1.sql just the INSERT?


Yes.


How is that file being fed to psql?


$ psql -d bustrac -f orgs-1.sql

Rich

Re: Multi-row insert: error at terminal row. [RESOLVED]

[Rich Shepard]

On Thu, 29 Oct 2020, Adrian Klaver wrote:


Is it just the 50 line version?


Adrian,

Nope.

If it is, what happens if you go back to original 1925 line version and 
correct the NULL issue in the line 26 and run it again?


I'm finding typos and column tranposition errors that I had not spotted when
I checked my work. Now going through the file in small chunks and fixing
what broke.

Thanks,

Rich

Re: Multi-row insert: error at terminal row. [RESOLVED]

[Rich Shepard]

On Thu, 29 Oct 2020, Rob Sargent wrote:


Would it be rude to suggest that you re-visit how that file was made?
Seems you'll be in the same boat "next time".


Rob,

Part of the files was exported from the old version of the database. New
rows were from different text files, each with a different format, and all
needing to be parsed with data belonging to four different tables.

There won't be a 'next time' now that I've corrected the database structure.
All new information (once I get these tables correctly loaded) will be
one-at-a-time.

Manipulating 2K datasets over long hours and multiple days led me to make
errors I didn't catch at the time.

Regards,

Rich

Drop column constraint

[Rich Shepard]

A table has a unique constraint on a column that needs removing. Reading the
postgres-12.x docs for alter table it appears the correct syntax is:

alter table locations drop constraint unique;

but this is wrong.

Trying 'alter table locations alter column loc_nbr drop constraint unique;' also
failed.

What's the proper syntax to drop the unique constraint on a table column?

TIA,

Rich

Re: Drop column constraint [FIXED]

[Rich Shepard]

On Fri, 30 Oct 2020, Adrian Klaver wrote:


It should be:
alter table locations drop constraint 'constraint_name';


Adrian,

Yes, I forgot to quote the constraint_name, And, I used the DDL name
'unique' rather than the internal name "locations_loc_nbr_key". Using the
latter, and adding 'cascade' (because the dependent table is empty) did the
trick.

Thank you,

Rich

Re: Drop column constraint [FIXED]

[Rich Shepard]

On Fri, 30 Oct 2020, Adrian Klaver wrote:


Actually unique is not the name, it is the constraint type. You can create
your own name when creating the constraint or Postgres will create one for
you.


Adrian,

Got it, thanks.

Rich

Another user error?

[Rich Shepard]

When trying to populate the locations table I get this error:
psql:locations.sql:2105: ERROR:  syntax error at or near ";"
LINE 2105: ...E Airport Way',null,'Portland','OR','97218',null,null,null);
 ^
Line 2105 is the last line in the file and is terminated with the only
semi-colon in the file. Every line prior to this one is terminated with a
colon. All other syntax errors have been corrected.

I've not encountered this issue before. Where should I look for the error?

TIA,

Rich

Re: Another user error?

[Rich Shepard]

On Sun, 1 Nov 2020, Adrian Klaver wrote:


I'm hoping that it is:
'Every line prior to this one is terminated with a comma'
not colon.


Adrian,

That's the case. The only colons are within strings.

Thanks,

Rich

Re: Another user error?

[Rich Shepard]

On Sun, 1 Nov 2020, Rich Shepard wrote:


Every line prior to this one is terminated with a colon. All other syntax
errors have been corrected.


Er, that's comma, not colon. Need to recaffinate.

Rich

Re: Another user error?

[Rich Shepard]

On Sun, 1 Nov 2020, Francisco Olarte wrote:


This is nearly impossible to diagnose without a chunk of the query (
i,e, the first lines and the last ones ).


Francisco,

$ psql -d bustrac -f locations.sql


It smells to missing parentheses. If you use some editor witch matches
them, try adding one BEFORE the semicolon. If it matches something
above you've got it.


Here're the last two lines:
(2297,1,'Principal place of business','600 SE Bay 
Boulevard',null,'Newport','OR','97365','541-265-7758','541-265-4235',null),
(2298,1,'Principal place of business','7200 NE Airport 
Way',null,'Portland','OR','97218',null,null,null);

And psql found a few missing terminating parentheses and commas which were
fixed. I expected this last run to successfully complete.

Regards,

Rich

Re: Another user error?

[Rich Shepard]

On Sun, 1 Nov 2020, Adrian Klaver wrote:


Also need the beginning lines including the INSERT part.


insert into locations (org_nbr,loc_nbr,loc_name,loc_addr1,loc_addr2,loc_city,state_code,loc_postcode,loc_phone,loc_fax,comment) values 
(1,1,'2nd Bridge Gravel Bar','16156 Hwy 101 S',null,'Brookings','OR','97415-0224','541-469-5341',null,null),


Again, while in previous runs psql had found errors none other than the final 
semicolon
showed up in this last one.

Rich

Re: Another user error? [RESOLVING]

[Rich Shepard]

On Sun, 1 Nov 2020, David G. Johnston wrote:


You encountered and asked on this exact same issue Friday...the advice in
that "Multi-row insert: error at terminal row." all still applies.


David J.

Which is why I did this file chunk-by-chunk.

Re-doing this by portions I find psql reporting errors it did not report
before. So, I keep re-running the command until it all works.

Regards,

Rich

Re: Another user error? [RESOLVING]

[Rich Shepard]

On Sun, 1 Nov 2020, Peter J. Holzer wrote:


Your chunk still seems to be 2105 lines long.


Peter,

I've started from the top and work in 50-100 line chunks. I'm finding psql
errors that it had not flagged the first time through. So I'm working slowly
and carfully and expect to find all my typos and other errors when I'm done.

Thanks,

Rich

PK issue: serial sequence needs updating

[Rich Shepard]

Running postgresql-12.2-x86_64-1_SBo on Slackware-14.2/x86_64.

My restructed business database has populated tables, but the primary keys
are not aware of the current maximum number since the table rows were added
external to postgres and read in using psql.

For example, I'm trying to add a new person to the people table which
currently has 484 rows. The insert into statement provides all columns but
the first, the PK (person_nbr) which is an int with an associated
people_person_nbr_seq. Passing the insert statement to psql is rejected
because there's already a person_nbr=1.

How do I inform the sequence that nextval should be 485? Or, do I drop that
sequence and restore it so it reads all existing table rows and their PKs?

If I didn't clearly explain myself, ask and I'll try again. :-)

TIA,

Rich

Re: PK issue: serial sequence needs updating [RESOLVED]

[Rich Shepard]

On Tue, 17 Nov 2020, Adrian Klaver wrote:


https://www.postgresql.org/docs/12/sql-altersequence.html
ALTER SEQUENCE people_person_nbr_seq RESTART 485;


Thanks, Adrian. I missed that page.

Regards,

Rich

Re: Alter the column data type of the large data volume table.

[Rich Shepard]

On Thu, 3 Dec 2020, Michael Lewis wrote:


On Wed, Dec 2, 2020 at 11:53 PM charles meng  wrote:



I have a table with 1.6 billion records. The data type of the primary key
column is incorrectly used as integer. I need to replace the type of the
column with bigint. Is there any ideas for this?



You can add a new column with NO default value and null as default and have
it be very fast. Then you can gradually update rows in batches (if on
PG11+, perhaps use do script with a loop to commit after X rows) to set the
new column the same as the primary key. Lastly, in a transaction, update
any new rows where the bigint column is null, and change which column is
the primary key & drop the old one. This should keep each transaction
reasonably sized to not hold up other processes.


Tell me, please, why

ALTER TABLE  ALTER COLUMN  SET DATA TYPE BIGINT

will not do the job?

I've found some varchar columns in a couple of tables too small and used the
above to increase their size. Worked perfectly.

Regards,

Rich

Re: Alter the column data type of the large data volume table.

[Rich Shepard]

On Thu, 3 Dec 2020, Michael Lewis wrote:


Afaik, it will require an access exclusive lock for the entire time it
takes to re-write the 1.6 billion rows and update all indexes. That sort
of lock out time doesn't seem workable in many production systems.


Michael,

Okay. I hadn't thought of that.

Stay well,

Rich

User input to queries

[Rich Shepard]

While I develop the application's GUI I use the database from the command
line (psql). While some queries are self-contained others need user input.
I've not found a search term that locates this information in the
postgres-12 user manual and my web searches suggest that using '&' should
work.

I tried the latter with this query but postgres didn't like it. (There may
well be other errors in it so don't hesitate to point out my mistakes.)

--
/* This query selects all activity information for a named person */

/* Specify columns */
SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,
   o.org_name,
   l.loc_nbr, l.loc_name,
   a.act_date, a.act_type, a.notes, a.next_contact
/* Specify tables. */ 
FROM People AS p

 JOIN Organizations AS o ON o.org_nbr = p.org_nbr
 JOIN Locations AS l ON l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr
 JOIN Activities AS a ON a.person_nbr = p.person_nbr
/* Specify rows */ 
WHERE p.lname = &p.lname AND p.fname = &p.fname;

-

Suggestions on what I should read to learn more about this subject are
appreciated.

Regards,

Rich

Re: User input to queries

[Rich Shepard]

On Wed, 9 Dec 2020, Michael Lewis wrote:


Are you looking for this perhaps?
https://www.postgresql.org/docs/current/sql-prepare.html


Michael,

I don't think so. Reading the PREPARE doc page my understanding is that its
use is for statement execution optimization, not asking for user data input
for variables in the query statement. It's useful information, though not
for my immediate need.

Thanks and stay well,

Rich