snippets

2023-07-21 ยท 2 min read

  • Remember strings must be surrounded with single quotes '' not double quotes ""!

"dry run" in psql repl #

Don't know why I didn't think of this. Literally just wrap everything in a transaction. If you fuck up, just rollback.

begin;

-- whatever queries --

rollback;
-- or
commit;

better printing for long rows # 

\x auto

update, then print new values # 

update payment
set id = 'os_57ea76' where id = 'or_57ea76'
returning created_at, id;

update payment
set id = replace(id, 'or_', 'os_')
where starts_with(id, 'or_')
returning created_at, id;

upsert # 

-- silently ignore on conflict
insert into "user"
values ('442e9dd9d1a3e39c203ce131492090d7', '4e718d3c17d3f4c67e2eaa26da2feb66')
on conflict (user_pk) do nothing;

-- replace on conflict
insert into "user"
values ('442e9dd9d1a3e39c203ce131492090d7', '4e718d3c17d3f4c67e2eaa26da2feb66')
on conflict (user_pk) do update set node_pk = EXCLUDED.node_pk;

-- also return
insert into "user"
values ('442e9dd9d1a3e39c203ce131492090d7', '4e718d3c17d3f4c67e2eaa26da2feb66')
on conflict (user_pk) do update set node_pk = EXCLUDED.node_pk
returning user_pk, node_pk;

explore db # 

-- show tables (basic info)
# \dt
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | payment          | table | lx
 public | seaql_migrations | table | lx
 public | user             | table | lx
(3 rows)

-- show tables (more detail)
# \dt+
                                       List of relations
 Schema |       Name       | Type  | Owner | Persistence | Access method |  Size  | Description
--------+------------------+-------+-------+-------------+---------------+--------+-------------
 public | payment          | table | lx    | permanent   | heap          | 172 kB  |
 public | seaql_migrations | table | lx    | permanent   | heap          | 16 kB  |
 public | user             | table | lx    | permanent   | heap          | 16 kB  |
(10 rows)

string length # 

select length('1234');
 length
--------
     64
(1 row)

hex # 

-- hex encode
select encode(data, 'hex');
 encode
--------
 7dd6a30e8559db3da3bd
(1 row)

-- hex decode
select decode('01abef', 'hex');
  decode
----------
 \x01abef
(1 row)

-- hex binary literal
select x'01abefff';
         ?column?
--------------------------
 000100101010101111101111
(1 row)