vbilopav

select 
  g.code
from (
    select generate_series as code from generate_series(1000, 9999) 
  ) g
  left outer join tbl t on g.code = t.id
where 
  t.id is null
order by
  random() limit 1;

vbilopav

Norm.net Data Access at a glance

• Prerequisites and Notes
• Execution
  • Simple execution
  • Chaining calls
• Single row mappings
  • Single value
  • Multiple value mappings
• ARRAY types (PostgreSQL only)

vbilopav

CREATE EXTENSION pg_stat_statements SCHEMA public;

# postgresql.conf

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

vbilopav

Dictionary for database pdd

• Server: PostgreSQL localhost:5436, version 14.0
• Local time stamp: 2022-07-15T12:01:44.0784710+02:00
• Schema: public

Table of Contents

• Table public.business_areas
• Table public.business_roles

vbilopav

PgRoutiner Cheat Sheet

TOC

• Installation
• Connection Managament
• Silent console output and basic configuration
• List database objects
• Create default configuration file
• Objects defintions

vbilopav

create or replace function companies.search_companies(
    _search varchar, 
    _skip integer, 
    _take integer
) 
returns json
language plpgsql 
as $$
declare
    _count bigint;

vbilopav

/*
Creates TypeScript models from CSharp models
- TypeScript model is set by config key TsModelsPath
- CSharp models namespace is set by config key ModelNamespace

1) Add to your Program.cs:
if (ModelBuilder.Build(args))
{
    return;
}

vbilopav

using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Serialization;

namespace MyNamespace;

public static class JsonValidator
{
    public static bool IsJsonValidType(this string json, Type modelType, out string? message)
    {
        try

vbilopav

--
-- Detect all duplicate foreign keys from public schema and generates a script that drops all duplicates but first.
--
with fkeys as (

    select
        tc.constraint_name, 
        tc.table_name, 
        kcu.column_name, 
        ccu.table_name as foreign_table_name,

vbilopav

select
    title,
    name,
    avg(score)
from movies m
    join movies_actors using(movie_id)
    join actors a using(actor_id)
    join (
        select movie_id
        from actors join movies_actors using(actor_id)