Must re-connect to see tables
Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works. What id does (essentially) is: psql postgres postgres drop database test; create database test; \c test \i data.sql \dt \q I tested this with a small data.sql and it worked fine. However, when I use a real (much larger) data.sql then \dt replies "No relations" ! I found this if I stick an extra '\c test' after the '\i data.sql' it works. The data.sql is a PostgreSQL dump from another database. When the '\i data.sql' is done, the prompt still shows 'test#' so it's not like I've changed databases. I only have one schema, 'public'. Can't figure out why a simple example works but a large file requires an extra \c. Sure appreciate any help. Blake McBride
Re: Must re-connect to see tables
I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit Linux box. On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride wrote: > Hi, > > I have been using PostgreSQL for many years but all of a sudden a db load > script I've been using no longer works. What id does (essentially) is: > > psql postgres postgres > drop database test; > create database test; > \c test > \i data.sql > \dt > \q > > > I tested this with a small data.sql and it worked fine. However, when I > use a real (much larger) data.sql then \dt replies "No relations" ! I > found this if I stick an extra '\c test' after the '\i data.sql' it works. > > The data.sql is a PostgreSQL dump from another database. When the '\i > data.sql' is done, the prompt still shows 'test#' so it's not like I've > changed databases. > > I only have one schema, 'public'. > > Can't figure out why a simple example works but a large file requires an > extra \c. > > Sure appreciate any help. > > Blake McBride > >
Re: Must re-connect to see tables
On Tue, Mar 27, 2018 at 6:48 AM, rob stone wrote: > Hello Blake, > > On Tue, 2018-03-27 at 06:23 -0500, Blake McBride wrote: > > I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit > > Linux box. > > > > On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride > > wrote: > > > Hi, > > > > > > I have been using PostgreSQL for many years but all of a sudden a > > > db load script I've been using no longer works. What id does > > > (essentially) is: > > > > > > psql postgres postgres > > > drop database test; > > > create database test; > > > \c test > > > \i data.sql > > > \dt > > > \q > > > > > > > > > I tested this with a small data.sql and it worked fine. However, > > > when I use a real (much larger) data.sql then \dt replies "No > > > relations" ! I found this if I stick an extra '\c test' after the > > > '\i data.sql' it works. > > > > > > The data.sql is a PostgreSQL dump from another database. When the > > > '\i data.sql' is done, the prompt still shows 'test#' so it's not > > > like I've changed databases. > > > > > > I only have one schema, 'public'. > > > > > > Can't figure out why a simple example works but a large file > > > requires an extra \c. > > > > > > Sure appreciate any help. > > > > > > Blake McBride > > > > > > > > > > > > 1) Where does your e-mail subject line "Must re-connect to see tables" > relate to the import? > I do the import and then \dt return no relations. I must *re-connect* via \c in order to see the tables. (According to the prompt, I was still connected to the database.) > > 2) Does the larger data.sql file have a "create database" line or any > other construct at the beginning of the file that would cause it to NOT > insert rows into database test? > data.sql is an untouched dump of a database from PostgreSQL 9.5.12. The import seems to have worked. I just need another \c after the \i. Thanks! Blake > > Cheers, > Rob >
Trouble incrementing a column
Greetings,
I am using PostgreSQL 10.10. I am having trouble incrementing a column for
reasons I can't see. It's probably some basic SQL thing. Your help is
appreciated.
create table my_table (
listid char(36) not null,
seq smallint not null,
item varchar(4096),
primary key (listid, seq)
);
insert into my_table (listid, seq) values ('abc', 1);
insert into my_table (listid, seq) values ('abc', 2);
-- the following works some of the time
update my_table set seq=seq+1;
-- the following doe not work for reasons I do not know
update my_table set seq=seq+1 where listid='abc';
What I get is a duplicate primary key. I wouldn't think I'd get that
because I'd think the whole thing is done in a transaction so that
duplicate checks wouldn't be done till the end (essentially).
Is there a clean way to do this?
Thanks!
Blake McBride
Please help: pgAdmin 4 on Amazon Linux 2
Greetings, I am trying to install pgAdmin 4 on Amazon Linux 2. PostgreSQL is already installed and working fine. I believe Amazon Linux 2 is based on RedHat. I am doing the following: [root@a-1lxumlkkw4mu4 ~]# rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm warning: /var/tmp/rpm-tmp.ZEygli: Header V3 RSA/SHA256 Signature, key ID 210976f2: NOKEY [root@a-1lxumlkkw4mu4 ~]# yum install pgadmin4 Loaded plugins: amzn_workspaces_filter_updates, halt_os_update_check, priorities, update-motd amzn2-core | 3.7 kB 00:00:00 amzn2extra-GraphicsMagick1.3 | 3.0 kB 00:00:00 amzn2extra-docker | 3.0 kB 00:00:00 amzn2extra-epel | 3.0 kB 00:00:00 amzn2extra-gimp | 1.3 kB 00:00:00 amzn2extra-libreoffice | 3.0 kB 00:00:00 amzn2extra-mate-desktop1.x | 3.0 kB 00:00:00 epel/x86_64/metalink | 15 kB 00:00:00 firefox | 2.2 kB 00:00:00 google-chrome | 1.3 kB 00:00:00 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-2-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found Trying other mirror. One of the configured repositories failed (pgadmin4), and yum doesn't have enough cached data to continue. At this point the only safe thing yum can do is fail. There are a few ways to work "fix" this: 1. Contact the upstream for the repository and get them to fix the problem. 2. Reconfigure the baseurl/etc. for the repository, to point to a working upstream. This is most often useful if you are using a newer distribution release than is supported by the repository (and the packages for the previous distribution release still work). 3. Run the command with the repository temporarily disabled yum --disablerepo=pgAdmin4 ... 4. Disable the repository permanently, so yum won't use it by default. Yum will then just ignore the repository until you permanently enable it again or use --enablerepo for temporary usage: yum-config-manager --disable pgAdmin4 or subscription-manager repos --disable=pgAdmin4 5. Configure the failing repository to be skipped, if it is unavailable. Note that yum will try to contact the repo. when it runs most commands, so will have to try and fail each time (and thus. yum will be be much slower). If it is a very temporary problem though, this is often a nice compromise: yum-config-manager --save --setopt=pgAdmin4.skip_if_unavailable=true failure: repodata/repomd.xml from pgAdmin4: [Errno 256] No more mirrors to try. https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/rhel-2-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found [root@a-1lxumlkkw4mu4 ~]# I have no idea how to fix this. Any help would sure be appreciated. Blake McBride
Packaging pgAdmin 4
Greetings, I've had ongoing difficulties installing pgAdmin 4. These difficulties have to do with the way pgAdmin is packaged and distributed. For example, I use LinuxMint and Manjaro. The pgAdmin distributions support neither. Although Mint is a derivative of Ubuntu, lsb_release -cs returns a value not usable by pgAdmin's distribution system. I had to tweak internal files to get it to work. I'd like to suggest using snap, flatpack, or AppImage. Using one of these, pgAdmin would be trivial to install on nearly any distro and on nearly any version of those distros. Secondly, you'd have to maintain far fewer builds. The only negative to using one of these is that the installations would be a bit larger. This is a very small penalty to get the portable and convenience these package managers provide. Thanks. Blake McBride
Re: Packaging pgAdmin 4
True but they're usually out-of-date. On Sat, Nov 27, 2021 at 2:09 PM Chuck Davis wrote: > Have you checked your distro repositories? The distro I use ships pgAdmin > together with the latest and previous versions of postgresql. > > On Sat, Nov 27, 2021 at 10:52 AM Blake McBride > wrote: > >> Greetings, >> >> I've had ongoing difficulties installing pgAdmin 4. These difficulties >> have to do with the way pgAdmin is packaged and distributed. >> >> For example, I use LinuxMint and Manjaro. The pgAdmin distributions >> support neither. Although Mint is a derivative of Ubuntu, lsb_release -cs >> returns a value not usable by pgAdmin's distribution system. I had to >> tweak internal files to get it to work. >> >> I'd like to suggest using snap, flatpack, or AppImage. Using one of >> these, pgAdmin would be trivial to install on nearly any distro and on >> nearly any version of those distros. Secondly, you'd have to maintain far >> fewer builds. >> >> The only negative to using one of these is that the installations would >> be a bit larger. This is a very small penalty to get the portable and >> convenience these package managers provide. >> >> Thanks. >> >> Blake McBride >> >>
Problem with COPY command on pg_dump
Greetings, I am using pg_dump on version 9.5.10 and trying to import it on version 9.3.20. Many (or all) COPY commands are failing with messages like: psql:16504.db:16874: ERROR: missing data for column "street" CONTEXT: COPY address, line 1: "" Is there an easy way for me to fix this (like export with some compatibility flag)? Thanks! Blake McBride
Re: Problem with COPY command on pg_dump
I suppose it's something I'm doing. I'm pg_dump'ing the schema with the data, so I can't be missing any columns. However, I wrote a program to filter out certain data. I believe that is where the problem is. I wrote it a long, long time ago before --exclude-table-data existed. When I use --exclude-table-data it works. Thanks. Blake On Sat, Dec 9, 2017 at 9:53 AM, Rob Sargent wrote: > > > > On Dec 9, 2017, at 8:42 AM, Blake McBride wrote: > > > > Greetings, > > > > I am using pg_dump on version 9.5.10 and trying to import it on version > 9.3.20. Many (or all) COPY commands are failing with messages like: > > > > psql:16504.db:16874: ERROR: missing data for column "street" > > CONTEXT: COPY address, line 1: "" > > > > Is there an easy way for me to fix this (like export with some > compatibility flag)? > > > > Thanks! > > > > Blake McBride > > > > Is there in fact ‘street’ data in the file? Sounds like you have the > wrong number of columns. Do you have your actual dump and restore commands > to show here? > >
