Re: cast bytea to macaddr

[Matt Zagrabelny]

Hi Boris,

[bringing this email back to the mailing list]

A couple of questions:

1. What do the double parenthesis '(( Values  ))' mean?

2. What is the 'x(field)' in the column alias?

Thanks for educating me!

-m

On Thu, Sep 28, 2023 at 10:34 AM Boris Zentner  wrote:

> Hi matt,
>
> This is my take. I guess this is also your solution ( encode(fteld,
> 'hex'): :macaddr  )
>
> SELECT encode(fteld, 'hex'): :macaddr FROM ((VALUES
> (decode('aabbccddeeff', 'hex'
>
> AS x(field)
> --
> Boris
>
>
> Am 28.09.2023 um 17:01 schrieb Matt Zagrabelny :
>
> 
> Greetings Pg folks,
>
> I have a database schema (not mine) that uses bytea to represent a MAC
> address. I'd like to create a view that casts that field into a MACADDR
> type.
>
> # \d lease4
>Table "public.lease4"
>  Column │   Type   │ Collation │ Nullable │
> Default
>
> ╪══╪═══╪══╪═
>  hwaddr │ bytea│   │  │
>
> # select hwaddr from lease4;
>  hwaddr
> 
>  \x9cebe803e3b9
>
> It looks like I can cast the bytea to text and then manipulate that and
> finally cast it to macaddr.
>
> Is there a better, or more canonical, way to convert/cast this field?
>
> Thanks for any help!
>
> -m
>
>

cast bytea to macaddr

[Matt Zagrabelny]

Greetings Pg folks,

I have a database schema (not mine) that uses bytea to represent a MAC
address. I'd like to create a view that casts that field into a MACADDR
type.

# \d lease4
   Table "public.lease4"
 Column │   Type   │ Collation │ Nullable │ Default
╪══╪═══╪══╪═
 hwaddr │ bytea│   │  │

# select hwaddr from lease4;
 hwaddr

 \x9cebe803e3b9

It looks like I can cast the bytea to text and then manipulate that and
finally cast it to macaddr.

Is there a better, or more canonical, way to convert/cast this field?

Thanks for any help!

-m

Re: A Small psql Suggestion

[Matt Zagrabelny]

At the risk of starting a +1 snowball or a divergent argument...


On Tue, Jan 31, 2023 at 10:16 AM Raymond Brinzer 
wrote:

> Greetings,
>
> There is (for me) a small speed bump in psql.  I think it's worth
> mentioning, minor though it is, because psql is such a polished tool
> generally, and because it's something which affects me many, many times a
> day.
>
> As it is, \d is a shortcut for \dtmvs.  What I actually want to see, on a
> regular basis, are my relations:  \dtmv.  Most of the time, the sequences
> are clutter.  If my habits are like most people's in this (and I suspect
> they are), excluding sequences from \d would optimize for the common case.
>

+1

Of course, I don't generally have that many relations that the few
sequences that show up are cause for issue, but it would be nice to not
have to parse them visually.

-m

Re: echo of \C foo

[Matt Zagrabelny]

On Tue, Aug 31, 2021 at 11:10 AM Tom Lane  wrote:
>
> Matt Zagrabelny  writes:
> > $ \C foo
> > Title is "foo".
>
> > How do I suppress the echo of "Title is..." from psql?
>
> The -q switch would do it ...

Thank you, Tom!

-m

echo of \C foo

[Matt Zagrabelny]

Greetings,

I am using the \C command in psql to set a title for my table.

psql is echoing 'Title is "foo"'.

$ \C foo
Title is "foo".

How do I suppress the echo of "Title is..." from psql?

Thanks for any help!

Cheers,

-m

quoted-printable to jsonb

[Matt Zagrabelny]

Greetings PG-users,

Scenario:

I am using FreeRADIUS to insert data to a PG database.

One of the pieces of data is an array of key value pairs. The array is
encoded with quoted-printable [0].

I'd like to ideally have the key-value pair put into a JSONB field.

Here is an example of the data encoded:

mdm-tlv=3Ddevice-platform=3Dlinux-64=2Caudit-session-id=3Dc0a87311021f3000601dda71=2Cmdm-tlv=3Dac-user-agent=3DOpenConnect
VPN Agent =28NetworkManager=29 v8.10-1

The =3D is an encoded "=", the =2C is an encoded ",", so forth and so on.

The encoded comma (=2C) separates the key-value pairs.

Are there any functions within PG to properly decode something like what I
have into a JSONB field?

Thanks for any help!

-m

[0] https://en.wikipedia.org/wiki/Quoted-printable

Re: count(*) vs count(id)

[Matt Zagrabelny]

On Mon, Feb 1, 2021 at 7:19 PM Guillaume Lelarge 
wrote:

>
>
> You're doing a left join, so I guess there's no row where
> call_records.timestamp::date = 2020-08-30. That would result with a NULL id.
>
>
Thanks for the excellent analysis everyone. I appreciate it!

Here is the documentation (for anyone reading the mailing list in the
future...)

count(*)   bigint number of input rows
count(expression) any bigint number of input rows for which the value of
expression is not null

Have a great night (or equivalent for your TZ).

-m

Re: count(*) vs count(id)

[Matt Zagrabelny]

On Mon, Feb 1, 2021 at 6:35 PM Tom Lane  wrote:

> Matt Zagrabelny  writes:
> > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent 
> wrote:
> >> You got one null from count(*) likely.
>
> > What is count(*) counting then? I thought it was rows.
>
> Yeah, but count(id) only counts rows where id isn't null.
>

I guess I'm still not understanding it...

I don't have any rows where id is null:

$ select count(*) from call_records where id is null;
 count
═══
 0
(1 row)

Time: 0.834 ms
$

select count(id) from call_records where id is null;
 count
═══
 0
(1 row)

Time: 0.673 ms

Which field is count(*) counting if it is counting nulls?

-m

Re: count(*) vs count(id)

[Matt Zagrabelny]

On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent  wrote:

[...]


>
> You got one null from count(*) likely.
>

What is count(*) counting then? I thought it was rows.

-m

count(*) vs count(id)

[Matt Zagrabelny]

Greetings,

Is there a preferred method of counting rows?

count(*) vs count(field_name)

I have used count(*) for a long time and am hitting an inconsistency. At
least it is an inconsistency to me (I'm sure there is an explanation)...

INCORRECT OUTPUT (not desired results)
$ select calendar.entry, count(*)  from generate_series('2020-01-01'::date,
'2021-01-10'::date, '1 day'::interval) as calendar(entry) left join
call_records on calendar.entry = call_records.timestamp::date group by
calendar.entry;
[...]
 2020-08-30 00:00:00-05 │ 1
 2020-08-31 00:00:00-05 │ 1
[...]

CORRECT OUTPUT (desired results)
$ select calendar.entry, count(id)  from
generate_series('2020-01-01'::date, '2021-01-10'::date, '1 day'::interval)
as calendar(entry) left join call_records on calendar.entry =
call_records.timestamp::date group by calendar.entry;
[...]
 2020-08-30 00:00:00-05 │ 0
 2020-08-31 00:00:00-05 │ 0
[...]

What am I missing between count(*) and count(id)?

Thanks for any help!

-m

initdb --data-checksums

[Matt Zagrabelny]

Hello,

I see the --data-checksums option for initdb. Is it possible to use
--data-checksums after the cluster has been initialized? I'm guessing
"not", but thought I'd ask.

I'm running v12 on Debian.

Thanks for any help!

-m

Re: Can we get SQL Server-like cross database queries

[Matt Zagrabelny]

On Thu, Jun 4, 2020 at 12:56 AM Laurenz Albe 
wrote:

> On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote:
> > There's no doubt it's useful, and it's also part of the SQL spec,
> > which says you can do catalog.schema.table.  I would guess that we
> > might get that as a byproduct of any project to make PostgreSQL
> > multithreaded.  That mountain moving operation will require us to get
> > rid of all the global state that currently ties a whole process to one
> > session and one database, and replace it with heap objects with names
> > like Session and Database that can be passed around between worker
> > threads.
>
> I am -1 on cross-database queries.
>
> I think it is a desirable feature to have databases isolated from
> each other, so you don't have to worry about a permission you forgot
> that allows somebody to access a different database.
>

Perhaps make it a new right that can be granted and it is disabled by
default.

Superusers could have it by default.

ALTER USER foo WITH CROSSDB | NOCROSSDB

Obviously there is much more to flesh out than this.

-m

Re: POLL: Adding transaction status to default psql prompt

[Matt Zagrabelny]

+1

On Wed, Feb 5, 2020 at 9:15 PM Ahmed, Nawaz (Fuji Xerox Australia)
 wrote:
>
> +1
>
> -Original Message-
> From: Vik Fearing 
> Sent: Thursday, 6 February 2020 1:55 PM
> To: pgsql-general@lists.postgresql.org
> Subject: POLL: Adding transaction status to default psql prompt
>
> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was 
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by 
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>
> IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is 
> private and confidential and may contain legally privileged information. It 
> is for the addressee's attention only. If you are not the intended recipient 
> and have received this transmission, you must not use, edit, print, copy or 
> disclose its contents to any person or disseminate the information contained 
> herein or hereto attached, and you must notify sender immediately by return 
> email and delete this transmission from your system. Any confidentiality, 
> privilege or copyright is not waived or lost because this e-mail has been 
> sent to you in error. We have used reasonable efforts to protect this 
> transmission from computer viruses and other malicious software, but no 
> warranty is made and the sender takes no responsibility for any loss or 
> damage incurred from using this email or the information contained in this 
> email.

continuation character for meta commands in psql

[Matt Zagrabelny]

Greetings,

I've searched the internet and also the pgsql-general list for an answer to
this question, but couldn't find one.

Is there a continuation character for meta commands in psql?

I've seen mention that a caret (^) could be used, but it didn't work
correctly and the psql man page makes no mention of it.

I'd like to do something like:

\set PROMPT1 'long prompt code' \
'other long prompt configurations' \
'and more still'

in my .psqlrc file.

What do you folks think?

Thanks for any help or guidance!

-m

Re: Subquery to select max(date) value

[Matt Zagrabelny]

Hey Rich,

On Tue, Feb 12, 2019 at 4:24 PM Rich Shepard 
wrote:

> The query is to return the latest next_contact date for each person. Using
> the max() aggregate function and modeling the example of lo_temp on page 13
> of the rel. 10 manual I wrote this statement:
>

I use DISTINCT ON and ORDER BY to get the single latest value from a table
with multiple date entries:

https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by

HTH,

-m

Re: psql profiles?

[Matt Zagrabelny]

On Fri, Dec 7, 2018 at 7:42 AM Arthur Zakirov 
wrote:

> On 07.12.2018 01:34, Matt Zagrabelny wrote:
> >
> >
> > On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera  > <mailto:alvhe...@2ndquadrant.com>> wrote:
> >
> > Sure, just define a pg_service.conf file.
> > https://www.postgresql.org/docs/11/libpq-pgservice.html
> >
> >
> > Thanks Alvaro!
> >
> > Is there any shorter version than:
> >
> > psql "service=foo"
> >
> > ?
> >
> > If not, I can make a shell alias that puts the "service=$@" into the
> > command.
>
>
>
Thanks for the hints and discussion about this.

Here's my final implementation for the curious and to close the loop:

# a zsh function to avoid having to type "service="

$ which pssql
pssql () {
psql "service=$@"
}

# and a zsh completion function:

$ cat ~/.fpath/_pssql
#compdef pssql

PG_SERVICES_CONF=~/.pg_service.conf

if [[ -r ${PG_SERVICES_CONF} ]]; then
compadd $(sed -nE 's/^ *\[(.*)\] *$/\1/p' ${PG_SERVICES_CONF})
fi

 It works like a charm!

Thanks for all the help!

-m

Re: psql profiles?

[Matt Zagrabelny]

On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera 
wrote:

> On 2018-Dec-06, Matt Zagrabelny wrote:
>
> > I'd rather do:
> >
> > psql foo
> >
> > and have it know that I connect to foo on host db-host-1.example.com.
> >
> > Is this possible with psql or do I hack together some wrapper script?
>
> Sure, just define a pg_service.conf file.
> https://www.postgresql.org/docs/11/libpq-pgservice.html


Thanks Alvaro!

Is there any shorter version than:

psql "service=foo"

?

If not, I can make a shell alias that puts the "service=$@" into the
command.

Thanks again!

-m

psql profiles?

[Matt Zagrabelny]

Greetings,

I'm setting up my workstation to use "gss" for auth to a variety of Pg
systems on different hosts.

I'd rather not have to specify the "-h" for a connection:

psql -h db-host-1.example.com foo

I'd rather do:

psql foo

and have it know that I connect to foo on host db-host-1.example.com.

Is this possible with psql or do I hack together some wrapper script?

Thanks for any input!

-m

Re: Question on postgresql.conf

[Matt Zagrabelny]

On Tue, Jul 31, 2018 at 9:21 AM, Tom Lane  wrote:

> "Lu, Dan"  writes:
> > Question:
> > Can  be a variable like `hostname` derived from Unix shell or
> I have to hardcode the name of the host like " include
> /nfs/global/postgres-host123.cnf
>
> Nope, you'd have to hard-code it.
>
> Of course, you could have the startup script that you intended to set the
> env variable just overwrite $PGDATA/postgresql.conf with the right thing.


Or have puppet/chef/ansible/etc. write the correct config file based on
your dynamic data.

-m

Re: Enhancement to psql command, feedback.

[Matt Zagrabelny]

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.
>

For fun, not because I've put considerable thought into it:

#!/usr/bin/psql --enable-hash-comment -f
...

-m

Re: problems with postgresql 10.1 hba_conf on fedora 27

[Matt Zagrabelny]

On Wed, Dec 20, 2017 at 9:30 PM, support-tiger 
wrote:

>
> No, the docs for understanding  hba_conf are not good (yes we can read and
> are fairly smart)  - we made suggestions the last time for several case
> examples and were ignored - okay, simplicity of  pouchdb/couchdb  is
> getting our attention
>
>
Can you point to a link where the hba docs are suboptimal and suggest to
the mailing list an improvement?

-m