Snippets Collections
SHOW search_path;
SET search_path TO Schema1, public;
select 
json_event->'project',
json_event->'pipeline_params'->'artifacts_iac' as iac
from cicd_event
where event_type = 'ci_start'
and jsonb_array_length(jsonb_path_query_array(
	json_event->'pipeline_params'->'artifacts_iac',
	'$[*] ? (@.classifier == "terraform")'
)) >=2
SELECT 
  indexname AS index_name,
  tablename AS table_name,
  indexdef AS index_definition
FROM 
  pg_indexes
WHERE 
  schemaname = 'public'; -- Replace 'public' with the desired schema name if the index is in a different schema
def groovyObject
String json

// Groovy -> JSON
import groovy.json.JsonOutput
json = JsonOutput.prettyPrint(JsonOutput.toJson(groovyObject))

// Quick println
println groovy.json.JsonOutput.prettyPrint(groovy.json.JsonOutput.toJson(groovyObject))

// JSON -> Groovy
import groovy.json.JsonSlurper
def jsonSlurper = new JsonSlurper()
groovyObject = jsonSlurper.parseText(json)
SELECT
    planname,
    '{"c":"baz"}'::JSON,
    json_build_object(planname,1,'bar',2)
FROM bplaene
LIMIT 10
;

-- Complex nested JSON with arrays
SELECT json_build_object(
               'trans_id', t.trans_id,
               'user_data', json_build_object(
                       'user_id', t.user_id,
                       'user_username', t.user_username,
                       'user_full_name', t.user_full_name,
                       'user_street', t.user_street,
                       'user_postal_code', t.user_postal_code,
                       'user_additional_info', t.user_additional_info,
                       'user_country', t.user_country,
                       'user_vat_number', t.user_vat_number),
               'order_data', json_build_object(
                       'order_date', t.order_date,
                       'order_sum', t.order_sum,
                       'order_vat', t.order_vat,
                       'order_invoice_nr', t.order_invoice_nr
                   ),
               'locations',
               (SELECT json_agg(row_to_json(locations))
                FROM (SELECT l.address,
                             l.project_title,
                             (SELECT json_agg(row_to_json(f))
                              FROM (SELECT layername,
                                           data
                                    FROM sales.features) f) features

                      FROM sales.locations l) locations)
           ) transaction
FROM sales.transactions t
select 
  case greatest(col1,col2,col3,col4) 
    when col1 then 'col1:' || col1
    when col2 then 'col2:' || col2
    when col3 then 'col3:' || col3
    when col4 then 'col4:' || col4
    else null
  end as greatestcolumnname
from mytable;
sudo -u postgres psql
create database mydb;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;
$ psql -h <host> -p <port> -U <username> <database>
select "field1", "field2", count(*)
from "tableName"
group by "field1", "field2"
HAVING count(*) > 1
  
create or replace function decode_url(url text)
  returns text as
