Useful Postgres Snippets

Last Updated On: 2025-09-01 04:31:51 -0400

Here are some useful Postgres SQL commands / Snippets:

Truncation and Restoration of Primary Key to 1

TRUNCATE comments, google_sheet_urls, user_roles, users, events, government_officials, congressional_districts RESTART IDENTITY;

Life Pro Tip: Using rails console for this

If you don’t have a sql client, you can execute a query like the above using the rails console when ssh’d into a server:

bin/rails c
ActiveRecord::Base.connection.execute("TRUNCATE comments, google_sheet_urls, user_roles, users, events, government_officials, congressional_districts RESTART IDENTITY;")

And that will execute the query.

Show Tables

\dt
 
\dt+

Change Database

-- Replace these with your actual database and user
\c myapp_production postgres

Get Past the Postgres 16 Security Issues

-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO myapp_user;

-- Grant usage and create privileges on the public schema
GRANT USAGE, CREATE ON SCHEMA public TO myapp_user;

-- Make myapp_user the owner of the public schema (optional but avoids future permission issues)
ALTER SCHEMA public OWNER TO myapp_user;

-- Ensure future tables created in public schema are accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT ALL ON TABLES TO myapp_user;

A Postgres Equivalent to SHOW CREATE TABLE from MySQL

Here’s a full example. Let’s say you had the table comments and you needed to get its schema to manually created it. You would just dump the schema to the screen and then manually create it line by line in the Postgres console.

You could also likely pipe this to pbcopy and then paste in the result.

pg_dump -d pollitify_hub_development -t comments –schema-only

– PostgreSQL database dump –

– Dumped from database version 14.17 (Homebrew) – Dumped by pg_dump version 14.17 (Homebrew)

SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = ‘UTF8’; SET standard_conforming_strings = on; SELECT pg_catalog.set_config(‘search_path’, ‘’, false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off;

SET default_tablespace = ‘’;

SET default_table_access_method = heap;

– – Name: comments; Type: TABLE; Schema: public; Owner: sjohnson –

CREATE TABLE public.comments ( id bigint NOT NULL, content text, commentable_type character varying, commentable_id bigint, user_id bigint, created_at timestamp(6) without time zone NOT NULL, updated_at timestamp(6) without time zone NOT NULL );

ALTER TABLE public.comments OWNER TO sjohnson;

– – Name: comments_id_seq; Type: SEQUENCE; Schema: public; Owner: sjohnson –

CREATE SEQUENCE public.comments_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE public.comments_id_seq OWNER TO sjohnson;

– – Name: comments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: sjohnson –

ALTER SEQUENCE public.comments_id_seq OWNED BY public.comments.id;

– – Name: comments id; Type: DEFAULT; Schema: public; Owner: sjohnson –

ALTER TABLE ONLY public.comments ALTER COLUMN id SET DEFAULT nextval(‘public.comments_id_seq’::regclass);

– – Name: comments comments_pkey; Type: CONSTRAINT; Schema: public; Owner: sjohnson –

ALTER TABLE ONLY public.comments ADD CONSTRAINT comments_pkey PRIMARY KEY (id);

– – Name: index_comments_on_commentable; Type: INDEX; Schema: public; Owner: sjohnson –

CREATE INDEX index_comments_on_commentable ON public.comments USING btree (commentable_type, commentable_id);

– – Name: index_comments_on_user_id; Type: INDEX; Schema: public; Owner: sjohnson –

CREATE INDEX index_comments_on_user_id ON public.comments USING btree (user_id);

– – Name: comments fk_rails_03de2dc08c; Type: FK CONSTRAINT; Schema: public; Owner: sjohnson –

ALTER TABLE ONLY public.comments ADD CONSTRAINT fk_rails_03de2dc08c FOREIGN KEY (user_id) REFERENCES public.users(id);

– – PostgreSQL database dump complete –