Re: External psql editor
On Fri, 2022-04-29 at 13:21 -0700, Rich Shepard wrote: > On Fri, 29 Apr 2022, Reid Thompson wrote: > > > https://linuxgazette.net/issue14/bashtip.html may of of use. > > Reid, > > I've had no issues using joe in any v.t. running an application > (e.g., > alpine) or by itself. Apparently, psql is different. > > Rich > > I believe that psql also uses readline, so my thought was that maybe these instructions could enable you to map the 'move' keystrokes that you're familiar with to be used while on the psql command line. A very quick test seems to indicate that you can.
Re: External psql editor
On Mon, 2 May 2022, Reid Thompson wrote: Apparently, psql is different. I believe that psql also uses readline, so my thought was that maybe these instructions could enable you to map the 'move' keystrokes that you're familiar with to be used while on the psql command line. A very quick test seems to indicate that you can. Reid, Nope. No difference. Learning to put the '\e' at the end of the command line rather than at the head did the job. All's well. Thanks, Rich
Re: Backing up a DB excluding certain tables
On 5/2/22 12:24, JORGE MALDONADO wrote: Hi, After a lot of tests and reading about the issue with the "*exclude table*" option on *pg_dump*, I found many articles saying that the problem has to do with the encoding of the DB. The DB I am testing with has a *WIN1252* encoding, so I decided to create a new DB with *UTF8* encoding. The following is a list of the actual DBs, being *riopoderoso* and *testdb* the ones I tested. As you can see, *riopoderoso* has *WIN1252* encoding and *testdb* has *UTF8* encoding. The *pg_dump* command worked correctly when testing the *testDB* database. So, this proves that encoding is the real issue here. Both DBs have the same *Collate* and *Ctype* however, the only difference is the encoding. With this in mind, I see that I can set the encoding to *UTF8* and leave *Collate* and *Ctype* as shown because, as far as I understand, both of them have to do with string comparison (the language used to enter data into the DB is Spanish). I will very much appreciate your comments on this. image.png Please do not use images for textual information as you end up with the above in the email archives or in email clients that are text only. The information is then lost. Copy and paste from the console. Back to the issue at hand: 1) Did you try the suggestion in the "Notes for Windows users" for the riopoderoso database? 2) What was the pg_dump command that you used that worked? With respect, Jorge Maldonado -- Adrian Klaver adrian.kla...@aklaver.com
Re: External psql editor
Rich Shepard writes: > On Mon, 2 May 2022, Reid Thompson wrote: >> I believe that psql also uses readline, so my thought was that maybe these >> instructions could enable you to map the 'move' keystrokes that you're >> familiar with to be used while on the psql command line. A very quick test >> seems to indicate that you can. > Nope. No difference. Perhaps your psql is built against libedit rather than readline. regards, tom lane
Re: External psql editor
On Mon, 2 May 2022, Tom Lane wrote: Perhaps your psql is built against libedit rather than readline. Tom, Could be I use the SlackBuilds.org build script. Regards, Rich
UUID vs serial and currval('sequence_id')
Hi,
When doing an insert with a serial primary key we can refer to
currval('sequence_name') in subsequent inserts and we also return it for
later processing.
Example:
CREATE TABLE contact (
contactid serial not null primary key, -- creates sequence
'contact_contactid_seq'
firstname text not null,
lastname text
);
CREATE TABLE contactinterests(
contactid int not null references contact(contactid),
interest text
);
-- insert statement as single transaction
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing');
--insert statement as single transaction returning contactid
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing')
returning currval('contact_contactid_seq');
Which is very nice as it gives us back the contactid.
Is it possible to get similar functionality using gen_random_uuid() or
uuid-ossp?
Thanks
Robert
Re: UUID vs serial and currval('sequence_id')
On Mon, May 2, 2022 at 3:33 PM Robert Stanford wrote:
>
> --insert statement as single transaction returning contactid
> INSERT INTO contact(
> firstname, lastname)
> VALUES('John', 'Smith');
> INSERT INTO contactinterests(
> contactid, interest)
> VALUES (currval('contact_contactid_seq'),'Fishing')
> returning currval('contact_contactid_seq');
>
> Which is very nice as it gives us back the contactid.
>
> Is it possible to get similar functionality using gen_random_uuid() or
> uuid-ossp?
>
>
You basically have to use "INSERT ... RETURNING" or variables. Which/how
depends on the language you are writing in. Pure SQL without client
involvement requires that you use chained CTEs of INSERT...RETURNING (or I
suppose you could leverage set_config(), haven't tried that way myself).
In pl/pgsql you can also use variables, and the same goes for psql - though
that requires client involvement and so isn't generally that great a choice.
David J.
Re: External psql editor
On 5/2/22 17:27, Tom Lane wrote: Rich Shepard writes: On Mon, 2 May 2022, Reid Thompson wrote: I believe that psql also uses readline, so my thought was that maybe these instructions could enable you to map the 'move' keystrokes that you're familiar with to be used while on the psql command line. A very quick test seems to indicate that you can. Nope. No difference. Perhaps your psql is built against libedit rather than readline. regards, tom lane That is easy enough to determine: mgogala@umajor ~]$ ldd `which psql` linux-vdso.so.1 (0x7ffded951000) libpq.so.private13-5 => /lib64/libpq.so.private13-5 (0x7ff145284000) _*libreadline.so.8 => /lib64/libreadline.so.8 (0x7ff14522c000)*_ libm.so.6 => /lib64/libm.so.6 (0x7ff14515) libc.so.6 => /lib64/libc.so.6 (0x7ff144f49000) libssl.so.1.1 => /lib64/libssl.so.1.1 (0x7ff144eac000) libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x7ff144bbe000) libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7ff144b65000) libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7ff144b09000) libtinfo.so.6 => /lib64/libtinfo.so.6 (0x7ff144ada000) /lib64/ld-linux-x86-64.so.2 (0x7ff14539a000) libz.so.1 => /lib64/libz.so.1 (0x7ff144ac) libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7ff1449e2000) libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7ff1449ca000) libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7ff1449c1000) libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7ff1449b) libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7ff1449a9000) libresolv.so.2 => /lib64/libresolv.so.2 (0x7ff144995000) liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7ff144982000) libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7ff144962000) libselinux.so.1 => /lib64/libselinux.so.1 (0x7ff144935000) libcrypt.so.2 => /lib64/libcrypt.so.2 (0x7ff1448fb000) libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x7ff144864000) [mgogala@umajor ~]$ Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: UUID vs serial and currval('sequence_id')
On Tue, 3 May 2022 at 08:39, David G. Johnston wrote: > You basically have to use "INSERT ... RETURNING" or variables. Which/how > depends on the language you are writing in. Pure SQL without client > involvement requires that you use chained CTEs of INSERT...RETURNING (or I > suppose you could leverage set_config(), haven't tried that way myself). > In pl/pgsql you can also use variables, and the same goes for psql - though > that requires client involvement and so isn't generally that great a choice. > > Thanks, so I can do: alter table contact add column contactuuid uuid alter table contactinterests add column contactuuid uuid alter table contactinterests drop column contactid with thisuuid as ( SELECT gen_random_uuid() as thisuuid ), contactuuid as( INSERT INTO contact( contactuuid,firstname, lastname) VALUES( (select thisuuid from thisuuid ),'John', 'Smith') returning (select thisuuid from thisuuid ) ) INSERT INTO contactinterests( contactuuid, interest) VALUES ( (select thisuuid from contactuuid ),'Fishing') returning (select thisuuid from contactuuid ); Robert
Re: UUID vs serial and currval('sequence_id')
On Mon, May 2, 2022 at 4:24 PM Robert Stanford wrote: > On Tue, 3 May 2022 at 08:39, David G. Johnston > wrote: > >> You basically have to use "INSERT ... RETURNING" or variables. Which/how >> depends on the language you are writing in. Pure SQL without client >> involvement requires that you use chained CTEs of INSERT...RETURNING (or I >> suppose you could leverage set_config(), haven't tried that way myself). >> In pl/pgsql you can also use variables, and the same goes for psql - though >> that requires client involvement and so isn't generally that great a choice. >> >> > Thanks, so I can do: > > alter table contact add column contactuuid uuid > alter table contactinterests add column contactuuid uuid > alter table contactinterests drop column contactid > > with thisuuid as ( > SELECT gen_random_uuid() as thisuuid > ), > contactuuid as( > INSERT INTO contact( > contactuuid,firstname, lastname) > VALUES( > (select thisuuid from thisuuid ),'John', 'Smith') returning > (select thisuuid from thisuuid ) > ) > INSERT INTO contactinterests( > contactuuid, interest) > VALUES ( > (select thisuuid from contactuuid ),'Fishing') > returning (select thisuuid from contactuuid ); > > It works but "returning contactuuid" is considerably easier to understand and probably cheaper to execute. If you are going to pre-compute the uuid the returning clause becomes pointless though, as your example demonstrates - you never actually use the returned value. I suggest avoiding naming the CTE query and the column(s) it produces the same thing. David J. David J.
Re: External psql editor
Tom Lane said on Mon, 02 May 2022 17:27:15 -0400 >Rich Shepard writes: >> On Mon, 2 May 2022, Reid Thompson wrote: >>> I believe that psql also uses readline, so my thought was that >>> maybe these instructions could enable you to map the 'move' >>> keystrokes that you're familiar with to be used while on the psql >>> command line. A very quick test seems to indicate that you can. > >> Nope. No difference. > >Perhaps your psql is built against libedit rather than readline. If this is indeed true, he can give his psql readline capabilities by installing rlwrap and performing the following command: rlwrap psql HTH, SteveT Steve Litt March 2022 featured book: Making Mental Models: Advanced Edition http://www.troubleshooters.com/mmm
Re: External psql editor
On 2022-05-03 01:49:41 -0400, Steve Litt wrote: > Tom Lane said on Mon, 02 May 2022 17:27:15 -0400 [changing keybindings doesn't work] > >Perhaps your psql is built against libedit rather than readline. > > If this is indeed true, he can give his psql readline capabilities by > installing rlwrap and performing the following command: While rlwrap is useful sometimes, I suggest reading the manual for libedit might be a better option. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