$BODY$
DECLARE result text;
BEGIN
    if url isnull then
        return null;
    end if;

    BEGIN
        with str AS (
            select
                   case when url ~ '^%[0-9a-fA-F][0-9a-fA-F]'
                   then array['']
                   end
            || regexp_split_to_array(url, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i') plain,

            array(select (regexp_matches(url, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi'))[1]) encoded
            )

        select string_agg(plain[i] || coalesce(convert_from(decode(replace(encoded[i], '%',''), 'hex'), 'utf8'),''),'')
        from str, (select generate_series(1, array_upper(encoded, 1) + 2) i FROM str) serie
        into result;

    EXCEPTION WHEN OTHERS THEN
        raise notice 'failed: %', url;
        return url;
    END;

    return coalesce(result, url);

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE
FUNCTION table_has_column(tablename TEXT,
OUT res boolean)
 RETURNS boolean

AS $func$
-- DECLARE res boolean DEFAULT FALSE;

BEGIN
SELECT
    (count(constraint_name)>0)::boolean AS res
INTO
    res
FROM
    information_schema.table_constraints
WHERE
    table_name = tablename
    AND constraint_type = 'PRIMARY KEY';
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_pk(tablename TEXT,
OUT res boolean) RETURNS boolean AS $func$ 
res boolean DEFAULT FALSE;

BEGIN
SELECT
    (count(constraint_name)>0)::boolean AS res
INTO
    res
FROM
    information_schema.table_constraints
WHERE
    table_name = tablename
    AND constraint_type = 'PRIMARY KEY';
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_column(tablename TEXT,
columnname TEXT,
OUT res boolean)
 RETURNS boolean
 
AS $func$ 

BEGIN
SELECT
    (count(column_name) > 0)::boolean AS res
INTO
    res
FROM
    information_schema.columns
WHERE
    table_name = tablename
    AND column_name = columnname;
END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION table_has_columns(tablename TEXT,
VARIADIC columnname TEXT) RETURNS boolean AS $func$
 DECLARE res boolean DEFAULT FALSE;

DECLARE
  x TEXT;

res boolean DEFAULT TRUE;

BEGIN
  FOREACH x IN ARRAY columnname LOOP
      EXIT
WHEN res = FALSE;

SELECT
    table_has_column(tablename,
    x) AS res
INTO
    res;
END;
END;

$func$ LANGUAGE plpgsql;
grant all privileges on database <dbname> to <username> ;
select pgc.conname as constraint_name,
       ccu.table_schema as table_schema,
       ccu.table_name,
       ccu.column_name,
       pgc.* as definition
from pg_constraint pgc
join pg_namespace nsp on nsp.oid = pgc.connamespace
join pg_class  cls on pgc.conrelid = cls.oid
left join information_schema.constraint_column_usage ccu
          on pgc.conname = ccu.constraint_name
          and nsp.nspname = ccu.constraint_schema
where contype ='c'
order by pgc.conname;
select users.last_login,us.*,users.* from users left join user_school us on us.user_id = users.id where school_id is not null
and last_login between '2021-01-01'::date and '2021-12-31'::date
UPDATE public.users
SET name=concat('User',id), email=concat('user',id,'@email.com')
WHERE name not like '%Testbruker';
created_at = models.DateTimeField(auto_now_add=True)
updated_at = models.DateTimeField(auto_now=True)
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'dojoreads_db',
        'USER': 'postgres',
        'PASSWORD': 'root',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}
star

Fri Jul 12 2024 11:02:41 GMT+0000 (Coordinated Universal Time)

#postgres #postgis #sql
star

Fri Jun 07 2024 13:57:08 GMT+0000 (Coordinated Universal Time)

#postgres #postgresql #json
star

Fri Jan 26 2024 08:26:10 GMT+0000 (Coordinated Universal Time)

#docker #postgres
star

Tue Jul 25 2023 07:23:58 GMT+0000 (Coordinated Universal Time)

#sql #postgres
star

Mon Jun 19 2023 10:05:19 GMT+0000 (Coordinated Universal Time)

#postgres #postgresql #percentile #median #average #duration
star

Fri Mar 03 2023 10:57:14 GMT+0000 (Coordinated Universal Time)

#postgres #sql #json
star

Thu Mar 10 2022 03:28:53 GMT+0000 (Coordinated Universal Time) https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

#postgres
star

Mon Feb 14 2022 07:38:16 GMT+0000 (Coordinated Universal Time) https://alvinalexander.com/blog/post/postgresql/log-in-postgresql-database/

#login #postgres
star

Mon Feb 14 2022 07:24:44 GMT+0000 (Coordinated Universal Time) https://medium.com/ruralscript/install-and-setuppostgresql-on-ubuntu-amazon-ec2-5d1af79b4fca

#ec2 #postgres #setup-postgres-on-ec2 #ubuntu
star

Mon Feb 14 2022 07:02:20 GMT+0000 (Coordinated Universal Time)

#psql #postgres #remotedb
star

Mon Jan 03 2022 02:32:45 GMT+0000 (Coordinated Universal Time) https://stackoverflow.com/

#sql #postgres #duplicate
star

Mon Dec 06 2021 10:27:38 GMT+0000 (Coordinated Universal Time)

#sql #postgres
star

Thu Dec 02 2021 18:53:06 GMT+0000 (Coordinated Universal Time)

#sql #postgres #columns
star

Wed Jul 07 2021 17:00:07 GMT+0000 (Coordinated Universal Time) https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

#postgres
star

Mon Jun 14 2021 17:06:09 GMT+0000 (Coordinated Universal Time)

#sql #postgres
star

Fri May 28 2021 15:01:34 GMT+0000 (Coordinated Universal Time)

#sql #postgres
star

Thu May 27 2021 20:27:23 GMT+0000 (Coordinated Universal Time)

#sql #postgres
star

Mon Mar 29 2021 22:54:38 GMT+0000 (Coordinated Universal Time)

#python #django #postgres
star

Mon Mar 29 2021 22:20:07 GMT+0000 (Coordinated Universal Time)

#python #django #postgres

Save snippets that work with our extensions

Available in the Chrome Web Store Get Firefox Add-on Get VS Code extension