> On 8/12/23 9:02 a.m., Amn Ojee Uw wrote:
>
> Is there a book to be recommended for PostgreSQL beginners?
If you are new to relational databases and SQL in general, I recommend
the basics of SQL (not Postgres-specific) to start off. The SQL Murder
Mystery is a good first experience.
On Fri, Mar 24, 2023 at 4:07 AM Inzamam Shafiq
wrote:
>
> Can someone please list pros and cons of MariaDB vs PostgreSQL that actually
> needs serious consideration while choosing the right database for large OLTP
> DBs (Terabytes)?
Think about what you want/need from the database for your
On Wed, Feb 8, 2023 at 11:56 AM Kirk Wolak wrote:
>
> CREATE FUNCTION generate_ulid() RETURNS uuid
> LANGUAGE sql
> RETURN ((lpad(to_hex((floor((EXTRACT(epoch FROM clock_timestamp()) *
> (100)::numeric)))::bigint), 14, '0'::text)
> || encode(gen_random_bytes(9),
On Thu, Feb 2, 2023 at 11:47 AM veem v wrote:
> Tested the UUIDv7 generator for postgres as below.
>
> With regards to performance , It's still way behind the sequence. I was
> expecting the insert performance of UUID v7 to be closer to the sequence ,
> but it doesn't seem so, as it's 500ms vs
On Wed, Feb 1, 2023 at 10:48 AM Kirk Wolak wrote:
>
>
> On Wed, Feb 1, 2023 at 1:34 PM veem v wrote:
>
>>
>> 1) sequence generation vs UUID generation, execution time increased from
>> ~291ms to 5655ms.
>> 2) Insert performance of "sequence" vs "UUID" execution time increased
>> from ~2031ms
On Sat, Jan 28, 2023 at 8:02 PM Ron wrote:
>
> Then it's not a Type 4 UUID, which is perfectly fine; just not random.
Yep, which is why it really should be re-versioned to UUIDv8 to be
pedantic. In everyday use though, almost certainly doesn't matter.
> Also, should now() be replaced by
even though the
standard hasn't been ratified yet.
Cheers,
Miles Elam
he impression that if the WHERE clause evaluated to
false, the SELECT clause would not be evaluated. Why is get_byte(...)
ever run in the first place even though length(bytes) is 3?
- Miles Elam
Are there any plans to (or specific decisions not to) support a RETURNING
clause on MERGE statements in future versions of Postgres? The only
reference I could find in the mailing list archives was this comment, which
suggested it was desired but simply not technically feasible at the time.
ting and willing/able to write some C code or
run some pl/pythonu, you could create an extension/function that performs
this logic.
Or again if you are self-managed and go the cron route as suggested by
David Johnson, there's the extension pg_cron.
– Miles Elam
On Tue, Oct 26, 2021 at 6:36 AM Marcos Pegoraro wrote:
>
>> Maybe converting new and old records to json and text
> PERFORM * FROM (select ID, row_to_json(O.*)::text Old_Values,
> row_to_json(N.*)::text New_Values from old_table o full outer join
> new_table N using(ID) where Old_Values is
On Fri, Sep 17, 2021 at 1:13 PM Benedict Holland <
benedict.m.holl...@gmail.com> wrote:
> I don't get why there are so many programming languages out there. C is
> virtually perfect.
>
Oh my. Even its creators didn't believe this, and that was decades ago. Use
after free. Dangling pointers. No
Follow up to this. Turns out we had a table without a primary key which
halted the ongoing replication.
Reviewing this document in detail now.
https://pgdash.io/blog/postgres-replication-gotchas.html
- Miles Elam
between versions?
Thanks in advance,
Miles Elam
On Fri, Aug 27, 2021 at 7:14 PM Julien Rouhaud wrote:
>
> Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent. If you
> need to write idempotent schema update scripts, you need to query the
> catalogs to check if the specific change you want to apply has already
> been applied or not.
has DDL idempotency been viable for maintenance of PG
databases fo you in production?
- Miles Elam
} is like '+' but clamps at 20.
select regexp_replace(
repeat('someone,one,one,one,one,one,one,', 60),
'(?<=^|,)([^,]+)(?:,\1){1,20}(?=$|,)',
'\1', -- replacement
'g' -- apply globally (all matches)
);
- Miles Elam
On Tue, Jul 27, 2021 at 4:38 AM Dave Cramer
wrote:
>
> Does RDS allow logical replication
>
Yes, it does. I believe it was patched for v9.6, but v10 and above support
it out of the box, and the RDS version of PostgreSQL shares that support. I
have used it with v10 and v11, and it works exactly
a condition is false; useful for outputting CHECK
constraint error values.';
CREATE DOMAIN po.email AS varchar
CHECK (po.confirm(VALUE, VALUE IS NULL OR NOT po.email_expanded(VALUE) IS
NULL));
Code is not seamless or DRY, but manageable.
- Miles
On Tue, Apr 6, 2021 at 2:18 PM Miles Elam wrote
On Tue, Apr 6, 2021 at 1:59 PM Ron wrote:
>
> The blunt force answer is to not use bulk inserts. Try COPY; it's good at
> saying which record throws an error.
>
Sadly, this is a cloud-managed database without direct access to 5432 from
outside the VPC and bastian instances are frowned upon by
On Tue, Apr 6, 2021 at 1:03 PM Ron wrote:
> On 4/6/21 2:40 PM, Miles Elam wrote:
>
> I've got a domain that validates email addresses. When inserting a bunch
> of entries I simply get the error message
>
> ERROR: value for domain po.email violates check constraint "e
I've got a domain that validates email addresses. When inserting a bunch of
entries I simply get the error message
ERROR: value for domain po.email violates check constraint "email_check"
SQL state: 23514
When inserting 1000+ entries in a batch, finding the exact entry with the
problem is
Also of note: PostgreSQL already has a money type (
https://www.postgresql.org/docs/current/datatype-money.html)
But you shouldn't use it (
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money).
I only bring it up so that you can know to make your money type a slightly
different
I did the same for at least a year, but I must admit that v4 has improved
greatly since its initial release. Also it turns out is handy for running
in a docker-compose environment so no matter who is starting up on your
team, they always have a database and a database admin tool at the ready
along
How can the new record returned from RETURNING to converted to jsonb?
For example something like:
INSERT INTO my_table (a, b, c) VALUES ('a', 'b', 'c')
RETURNING to_jsonb(*);
or
UPDATE my_table SET a = 'a'
RETURNING to_jsonb(*);
or
INSERT INTO my_table (a, b, c) VALUES ('a', 'b', 'c')
ON
On Tue, Feb 25, 2020 at 6:41 AM Josef Šimánek
wrote:
>
> út 25. 2. 2020 v 15:35 odesílatel Miles Elam
> napsal:
>
>> How do you see this syntax working in a JOIN query?
>>
>> SELECT x.* EXCEPT x.col1, x.col2, y.col1
>> FROM tablex AS x
>> LEFT JOIN
How do you see this syntax working in a JOIN query?
SELECT x.* EXCEPT x.col1, x.col2, y.col1
FROM tablex AS x
LEFT JOIN tabley AS y;
The column(s) you want to exclude become ambiguous. Parentheses?
SELECT x.* EXCEPT (x.col1, x.col2), y.col1
FROM tablex AS x
LEFT JOIN tabley AS y;
Could
about inheritance being useful for temporal logic but not much
else since proper table partitioning was introduced. By and large I agree
with the reasoning, especially with regard to unique keys and their lack of
propagation. It just didn't seem to address the interface model one way or
another.
- Miles Elam
In terms of "wasted computation", MD5, SHA1, and the others always compute
the full length before they are passed to a UUID, int, or whatever. It's a
sunk cost. It's also a minor cost considering many hash algorithms are
performed in CPU hardware now. All that's left is the truncation and cast,
I was under the impression that PostgreSQL 12 removed this limitation. Was
this incorrect?
https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/
On Mon, Nov 18, 2019 at 9:58 AM Michael Lewis wrote:
> On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan
> wrote:
>
if they wanted triggers to fire on the
subscriber, they only a subset of all tables replicated, etc.
Perhaps a better question would be "What problem are you trying to solve?"
rather than focus on how you expected to solve that problem.
Cheers,
Miles Elam
On Thu, Oct 10, 2019 at 11:08 AM L
, in_extension
FROM pg_event_trigger_ddl_commands();
END;
$$;
CREATE EVENT TRIGGER aa_ddl_info ON ddl_command_end
EXECUTE PROCEDURE ddl_log();
On Wed, Oct 9, 2019 at 2:27 PM Adrian Klaver
wrote:
> On 10/9/19 1:56 PM, Miles Elam wrote:
> > GRANT and REVOKE trigger on a ddl_command_
have an object_type of 'TABLE' instead of
lower case names like 'table' for all other event types?
Thanks,
Miles Elam
Thanks, it does!
On Sat, Oct 5, 2019 at 1:50 AM Luca Ferrari wrote:
> On Fri, Oct 4, 2019 at 10:38 PM Miles Elam
> wrote:
> >
> > The event trigger firing matrix lists tags like DROP TABLE and DROP
> FUNCTION are listed below the ddl_command_end event, but when I created
that DROP statements
require explicit tagging, so I assumed that not specifying any tags would
include all tags. Is this an oversight in the docs and expected behavior or
is this a bug? Doesn't fire in any version from 9.6 on. I didn't test
versions before 9.6.
Thanks in advance,
Miles Elam
Makes sense. Thanks!
On Wed, Sep 11, 2019 at 1:43 PM Tom Lane wrote:
> Miles Elam writes:
> > Is there any way to prevent a user from dropping a table when that user
> has
> > create rights? I'd like to allow that user to be able to create and
> delete
> > their
Is there any way to prevent a user from dropping a table when that user has
create rights? I'd like to allow that user to be able to create and delete
their own tables but not specific shared tables.
Is the only way to put the shared tables into a different schema?
Thanks in advance
Is there any way to define a natural sorting order for composite types? For
example, let's say you have a type like:
CREATE TYPE contrived AS (
i1 integer,
i2 integer
);
The semantics of this contrived type are that the natural order is
ascending NULLS first for i1 and descending NULLS
Not enough information. It looks far more like he's testing Ruby's support
for ElasticSearch vs ActiveRecord rather than ES vs PostgreSQL. Caching
could definitely hold a role but also choice of indexes. If ES is
calculating some aggregate info on the fly, the equivalent in PG would be a
stats
he history in an ad-hoc
manner rather than the trigger-based predetermined insert pattern.
On Thu, Jun 20, 2019 at 8:01 AM Adrian Klaver
wrote:
> On 6/19/19 3:07 PM, Miles Elam wrote:
> > Hi Adrian, thanks for responding.
> >
> > How would I restrict access to the SECUR
at 6:20 PM Adrian Klaver
wrote:
> On 6/18/19 10:14 AM, Miles Elam wrote:
> > Thanks for the suggestion. Unfortunately we only have a single login
> > role (it's a web app) and then we SET ROLE according to the contents of
> > a JSON Web Token. So we end up with SESSION_USER
lect (xx()).*;
>cur| sess
> --+---
> postgres | write
>
>
> On Tue, Jun 18, 2019 at 6:30 PM Miles Elam
> wrote:
>
>> That seems straightforward. Unfortunately I also want to know the
>> user/role that performed the operation. If I use SECURITY DEFINER, I get
>> the
still retaining info about the current user/role?
On Mon, Jun 17, 2019 at 5:47 PM wrote:
> Adrian Klaver wrote:
>
> > On 6/17/19 4:54 PM, Miles Elam wrote:
> > > Is there are way to restrict direct access to a table for inserts but
> > > allow a trigger on anot
Is there are way to restrict direct access to a table for inserts but allow
a trigger on another table to perform an insert for that user?
I'm trying to implement an audit table without allowing user tampering with
the audit information.
Thanks in advance,
Miles Elam
44 matches
Mail list logo
