Cuestionario: Deep Postgres: Parte 1

MEDIAN no es incorporada! Necesitas:

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

Agregados incorporados comunes:

• MIN, MAX, COUNT
• AVG, SUM
• ARRAY_AGG, STRING_AGG
• Varias funciones estadísticas

PostgreSQL admite tres sintaxis de casting:

1. ANSI SQL: CAST(expression AS type).
2. PostgreSQL: expression::type.
3. Función de tipo: type 'literal'.

Todas son funcionalmente equivalentes, pero:

• CAST() es la más portable.
• :: es específico de PostgreSQL pero se usa con frecuencia.
• El estilo infijo type 'literal' es menos común pero sigue siendo válido.

Restricciones UNIQUE en PostgreSQL:

• Permiten múltiples valores NULL.
• NULL ≠ NULL en las verificaciones de unicidad.

Para evitar valores NULL, agrega NOT NULL:

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

¡Los intervalos son una herramienta poderosa para simplificar operaciones con rangos de fechas!

Aritmética de fechas en PostgreSQL:

• + interval '24 hours' añade 24 horas
• + interval '1 day' añade 1 día
• + interval '1 month' añade 1 mes
• + interval '1 year' añade 1 año

El resultado es 2024-11-28 00:00:00.

Ambos son de 8 bytes, pero no almacenan el mismo tipo de valor.

Entonces, ¿cuál es la diferencia? Está en el análisis de la entrada.

timestamptz

• Normaliza la entrada a un punto absoluto en el tiempo.
• Tiene en cuenta la configuración TimeZone del servidor/conexión al analizar la entrada sin un desplazamiento explícito y al mostrar la salida.

timestamp

• Almacena una fecha y hora sin conversión de zona horaria.
• No conserva ni normaliza la información de zona horaria.

timestamp

• Almacena la fecha y hora sin información de zona horaria.
• Útil para guardar explícitamente fechas estandarizadas, ya sea en UTC o en una zona horaria específica.

PostgreSQL tiene un amplio conjunto de tipos de datos, pero STRING(100) no es uno de ellos.

Los tipos de cadena correctos incluyen:

• VARCHAR(100) (cadena de longitud variable)
• CHAR(100) (cadena de longitud fija)
• TEXT (longitud ilimitada)
• CHARACTER VARYING(100) (igual que VARCHAR(100))

Puede parecer familiar ya que decimal128 es un tipo en muchos lugares (incluyendo Mongo y Java). No es un tipo válido de PostgreSQL, el tipo válido es decimal.

Los tipos numéricos correctos son:

• int (entero de 4 bytes)
• bigint (entero de 8 bytes)
• real (punto flotante de 4 bytes)
• double precision (punto flotante de 8 bytes)
• bigserial (entero de 8 bytes autoincremental)
• smallserial (entero de 2 bytes autoincremental)

¿Te hizo esto sentir frustrado, incluso enojado? ¡No estás solo! Para citar a un contribuidor “core” sin nombre, “what the hell, Dan?! I crashed on the type questions! Thats violent sir! Not sharing my score, hah.” 😈 De nada.

El rico conjunto de tipos de red de PostgreSQL no incluye ipv4. Cada vez que intento usarlo sin buscar, me equivoco. Tal vez macaddr8 me hace sentir que debe haber tipos ipv4 y ipv6. No, inet cubre ambos. Además, cidr cubre máscaras de red para ambos.

Los tipos de red válidos incluyen:

• cidr (dirección de red IPv4/IPv6)
• inet (dirección de host IPv4/IPv6)
• macaddr (dirección MAC)
• macaddr8 (dirección MAC EUI-64)

PostgreSQL tiene un amplio conjunto de tipos especializados, pero currency no es uno de ellos!

Los tipos válidos incluyen:

• xml (datos XML)
• uuid (UUID)
• money (monto monetario)
• interval (intervalo de tiempo)

PostgreSQL tiene un conjunto amplio de tipos especializados, pero triangle no es uno de ellos.

Creo que en próximas versiones de GEOS se incluirá soporte OGC/WKT para Triangle, lo que significa que eventualmente debería estar en PostGIS. (Básicamente, esta respuesta podría ser incorrecta en el futuro.)

Los tipos especializados correctos incluyen:

• box (caja rectangular)
• line (línea infinita)
• point (punto 2D)
• circle (círculo 2D)
• polygon (polígono 2D)

El tipo integer de PostgreSQL es de 32 bits con signo, con rango de -2,147,483,648 a 2,147,483,647.

El cálculo 256^4 = 4,294,967,296 supera este rango.

Para manejar números más grandes:

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;

-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

Los timestamps de PostgreSQL tienen precisión de microsegundos (6 decimales).

• Máximo: .123456 (6 dígitos)
• Los nanosegundos (9 dígitos) se redondean o truncan a la precisión admitida
• Las zonas horarias se aceptan para timestamptz, pero no son obligatorias

Curiosidad poco común: Algunos lenguajes/frameworks envían precisión de nanosegundos, pero PostgreSQL almacena los timestamps con precisión de microsegundos.

PostgreSQL incluye varios tipos geométricos y de búsqueda de texto, pero tsrank no es uno de ellos.

Los tipos geométricos y de búsqueda de texto correctos incluyen:

• lseg (segmento de línea)
• bytea (datos binarios)
• tsquery (consulta de búsqueda de texto)
• tsvector (documento de búsqueda de texto)

NOT VALID constraints:

• Se verifican inmediatamente para inserciones y actualizaciones nuevas
• No validan filas existentes
• Pueden validar filas existentes más tarde con VALIDATE CONSTRAINT
• Son útiles para tablas grandes

Sin NOT VALID:

• La restricción se verifica inmediatamente
• Todas las filas existentes se validan
• Puede ser lento en tablas grandes